VBA Formula関数で別のセルを参照したい

Question

前提・実現したいこと

VBA初心者です。

ある文字列を含む列番号を取得し、その列のセル値を別の列にFomula関数で参照することを考えています。（後で手入力で修正したいため）

例えば、市場価格に合わせて販売価格を決定したいときに
市場価格の列に　=販売価格の列のセル値　
という式を入れたいのです。
Formatを変更することもあるため、列は変数として扱いたいです。

発生している問題・エラーメッセージ

下のコードでは、当たり前ですが、=Cellと入ってNAME?のエラーが出ます。
また=Cells.Addressでは絶対参照になってしまい、すべて1行目のセル値が入ってしまいます。
うまく1行目から256行目まで対応する式を代入するためには、どう書けばいいでしょうか。

該当のソースコード

販売価格_列 = Range("販売価格").Column　'セル名"販売価格”の列番号を販売価格_列に格納
市場価格_列 = Range("市場価格").Column　'セル名"市場価格”の列番号を市場価格_列に格納

Range(Cells(1, 販売価格_列), Cells(256, 販売価格_列)).Formula = "= Cells(1," & 市場価格_列& ")"
'販売価格の列に =市場価格のセル値 という式を1行目から256行目まで代入

。

Answer 1

こういうことでしょうか

    Range(Cells(1, 販売価格_列), Cells(256, 販売価格_列)).FormulaR1C1 = "=RC" & 市場価格_列

あるいはこういう手も？

    Range("販売価格").Formula = "=市場価格"

Comments

[Akira_1]

ご回答ありがとうございます。
一つ目の案でいけそうな気がします。
追加の質問で恐縮ですが、例えば市場価格にまた別の割引率(%)という列の数値をかけたいとき

Range (Cells(1, 販売価格_列, Cells(256, 販売価格_列)).FormulaR1C1 = "=RC" & 販売価格_列 & " * RC" & 割引率_列) & "/100 "

というコードでいけるかなと思いましたが、うまくいきません。
修正箇所ご教示願いませんでしょうか。

[退会済みユーザー]

かっこの関係がおかしくなっているので、その辺気を付けて見るようにしてください。
最初の「販売価格_列」の右にあるべき閉じかっこがなぜか最後の「割引率_列」の右に行ってしまってる感じでしょうか。
あと、右辺にある「販売価格_列」は「市場価格_列」の間違いでは。

Answer 2

質問の意図は、
「販売価格列に市場価格列の各行を参照する
数式を挿入したい」のではないかと推察し、
コードを書いてみました。

Sub 市場価格を参照()
    
    Dim 市場価格_列 As Long: 市場価格_列 = 1
    Dim 販売価格_列 As Long: 販売価格_列 = 2
    Dim i As Long
    
    For i = 1 To 256
        Cells(i, 販売価格_列).Formula = _
        "=" & Cells(i, 市場価格_列).Address '(RowAbsolute:=False, ColumnAbsolute:=False)
    Next i
    
End Sub

絶対参照が不要の場合は
コメントのシングルクォーテーションを削除してください。

Comments

[Akira_1]

ご回答ありがとうございます。
すみません、付け加え忘れたのですが。For文も試しており、処理に時間がかかりすぎて、ほかの案を考えていました。
実際には２５６ではなくもっと大量の行を処理したいです。

Answer 3

不本意かもしれませんが、for next 構文で１行ずつ処理すると実現できます。

Option Explicit
Option Base 1

Sub hoge()
    Dim 市場価格_列 As Long: 市場価格_列 = 1
    Dim 販売価格_列 As Long: 販売価格_列 = 2
    
    Dim Vlist() As Variant
    ReDim Vlist(256, 1)

    Dim cnt As Long
    
    For cnt = 1 To 256
      Vlist(cnt, 1) = CStr("=" & Cells(cnt, 市場価格_列).Address)
    Next cnt

    Range(Cells(1, 販売価格_列), Cells(256, 販売価格_列)).Formula = Vlist
End Sub

3109さまの回答を踏まえ、コードを修正しました。
Variant型配列でセルへの転記処理を高速化できます。

Comments

[Akira_1]

ご回答ありがとうございます。
ご推測の通り、For文では時間がかかりすぎて、他案ないか考えておりました。
実際には256行ではなくもっと大量行があり、式を反映させる列も一列ではないので、、

[TanakaHiroaki]

3109さまの回答を踏まえ、Variant型配列を用いたコードに書き換えました。
For文を使用しても処理が高速化できているはずですが、いかがでしょうか。