• Hello all

    Having difficulties using application.vlookup in a script and hoped that someone on her could help.

    A simplified version of my problem is as follows….

    Item1 Qty1
    A1 2
    A2 4
    A3 1
    A4 5
    B1 1
    B2 1
    B2 3
    C1 3
    C2 8
    C3 1…..

    Item2 Lookup Qty2
    A1 A1/R 1
    C2 C2/R 2
    F1 F1/R 2…..

    I need to perform a lookup from ‘Item1’ against ‘Item2’ and return the value‘Lookup’ AND the associated value ‘Qty1’ from Worksheet1 needs to be divided against ‘Qty2’ in worksheet2. The results to be shown in a third worksheet, so for the above it would be..

    Lookup Qty3
    A1/R 2
    C2/R 4….

    Thanks for your time reading – if this doesn’t make sense please let me know


  • Re: Application.Vlookup

    Can you post your code?

    As a preliminary suggestion try:

    Application.WorksheetFunction.VLookup(your criteria)

    Will look into it further and see if I can find a more specific solution


    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Application.Vlookup

    Thanks Alastair

    Now here's the thing, from what I've done so far you will see that I'm actually not very good at VB - For what it's worth I've attached it below - any help would be great though.
    Thanks again


  • Re: Application.Vlookup

    Is there any particular reason why you want to use VBA not a formula?

    See attached sheet for a formula answer (will start working on VBA)

    Hope this helps


  • Re: Application.Vlookup

    Thanks Alastair

    Need it in VBA because the spreadsheet (Order Data) will be updated many times a day - and the user responsible will not have the time. My intention was to assign it to a button so that when new data is dropped into "Order Data" the process can be quickly achieved with a push of a button.
    Really appreciate your time on this.

    PS Look forward to seeing if I was just a little bit right with my attempt!!

  • Re: Application.Vlookup

    Hi Alastair

    Sorry, has just occurred to me that it is possible that sheet 'Order Data' could contain two identical valid items for different quantities ie

    Item1 Qty1
    A1 4
    C1 8
    A1 2

    Both would need to be included in the 'Results' worksheet, but both should have different calculated Qty results - . vlookup would always calculate on the first instant that it finds?? Does this mean that it cannot be done this way?


  • Re: Application.Vlookup

    I think Alistair has gone offline for a bit. Based on your last few posts, I'm also not sure why you need VBA. Have a look at the attached. It uses SUMPRODUCT to catch multiple entries of data on sheet "Order Data." As long as users input data with the range encompassed by SUMPRODUCT (BTW: SUMPRODUCT will not accept an entire column as a legitimate argument) the formulas will update immediately. No need to push a button.

    To get classier, use a Dynamic Named Range in SUMPRODUCT that adjusts to the actual data content in column A (assuming there will not be any blank cells in column A among the data to be counted).

    I put the soultion on sheet "Order Data" but it can go anywhere.

  • Re: Application.Vlookup

    Thank you both very much. You have both come up with a solution for the problem that I presented, but I don’t think that I explained all very well.

    The problem is that the ‘Results’ worksheet cannot contain data such as ‘N/A’. I need the results to ignore any rows of irrelevant data – so in Thomach spreadsheet the results will be just the 5 rows of data. This is needed because the results need to be loaded into a database in .CSV form. I was going to incorporate this in the end of the VB script. If either of you or anyone else knows how to do this is VB then please let me know. I know that the results are easily sorted and the required data extracted but this has to be done so often that it becomes labour intensive.
    Again, thanks Alistair and Thomach for what you’ve done.


  • Re: Application.Vlookup


    Just as quick addition SUMPRODUCT will not work because I need to deal with each line separately. So if in ‘Order Data’ I have two lines next to each other ie

    Item1 Qty1
    A1 -------- 2
    A1 -------- 8

    Then Qty1 for each line needs to be divided by the look up value for A1 for example

    Item2 Qty2
    A1 -------- 2

    So finally the Results worksheet will show

    Item3 Qty3
    A1 -------- 1 --- ( 2/2)
    A1 -------- 4 --- ( 8/2)

    Sorry, really making a mess of explaining!!

  • Re: Application.Vlookup

    Thanks HTH

    Do you mean use a VB macro to run the filtering process?? Yes, that would work and I will use your example....unless someone knows how do do the whole process with VB script - Also to explain, I'm interested in learning how to correctly write script so would be interested see how it would look/structure.

    Thanks everyone


  • Re: Application.Vlookup

    So, a little bit embarrassed then – not up on my short hand and don’t have a mobile phone, so text language is out.

    Apologies, Krishnakumar and thanks for the link.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!