Results 1 to 13 of 13
Thread: VLookup in VBA (03)

20070213, 18:28 #1
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
VLookup in VBA (03)
Using VBA is it possible to perform a VLookup based on two columns? The lookup should be based on both account and product (see table below). An example of an account/product combination would be: 602000.50 and if I am looking for the total it would return 1,000.
<table border=1><td>Account</td><td>Product</td><td>Qtr 1</td><td>Qtr 2</td><td>Qtr 3</td><td>Qtr 4</td><td>Total</td><td>604320</td><td>NoProd</td><td>10</td><td>20</td><td>30</td><td>40</td><td>100</td><td>602000</td><td>50</td><td>100</td><td>200</td><td>300</td><td>400</td><td>1000</td></table>
Thanks,
John

20070213, 18:39 #2
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: VLookup in VBA (03)
But what would the desired result be if the account was 604320 and the product was 50?
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20070213, 18:50 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLookup in VBA (03)
Do you have a specific reason to want to do this in VBA? It's possible to use a formula for this.

20070213, 18:50 #4
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Catharine,
In my example I would be looking for "Total" resulting in a returned value of 1,000
Regards,
John

20070213, 18:56 #5
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Hans,
On second thought, It most likely can be done without VBA.
I wanted to express a value in the cell not a formula. I do not want the End User to trace the formula to a veryHidden sheet. I can hide the formula by formatting the cell(s) with hidden.
Regards,
John

20070213, 19:08 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLookup in VBA (03)
The attached workbook contains a formula solution. The worksheet is not protected, so you can see the formula.

20070213, 19:21 #7
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Thank you,
John

20070213, 22:07 #8
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Hans,
Do to the sheer number of cells that now have formulas, I am encountering a performance challenge in the length of time it takes the workbook to calculate. As an example, I have code that hides/displays rows whose totals are zero. Included in the code for hiding rows, I have set the ScreenUpdating to FALSE and Calculation to MANUAL.. This works just fine as the actual time to hide the rows is minimal. The performance impact is when I reset the Calculation to AUTOMATIC and the entire workbook is calculated. I have tried calculating just the worksheet but there is not much of a difference in calc time.
Although there is a tradeoff in calculation time, doing it in VBA would seem to make more sense.
Regards,
John

20070213, 22:32 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLookup in VBA (03)
We'll have to know how exactly you are using this. If at all possible, you should attach a sample workbook.

20070213, 23:14 #10
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Hans,
Please find attached a sample workbook. It should provide a good example of what the code is doing although the items listed in both sheets have been reduced substantially. On the working copy, each sheet has approximately 2,000 rows.

20070213, 23:31 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLookup in VBA (03)
Try this macro. It'll be very slow on the real data, but you'll only have to run it when the source data have changed.
Sub LookEmUp()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngSourceRow As Long
Dim lngTargetRow As Long
Dim lngMaxSourceRow As Long
Dim lngMaxTargetRow As Long
' Set references to worksheets
Set wshSource = Worksheets("Source")
Set wshTarget = Worksheets("Sheet1")
' Determine last used rows
lngMaxSourceRow = wshSource.Range("A65536").End(xlUp).Row
lngMaxTargetRow = wshTarget.Range("D65536").End(xlUp).Row
' Loop through target rows
For lngTargetRow = 4 To lngMaxTargetRow
If Not wshTarget.Range("D" & lngTargetRow) = "" Then
' Clear target cell
wshTarget.Range("F" & lngTargetRow).ClearContents
' Loop through source rows
For lngSourceRow = 2 To lngMaxSourceRow
' Compare Account and Product
If wshTarget.Range("D" & lngTargetRow) = _
wshSource.Range("A" & lngSourceRow) And _
wshTarget.Range("E" & lngTargetRow) = _
wshSource.Range("B" & lngSourceRow) Then
' Copy value of Total
wshTarget.Range("F" & lngTargetRow) = _
wshSource.Range("G" & lngSourceRow)
Exit For
End If
Next lngSourceRow
End If
Next lngTargetRow
End Sub

20070213, 23:37 #12
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup in VBA (03)
Excellent! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
Regards,
John

20070213, 23:54 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,280
 Thanks
 3
 Thanked 191 Times in 177 Posts
Re: VLookup in VBA (03)
If you use a helper column in the source data to perform the concatenation there, then your INDEX/MATCH formula becomes simpler and does not need to be an array formula, which should also speed up calculation time.
FWIW.Regards,
Rory
Microsoft MVP  Excel