我有两个工作表(sheet1和sheet2) . 两者都包含一个 Headers 为“ID”的列(列不总是在同一位置,因此需要找到) .
需要的是在“ID”列之前的新列中的vlookup .
这是我到目前为止所得到的
sub vlookup ()
FIND COLUMNS WITH "ID"-HEADER
'Set variables for Column Sku
'note: cfind1 is for sheet 1 and cfind 2 is for sheet 2
Dim col As String, cfind1 As Range, cfind2 As Range
column = "ID"
Worksheets(1).Activate
Set cfind1 = Cells.Find(what:=column, lookat:=xlWhole)
Worksheets(2).Activate
Set cfind2 = Cells.Find(what:=column, lookat:=xlWhole)
'CREATE COLUMN WITH VLOOKUP
'activate worksheet 1
Worksheets(1).Activate
'add column before sku-column
cfind1.EntireColumn.Insert
'Select cell 1 down and 1 to left of sku-cell.
cfind1.Offset(1, -1).Select
'Add VlookUp formulas in active cell
ActiveCell.Formula = "=VLOOKUP(LookUpValue, TableArray,1,0)"
'(Lookup_Value should refer to one cell to the right
(= cfind1.Offset (1, 0)??)
'Table_Array should refer to the column in sheet(2) with header "id"
'Autofill Formula in entire column
'???
End Sub
一切正常,直到“vlookup-part”我设法将公式放入正确的单元格中,但我无法让公式起作用 .
How can I set lookup_value as "one cell to the right" in the same sheet and "table_array" as the column with header "ID" in worksheet(2)?
And how can I finally autofill the vlookup formula throughout the whole column?
如果任何人可以帮助我使用正确的vlookup公式/变量和自动填充,那将是很好的 .
2 回答
如果您想避免使用工作表,也可以使用类似于下面的内容
当然需要更改值/变量 . lookup_value,数组(范围),列号,完全匹配 .
完全匹配需要false,类似匹配需要true
请尝试以下完整代码
之前没有这样做,但我的方法是使用cell或range.formula属性并构建您将在单元格中写入的字符串 . 例如:
myrange.formula = "=Vlookup("&Lookup_Value&","&Table_Array&","&Col_index_num&","&Range_Lookup&")"