VBAで数式を入力する場合のAA列以降の指定

Question

下記のようなVBAがあります。
i=27以上になり、AA列以降も数式を入力する場合には、
どのようすればよいでしょうか。
i=100までに対応できればと思います。
よろしくお願いします。

Sub macro1()
    Dim i As Integer
    For i = 1 To 26
        Worksheets("Sheet1").Cells(3, i).Formula = "=" & Chr(64 + i) & "1+" & Chr(64 + i) & "2"
    Next
End Sub

Answer 1

解決案１
セル位置の表示にR1C1形式を使う

Sub macro1()
    Dim i As Integer
    For i = 1 To 100
        Worksheets("Sheet1").Cells(3, i).FormulaR1C1 = "=" & "R1C" & i & "+R2C" & i
    Next
End Sub

案２
マイクロソフトから列番号をアルファベットに変換するマクロが提供されているのでそれを使う。
ここを参照

Sub macro2()
    Dim i As Integer
    Dim col As String
    For i = 1 To 100
        col = ConvertToLetter(i)
        Worksheets("Sheet1").Cells(3, i).Formula = "=" & col & "1" & "+" & col & "2"
    Next
End Sub

Function ConvertToLetter(ByVal iCol As Long) As String
   Dim a As Long
   Dim b As Long
   a = iCol
   ConvertToLetter = ""
   Do While iCol > 0
      a = Int((iCol - 1) / 26)
      b = (iCol - 1) Mod 26
      ConvertToLetter = Chr(b + 65) & ConvertToLetter
      iCol = a
   Loop
End Function

注意：オリジナルのConvertToLetterは、
Function ConvertToLetter(iCol As Long) As String　となっていてicolは参照渡しです。
参照渡しの場合、呼び出し側のiが破壊されるので、byvalをつけて値渡しに変えてあります。

Comments

[nkym771]

ありがとうございます。
R1C1形式 参考になりました。
またMicrosoftで変換マクロが出ているのですね。
参考にします。参照渡しと値渡しの返還もありがとうございました。

Answer 2

列数がいくつでも大丈夫にするには
一番右のセルの列数をとる方法があります。

For i = 1 To 26 のところを以下のように変更すればできるかと思います。

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column

Comments

[nkym771]

ありがとうございます。
右端を取得する方法の参考にします。

Answer 3

26 を 100 にすればいいだけです。
Addressプロパティでセルアドレスを取得すればいいでしょう。

Sub macro1()
    Dim i As Integer
    With Worksheets("Sheet1")
        For i = 1 To 100
            .Cells(3, i).Formula = "=" & .Cells(1, i).Address(0, 0) & "+" & .Cells(2, i).Address(0, 0) & "2"
        Next
    End With
End Sub

---

R1C1形式の相対参照を使えばもう少しシンプルになります。

Sub macro2()
    Dim i As Integer
    For i = 1 To 100
        Worksheets("Sheet1").Cells(3, i).FormulaR1C1 = "=R[-2]C+R[-1]C"
    Next
End Sub

R[-2]Cは自分のセルより２つ上のセルという意味になります。

---

実は、ループしなくても下記の１行で同じ結果になります。

Sub macro3()
        Worksheets("Sheet1").Cells(3, 1).Resize(,100).Formula = "=A1+A2"
End Sub

A3に式を入力して、右へ100列分オートフィルしたのと同じ動作になります。

Comments

[nkym771]

ありがとうございます。
Addressプロパティというものがあるんですね。これまでの書き方から一番近く、考え方が分かりやすいです。
R1C1形式も初めてですが参考になりました。
３番目のExcelに即した方法で便利ですね。今後活用します。