MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Vlookup in VBA

  Asked By: Geeske    Date: Feb 01    Category: MS Office    Views: 1233

Please look at the code below and let me know why the VLOOKUP does not
recognize the lookup value. It seems to me that the VLookup cannot have
a variable in the parentheses but I was not able to delete them. I need
the Vlookup function to be functinoable in the cell after the code is
run. Thanks!

Cells(KeyStartRow, DescriptionCol).Select

Selection.FormulaR1C1 = "=VLOOKUP(Cells(KeyStartRow, TitleCol),'Ref.

Selection.AutoFill Destination:=Range(Cells(KeyStartRow,
DescriptionCol), Cells(KeyEndRow, DescriptionCol))



5 Answers Found

Answer #1    Answered By: Sairah Hashmi     Answered On: Feb 01

You're trying to enter formulae into the worksheet cells with the word
'Cells' in as well as a few other variables which you may not have
defines as Names.

Try something on the lines of:
Selection.FormulaR1C1 = "=VLOOKUP(" & Cells(KeyStartRow,
TitleCol).Address(ReferenceStyle:=xlR1C1) & ",'Ref.Tables

Selection.Formula = "=VLOOKUP(" & Cells(KeyStartRow,
TitleCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) &

Answer #2    Answered By: Rosa Reynolds     Answered On: Feb 01

It doesn't work because in
Selection.FormulaR1C1 = "=VLOOKUP(Cells(KeyStartRow, TitleCol),'Ref.

You don't want KeyStartRow. You want the current row.

Answer #3    Answered By: Geldefsman Bakker     Answered On: Feb 01

This almost fixed my problem. It looks at the cell  I would like but now
instead of looking at the worksheet Ref. Tables that is in the same
workbook it looks for a totally new workbook. Can we fix that problem

Answer #4    Answered By: Von Fischer     Answered On: Feb 01

The same happened to me, just make sure the name of the sheet is
exactly right in the formula, spaces'n'all.

Answer #5    Answered By: Lenora Green     Answered On: Feb 01

KeyStartRow and TitleCol are local variables in your program, but you are
putting them in the formula as part of the quoted static part of the
formula. I.e. the formula becomes

=VLOOKUP(Cells(KeyStartRow, TitleCol),Ref.Tables!K:L,2,FALSE)

In fact the whole "Cells(KeyStartRow, TitleCol)" is wrong. You need a cell
address. E.g.

Selection.FormulaR1C1 = "=VLOOKUP(R" & KeyStartRow & "C" & TitleCol &

I'm not sure what you mean by 'Ref.Tables'!C[4]:C[5] either. That's a
relative reference 4 and 5 columns to the right of some unspecified position
in the Ref.Tables sheet. Do you want absolute references?

Didn't find what you were looking for? Find more on Vlookup in VBA Or get search suggestion and latest updates.