Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

Tables'!C[4]:C[5],2,FALSE)"

Selection.AutoFill Destination:=Range(Cells(KeyStartRow,

DescriptionCol), Cells(KeyEndRow, DescriptionCol))

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

'!C[4]:C[5],2,FALSE)"

or:

Selection.Formula = "=VLOOKUP(" & Cells(KeyStartRow,

TitleCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) &

",'Ref.Tables'!D:E,2,FALSE)"

It doesn't work because in

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

Tables'!C[4]:C[5],2,FALSE)"

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

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

too?

The same happened to me, just make sure the name of the sheet is

exactly right in the formula, spaces'n'all.

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 &

",'Ref.Tables'!C[4]:C[5],2,FALSE)"

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.

Related Topics:

- VLOOKUP in VBA
- Vlookup in vba when lookup fails
- Problem with VLookup in VBA
- VBA Vlookup returning wrong values
- VLookup in VBA
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- vlookup with two imputs
- vlookup and error
- vlookup with two imputs
- Help required in Vlookup
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- Vlookup returns #NA
- Perform a function based on a VLookup
- VLOOKUP Use in Macros
- Newbie VLOOKUP with dates question
- Help needed in VLOOKUP
- vlookup and the value #N/A
- using vlookup in userform
- Help with vlookup in vba, how to deal with errors?
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- Placing VBA Code "Live" Into Another WkBook Via VBA