Application.Vlookup

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….

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

Worksheet2
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..

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

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

Bert

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

Bert

Sub Ratio()
    
    Dim Ratio As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim Results As Worksheet
    Dim LookupTable As Range
    Dim Target As Range
    Dim LookupResult As Variant
    Dim Item As Variant


    
    Set Ratio = ActiveWorkbook
    With Ratio
        Set wsTarget = .Worksheets("Order Data")
        Set wsSource = .Worksheets("Ratio Lookup")
    End With
    
    With wsSource
        Set LookupTable = .Range(.Range("A1"), .Range("C65536").End(xlUp))
    End With
    
    
    'Not sure how to put loop in here (If that's what's needed!!)
    With wsTarget
        Set Item = .Range(.Range("A1"))
    
    LookupResult = Application.VLookup(Item, LookupTable, 2, False)
      
    
    Application.ScreenUpdating = False
     
    Sheets("Results").Range("A65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Value = LookupResult

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.
Thanks
Bert

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?

Thanks
Bert

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.

Bert

Re: Application.Vlookup

Hi

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

Bert

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.

Re: Application.Vlookup

Thanks Krishnakumar

That great and thanks for the explanations alongside the code - very helpful

Bert