?
Solved

VBA VLOOKUP

Posted on 2011-04-26
5
Medium Priority
?
523 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Seamus2626
5 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35466033
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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 35466034
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
 
LVL 50
ID: 35466043
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
 

Author Closing Comment

by:Seamus2626
ID: 35466051
Thanks Fellas,

Understand now.

Cheers,
Seamus
0
 

Author Comment

by:Seamus2626
ID: 35466326
Thank you too Teylyn, just seen your response

Thanks
Seamus
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question