ｖｂａ 数式を入れる 列を数値にしたい

質問

ｖｂａで数式をセルに入れる際に、アルファベットで列を指定するのではなく
数値で指定する場合、どうすればいいでしょうか？

例えば、Ａ１セルに
Sub Sample()
Cells(1, 1) = "=a2+a3"
End Sub

と言う結果にしたい場合、
a列の部分も数値で指定したいです。

Sub Sample()
Cells(1, 1) = "=" & Cells(2,1) & "+" & Cells(3,1)
End Sub

こういうことをやりたいのですが
これだとエラーになってしまいます。

方法を教えてください。

回答No.3

kagakusuki · ベストアンサー

A1セルに入力したいワークシート関数が

=A2+A3

ならば、列番号と行番号がともに相対参照となっていて、A2セルは（関数を入力するセルである）A1セルの1行下の同じ列のセルであり、A3セルはA1セルの2行下の同じ列のセルであるのですから、

Sub Sample()

Cells(1, 1).FormulaR1C1 = "=R[1]C+R[2]C"

End Sub

となります。
　尚、同じ関数（「=A2+A3」）を、例えばB3セルに入力する場合には、

Cells(3, 2)FormulaR1C1 = "=R[-1]C[-1]+RC[-1]"

になりますし、セル番号を絶対参照の

=$A$2+$A$3

とする場合には、

Cells(1, 1).FormulaR1C1 = "=R1C2+R1C3"

になります。

お礼

どうもありがとうございました。

回答No.4

MarcoRossiItaly

流儀は様々あろうかと思いますが、ループで回そうといったお考えなら、こんな感じが分かりやすいかもしれませんね。

cells(1,1).formula = "=" &cells(2,1).address(0,0) &"+" &cells(3,1).address(0,0)

お礼

どうもありがとうございました。

回答No.2

cj_mover

こんにちは、お邪魔します。

ご質問に対する解釈が揺れますが、お求めなのは、計算結果ではなくて、
「指定の数式そのもの、をセルに設定する際に、列参照を数値で表現したい」
という風に受け止めています。
もっとも、例示のようなシンプルな数式ですと、このような処理が実際に必要なのか、
多少、疑問は残ります。
ただ、私自身、その手のコードを何度か掲示したことがありますし、
ニーズというか、そうした方が合理的な場面も、あるにはありますから、
「簡素化した数式を例示しているが、実際には複雑で、他に有効な方法がない」？
という場合を想定しての回答、ということになります。

ひとまず、（非VBA）Excel一般機能のセル参照について、
「相対参照と絶対参照」、「R1C1 参照形式」等、基本事項に自信がない場合は
お浚いしておいてください。

特に、Excel一般機能の「相対参照と絶対参照」は、
VBAからの数式設定に大きく関係してきます。
多くの場合、相対参照/絶対参照を組み合わせて数式を指定することで、個別の参照について、
「行や列をいちいち指定する必要」がある場面は、限られていると思います。
一応、誤解する閲覧者さんがいるといけないので、「行や列をいちいち指定しなくても」
ある程度のことはできることを示しておきます。
' ' 〓〓〓
' ' 〓〓〓
Sub check0()　'　相対参照
　　Range("F1:J5").Formula = "=A1"
End Sub
Sub check1()　'　相対参照
　　Range("B2,E2,B5,E5").Formula = "=A1"
End Sub
Sub check2()　'　行は相対参照、列は絶対参照
　　Range("B2,E2,B5,E5").Formula = "=$A1"
End Sub
Sub check3()　'　行は絶対参照、列は相対参照
　　Range("B2,E2,B5,E5").Formula = "=A$1"
End Sub
Sub check4()　'　絶対参照
　　Range("B2,E2,B5,E5").Formula = "=$A$1"
End Sub
' ' 〓〓〓
' ' 〓〓〓

本題です。
.Value プロパティ、.Formula プロパティ、等は、
一応は省略可能ですけれど、
「値を取得する」ということや「数式を設定する」ということが、
読み手に解り易く伝わるように書く方がベターです。
> Cells(1, 1) = "=" & Cells(2,1) & "+" & Cells(3,1)
この記述では、.Value プロパティが３カ所省略されているという意味になってしまいますが、
もしも、プロパティを省略しないで書いてみようという意識が働けば、
それだけで、自力解決に大きく近づける課題だったかも知れません。

以下、【直接の回答】を含め、具体例を（他にもありますが）５種類、挙げてみます。

' ' 〓〓〓
' ' 〓〓〓J　記法：絶対参照　→　.Address(0, 0)　→　結果：相対参照　　=A2+A3　【直接の回答】
Sub test0()
　　Cells(1, 1).Formula = "=" & Cells(2, 1).Address(0, 0) & "+" & Cells(3, 1).Address(0, 0)
End Sub
' ' 変数を用いる場合。
Sub test0i()
　　Dim iCol As Long
　　iCol = 1
　　Cells(1, iCol).Formula = "=" & Cells(2, iCol).Address(0, 0) & "+" & Cells(3, iCol).Address(0, 0)
　　' ' または
'　　Cells(1, 1).Formula = "=" & Cells(2, iCol).Address(0, 0) & "+" & Cells(3, iCol).Address(0, 0)
End Sub

' ' 〓〓〓　記法：絶対参照　　→　ConvertFormula　→　結果：相対参照　　=A2+A3
' ' VBAで喩えるなら、Cells(RowIndex, ColumnIndex) の要領で数式中のセル範囲をR1C1指定。
Sub Re8374499()
　　Cells(1, 1).Formula = Application.ConvertFormula("=R2C1+R3C1", xlR1C1, xlA1, xlRelative)
End Sub
' ' 変数を用いる場合。
Sub Re8374499i()
　　Dim iCol As Long
　　iCol = 1
　　Cells(1, iCol).Formula = Application.ConvertFormula("=R2C" & iCol & "+R3C" & iCol, xlR1C1, xlA1, xlRelative)
　　' ' または
'　　Cells(1, 1).FormulaR1C1 = Application.ConvertFormula("=R2C" & iCol & "+R3C" & iCol, xlR1C1, , xlRelative)
End Sub

' ' 〓〓〓A　記法：絶対参照　→　結果：絶対参照　=$A$2+$A$3　【もっともシンプル+省略形】
' ' VBAで喩えるなら、Cells(RowIndex, ColumnIndex) の要領で数式中のセル範囲をR1C1指定。
Sub testA()
　　Cells(1, 1) = "=R2C1+R3C1"
End Sub
' ' 変数を用いる場合。
Sub testAi()
　　Dim iCol As Long
　　iCol = 1
　　Cells(1, iCol) = "=R2C" & iCol & "+R3C" & iCol
　　' ' または
'　　Cells(1, 1) = "=R2C" & iCol & "+R3C" & iCol
End Sub

' ' 〓〓〓B　記法：オフセット（相対）参照　→　結果：相対参照　　=A2+A3
' ' VBAで喩えるなら、Cells(1, 1).Offset(RowOffset, ColumnOffset) の要領で数式中のセル範囲をR1C1指定。
Sub testB()
　　Cells(1, 1).FormulaR1C1 = "=R[1]C[0]+R[2]C[0]"
End Sub
' ' 変数を用いる場合。
Sub testBi()
　　Dim iCol As Long
　　iCol = 1
　　Cells(1, iCol).FormulaR1C1 = "=R[1]C[" & iCol - 1 & "]+R[2]C[" & iCol - 1 & "]"
　　' ' または
'　　Cells(1, 1).FormulaR1C1 = "=R[1]C[" & iCol - 1 & "]+R[2]C[" & iCol - 1 & "]"
End Sub

' ' 〓〓〓C　記法：オフセット（相対）参照　→　結果：相対参照　　=A2+A3
' ' VBAで喩えるなら、Cells(1, 1).Offset(RowOffset) の要領で数式中のセル範囲をR1C1指定。
' ' 数式を設定するセルと参照先のセルが同じ列の場合は、列位置を省略できる。
Sub testC()
　　Cells(1, 1).FormulaR1C1 = "=R[1]C+R[2]C"
End Sub
' ' 変数を用いる場合。
Sub testCi()
　　Dim iCol As Long
　　iCol = 1
　　Cells(1, iCol).FormulaR1C1 = "=R[1]C+R[2]C"
End Sub

' ' 〓〓〓

お礼

どうもありがとうございました。

回答No.1

keithin

文字列の結合（＆）では，「空っぽのセルを自動でゼロにしてくれる」機能が働かないので，エラーになります。

簡易には
Cells(1, 1) = "=" & (Cells(2,1)+0) & "+" & (Cells(3,1)+0)
とかでもいいですし，あなたのマクロを動かす「前に」Ａ２，Ａ３に先に何か数字を記入しておくだけでも，エラーは回避できます。

＃蛇足：以下のコメントにはフォロー不要です
あなたが実際の所なにをしたいのかさっぱり不明ですが，あなたのご説明の通りだとすると結局Ａ１セルに
=2+3
のように記入されるだけなので，素直に
cells(1,1) = cells(2,1) + cells(3,1)
のようにしておいて，「計算後のコタエの数字」を記入しておくのと，なんら変わり有りません。

お礼

どうもありがとうございました。