Comparing 2 Lists and using Vlookups with multiple rows.

[steveh8204]

https://drive.google.com/open?id=1TfqRi0g6FwDCY0iNOfVWOyLhAXgBPygR

I'm trying to write a VBA sub to look up the value of two references on different sheets. The problem I've got is the value being looked up has entries in more than one row which I believe is difficult for this command.

I was hoping to use a given number, look it up and gain every quantity for the value with a date range that hasn't occurr

ed yet (highlighted in yellow on attached picture).

Has anyone got any advice on the best way to do this.

So to clarify I need a number from Sheet B (which also has a total quantity) and look it up in the table pictured adding up all the values from dates in the past (which in this example would total 2,030,938). I know what you are thinking, I'm not asking for much. I've tried to learn everything from other resources but it's proved to be too much for my relatively new VBA knowledge base.

Thanks in advance for any help.

 

[DanteAmor]

Try this

Sub Comparing_Totals_Graphic()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim u As Long, i As Long, wSuma As Double
    Dim r As Range, b As Range, celda As String
    
    Set sh1 = Sheets("Graphic")
    Set sh2 = Sheets("SAP Graphic")
    
    u = sh1.Range("F" & Rows.Count).End(xlUp).Row
    For i = 3 To u
        wSuma = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(sh1.Cells(i, "F"), LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                wSuma = wSuma + sh2.Cells(b.Row, "D").Value
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
        sh1.Cells(i, "P").Value = wSuma
    Next
End Sub

Or this

Sub Macro3()
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = Sheets("Graphic")
    Set sh2 = Sheets("SAP Graphic")
    With sh1.Range("P3:P" & sh1.Range("F" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=SUMIF('SAP Graphic'!C[-15],RC[-10],'SAP Graphic'!C[-12])"
        .Value = .Value
    End With
End Sub

 

[steveh8204]

Nice one. I'll try when the daughter has finished her homework on my laptop. Thanks again for your help, it is much appreciated.

 

[DanteAmor]

You're welcome