VBA VLOOKUP

Hi,

I have two VBA Editor Vlookups (attached)

In excel, the first lookup returns formulae:
=VLOOKUP(L2,'[Prime Dividends Checker.xls]PAID DIVS'!$D:$D,1,0)

The second returns:
=VLOOKUP(W2,'[Prime Dividends Checker.xls]PAID DIVS'!'E4',1,0)

The key difference in the returned formulae is the second does not search the column

I recorded the forst vlookup as a macro and then copy/pasted it for the second, adjusting columns


Why is there is a diffirence in result when the first formulae is the same in the editor?

Thanks
Seamus


.Range("AC2").Formula = "=VLOOKUP(RC[-17],'[Prime Dividends Checker.xls]PAID DIVS'!C4,1,0)"
    .Range("AD2").Formula = "=VLOOKUP(RC[-7],'[Prime Dividends Checker.xls]PAID DIVS'!E4,1,0)"

Open in new window

Seamus2626Asked:
Who is Participating?
 
Rory ArchibaldCommented:
C4 in the first stands for Column 4 as you are using R1C1 format. E4 can only be a cell reference. If you meant column E, you should use C5.
You should also use FormulaR1C1 rather than Formula.
0
 
Rob HensonFinance AnalystCommented:
The first formula is seeing the C4 as column 4 whereas the second is seeing the E4 as a cell reference.

With more info, might be able to correct it.

Regards
Rob H
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,
in this formula

=VLOOKUP(L2,'[Prime Dividends Checker.xls]PAID DIVS'!$D:$D,1,0)

the lookup range is the whole column D

In the second formula,

=VLOOKUP(W2,'[Prime Dividends Checker.xls]PAID DIVS'!'E4',1,0)

the lookup range consists of just one cell, E4. So unless the search term is present in that cell, the Vlookup will return an error.

The second formula in your code snippet also refers to just one cell as the lookup range. That won't work well. You need a range with more than one row for a meaningful result.

cheers, teylyn
0
 
Seamus2626Author Commented:
Thanks Fellas,

Understand now.

Cheers,
Seamus
0
 
Seamus2626Author Commented:
Thank you too Teylyn, just seen your response

Thanks
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.