Home>

VBA beginner.

I'm thinking of getting the column number including a certain string and referencing the cell value of that column to another column using the Fomula function. (I want to manually correct it later)

For example, when I want to determine the selling price according to the market price
In the market price column = cell value in the sales price column
I want to put the expression.
I want to treat the column as a variable because the Format may change.

Error message

In the code below, it is natural, but = Cell is entered and an error of NAME?
Also, = Cells.Address will be an absolute reference, and all the cell values ​​in the first row will be entered.
How can I write to assign the corresponding expressions from the first line to the 256th line?

Applicable source code
Sales Price_Column = Range ("Sales Price"). Column Stores the column number of 'Cell Name "Sales Price" in Sales Price_Column
Market Price_Column = Range ("Market Price"). Column Stores the column number of 'Cell Name "Market Price in the Market Price_Column
Range (Cells (1, Sales Price_Column), Cells (256, Sales Price_Column)). Formula = "= Cells (1,"&Market Price_Column&")"
'Substitute the formula = cell value of market price from the first row to the 256th row in the sales price column

.

  • Answer # 1

    Is this the case

    Range (Cells (1, Sales Price_Column), Cells (256, Sales Price_Column)). FormulaR1C1 = "= RC"&Market Price_Column


    Or such a hand?

    Range ("Sales Price"). Formula = "= Market Price"

  • Answer # 2

    The intent of the question is
    "Refer to each row of the market price column in the sales price column
    `` I want to insert a formula '',
    I wrote the code.

    Sub Refer to market price ()
        Dim Market Price_Column As Long: Market Price_Column = 1
        Dim Sales Price_Column As Long: Sales Price_Column = 2
        Dim i As Long
        For i = 1 To 256
            Cells (i, Sales Price_Column) .Formula = _
            "="&Cells (i, market price_column) .Address' (RowAbsolute: = False, ColumnAbsolute: = False)
        Next i
    End Sub


    If you don't need an absolute reference
    Please remove the single quotes in the comment.

  • Answer # 3

    It may be unwilling, but it can be achieved by processing one line at a time with the for next syntax.

    Option Explicit
    Option Base 1
    Sub hoge ()
        Dim Market Price_Column As Long: Market Price_Column = 1
        Dim Sales Price_Column As Long: Sales Price_Column = 2
        Dim Vlist () As Variant
        ReDim Vlist (256, 1)
        Dim cnt As Long
        For cnt = 1 To 256
          Vlist (cnt, 1) = CStr ("="&Cells (cnt, market price_column) .Address)
        Next cnt
        Range (Cells (1, sales price_column), Cells (256, sales price_column)). Formula = Vlist
    End Sub


    The code was revised based on 3109's response.
    Variant array speeds up the process of transferring to a cell.