I am trying to write a vba code to fill a column with vlookup formula in an excel spreadsheet. I tried the autofill function, yet it only increased 2 row numbers repetitively based on the formulas in the first two cells in the range. How can I have “autofill” or some other functions to fill the whole column automatically, meaning increasing the row numbers accordingly?
Refer to the following sample code that describes my problem:
Range(“K12”).Formula = “=IF(ISERROR(VLOOKUP($J$12, EMPLOYEE_LIST, 2, FALSE)),” _
& Chr(34) & “Unknown” & Chr(34) & “, VLOOKUP($J$12, EMPLOYEE_LIST, 2, FALSE))”
Range(“K13”).Formula = “=IF(ISERROR(VLOOKUP($J$13, EMPLOYEE_LIST, 2, FALSE)),” _
& Chr(34) & “Unknown” & Chr(34) & “, VLOOKUP($J$13,EMPLOYEE_LIST, 2, FALSE))”
Set SourceRange = .Range(“K12:K13”)
Set fillRange = .Range(“K12:K61”)
SourceRange.AutoFill Destination:=fillRange
Another question: say I have a employee_name_list column. After I applied a validation method tothis column, how can I jump directly to the record I want by typing the initial letter in the drop-down list?