Seamus2626
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
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)"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Fellas,
Understand now.
Cheers,
Seamus
Understand now.
Cheers,
Seamus
ASKER
Thank you too Teylyn, just seen your response
Thanks
Seamus
Thanks
Seamus
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