エクセルの関数をマクロで変換

=IF(Sheet1!$A$1="", "", Sheet1!$A$1)　というように0を0として表示させたいので、
式を長く変形したいです。

=Sheet1!$A$1　セルには右のような式が入っています。うまくマクロを使って
対応するたくさんのセルに関して
変形できないでしょうか。

A2のセルにA6の絶対参照が入っているようなこともあるのでマクロでしたいと思います。

相対参照⇔絶対参照はできるようになりましたが、式変形に関してのマクロの知識がありません。
変換でもできないようなのでどうしたらよいのか困ってます。

No.1 回答者： WindFaller 回答日時： 2017/05/08 10:45

こんにちは。

>=Sheet1!$A$1　セルには右のような式
これは、左ですね。探してしまいました。
それで、どのような内容にするのか分かりませんから、
仮の方法を教えておきます。なお、これ以上に複雑なものに関しては、正規表現を使って変換します。


ConvertFormulaのメソッドを使います。
'ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

FromReferenceStyle: xlA1/xlR1C1
ToReferenceStyle : xlA1/xlR1C1

ToAbsolute
--------------
xlAbsolute 　1
xlAbsRowRelColumn　2
xlRelRowAbsColumn 3
xlRelative 4
-------------
RelativeTo
------------
起点となるセル/ R1C1 型から変換する時
-----------

仕様
xlAbsolute 　= "選択範囲のセル参照を、絶対参照($A$1)に一括変換します。"
xlAbsRowRelColumn = "選択範囲のセル参照を、行は絶対参照､列は相対参照(A$1)に一括変換します。"
xlRelRowAbsColumn = "選択範囲のセル参照を、行は相対参照､列は絶対参照($A1)に一括変換します。"
xlRelative = "選択範囲のセル参照を、相対参照(A1)に一括変換します。"

'//絶対参照から相対参照にする場合。

Sub SampleMacroFormulaConvert()
Dim c As Range
　For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
　If c.HasFormula Then
　 c.FormulaLocal = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlRelative)
　End If
Next
End Sub

この回答へのお礼

Formulaの文を使っていくつかトライしましたが、うまくいきませんでした。しかし、目前の問題は、コピーアンドペーストで解決しました。絶対参照にしていたので、うまくコピーアンドペーストができなかったようです。
絶対参照から相対参照にするマクロは便利ですね。ストックしておきます。ありがとうございました。

お礼日時：2017/05/08 23:35

No.2 回答者： fujillin 回答日時： 2017/05/08 12:38

こんにちは

セルの参照式が
　=○○　（○○はセルの参照）
となっているものを、一括で
　=IF(○○="", "", ○○)
という形に変更なさりたいということと解釈しました。


以下は単純なセル参照が設定されている場合に、関数式を上記のように変更するマクロです。（処理対象範囲は適宜設定してください）

Sub Sample()
Dim c As Range
Dim tmp As Range
Dim addr As String

Const TargetRange = "A1:E50" '←処理対象の範囲を指定

For Each c In Range(TargetRange)
　If c.HasFormula Then
　　addr = Mid(c.Formula, 2)

　　On Error Resume Next
　　Set tmp = Range(addr)
　　If Err.Number = 0 And tmp.Count = 1 Then
　　　c.Formula = "=IF(" & addr & "="""", """"," & addr & ")"
　　End If
　　On Error GoTo 0

　End If
Next
End Sub

※ 単純参照の式であることのチェックを外せば、一般式の場合でも同様の変更が可能になりますが、使い方を間違えるとおかしなことになるので、単純参照の場合だけに限定してあります。

この回答へのお礼

ありがとうございます。マクロはなかなか難しいですが、参考になりました。おかげ様で解決しました。

お礼日時：2017/05/08 23:32