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.
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?
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.
- vba - i want to use worksheetfunctionvlookup to get data from another sheet, but i can't refer to it
- how to refer the value of another sheet with vba form
- c ++ - please tell me how to pass the function argument to another file
- vba - i want to select a sheet and copy the contents to another sheet
- vba - post book name to another book without extension
- vba - i want to pass the value entered in the combo box to the dir function
- vba - macro stops when opening another macro file
- formula embedded in vba does not work
- i want to use the function of vba function "filedialog" on mac
- i want to transfer a value from another book using vba identifier
- i want to reference a value from another book with vba and replace a specific string
- i want to create a vba function in gas and make it work
- (go language) i want to call a function from another package in go
- python 3x - i want to import my own function from another file in the same directory in python 3 series
- questions about vba's weekday function
- i want to use a function defined in another file
- rust - i want to pass a function with a reference argument to another function
- i want to solve that the defined function cannot be used in vba function
- how to create source when posting from workbook to workbook in excel with vba macro
- vba - i want to divide the sheet by the same data
- error when using collection with vba as argument
- vba - an error occurs when searching for a numerical value using find and moving the corresponding cell
- vba - i want to create a macro that does not post data under certain conditions, but the conditions do not work
- vba - i want to create a member to use as an argument in my own class
- vba - draw a ruled line only for the table below
- formula embedded in vba does not work
- cannot read file with vba adodbstream
- about mouse over processing when chrome operation is performed with vba and selenium