Web Development

Ask something
Discussion Locked

Excel VBA - Vlookup & Validation List

0 helpful
3 replies

by maine kebo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by shanghai sam

First of all... IN VBA the quotes around "Unknown" screw up setting the formula when setting the formula property in VBA...

I've tried to figure out a way to put the quotes in the formula whilst still using VBA, no luck...

I used the "ISTEXT" formula instead of "ISERROR":

"=IF(ISTEXT(VLOOKUP(J12, Employee_List, 2, FALSE))=FALSE, CHAR(34) & "Unknown User" & CHAR(34), VLOOKUP(J12, Employee_List, 2, FALSE))"

**************************************************
The above formula MUST reside in cell K12 for the AutoFill command to work... Also, remove the "." before the "Range" command...
**************************************************

Set SourceRange = Range("K12")
Set fillRange = Range("K12:K61")
SourceRange.AutoFill Destination:=fillRange

Then, if you leave the ("$") around the J12 cell reference, then it is always going to look to cell J12 for the lookup value. Remove the ("$")s around the cell reference "J12" for the lookup to use the cell reference "J13"...

For the future, review the help for "Absolute References"...

The way you have the formula currently is that its always going to be looking at either cells J12 or J13 for the name or username of the employee. Remove the "$"s and the autofill should work down the column.

This discussion is closed to new replies.