locked
VB code to replicate a VLOOKUP function RRS feed

  • Question

  • Hi there,

    I've read quite a lot of the posts on this forum but can't find one that helps me answer my specific question, so apologies for reposting.

    I am using Excel 2003.

    I have a two-sheet workbook - Sheet 1 is my data entry sheet and Sheet 2 is my lookup sheet.

    On Sheet 1, I have a column (Column D) with a drop down list and want to automatically populate Column C with data from my lookup sheet once a selection is made in Column D. I can do this using VLOOKUP but I will have approximately 15000 rows and if I copy the formula down that far the workbook size increases dramatically.

    A) Will using VB reduce the overall file size?
    B) Can I use VB in this instance and what code should I use if so?

    Thanks for your assistance

    Thursday, July 5, 2012 10:29 AM

Answers

  • Yes, you have pointed out one weakness in the Excel front-end that is easily overcome with some VBA.

    Just use the SelectionChange event handler in the worksheet to call the VBA code which can use MATCH or FIND functions.
    Then poke the result into the cells where the formulas would have gone.

    • Proposed as answer by Leo_Gao Tuesday, July 10, 2012 6:28 AM
    • Marked as answer by Leo_Gao Wednesday, July 11, 2012 1:12 AM
    Thursday, July 5, 2012 11:42 AM

All replies

  • Yes, you have pointed out one weakness in the Excel front-end that is easily overcome with some VBA.

    Just use the SelectionChange event handler in the worksheet to call the VBA code which can use MATCH or FIND functions.
    Then poke the result into the cells where the formulas would have gone.

    • Proposed as answer by Leo_Gao Tuesday, July 10, 2012 6:28 AM
    • Marked as answer by Leo_Gao Wednesday, July 11, 2012 1:12 AM
    Thursday, July 5, 2012 11:42 AM
  • Hi Tigger,

    First when you say you have a column (Column D), do you mean a cell in which there is a drop down list in column D and not an actual column full of drop down lists? Or do you mean you have column of "lookup" values that can be updated by selecting something from a drop down list, and you then want your column C to look-up whatever is in Column D for each row? (I assume it`s the latter)

    My first reaction would be to try to using the offset and match functions - much quicker than vlookup, however that wouldn`t necessarily solve your size problem.

    Your second solution could be to use VB to loop down the rows and use the xlworksheetfunction in VB to do your vlookup (or offset-match) for you, that would then prevent the need to expand the vlookup, however I`m not sure how quick that would be (potentially slower than the excel solution). Depending on your answer to my first question, if it is option 2 then that would still not save you much size as you would simply have text in there and not a formula which will make little difference to the size of the workbook (my guess) - therefore not worth using VB.

    Will happily provide examples of code if you can clarify your situation.

    Cheers!


    If it`s not one thing it`s another...

    Thursday, July 5, 2012 12:02 PM