Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Seamus2626

ASKER

Thanks Fellas,

Understand now.

Cheers,
Seamus
Thank you too Teylyn, just seen your response

Thanks
Seamus