Results 1 to 13 of 13
  1. #1
    Silver Lounger
    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

  2. #2
    Super Moderator WebGenii's Avatar
    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

  3. #3
    Plutonium Lounger
    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.

  4. #4
    Silver Lounger
    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

  5. #5
    Silver Lounger
    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
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    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.
    Attached Files Attached Files

  7. #7
    Silver Lounger
    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

  8. #8
    Silver Lounger
    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 trade-off in calculation time, doing it in VBA would seem to make more sense.

    Regards,
    John

  9. #9
    Plutonium Lounger
    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.

  10. #10
    Silver Lounger
    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.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    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

  12. #12
    Silver Lounger
    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

  13. #13
    WS Lounge VIP rory's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •