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]

Can you give an example of the value you want to look for?

 

[steveh8204]

The following (sorry but adding image doesn't work) is the original table which looks up the value in column D and checks in the other table (the one from my original post) and enters the total quantity available (date is older than NOW) into a new colum at the end:

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

seems a bit of a complex one but any help would be appreciated.

Basically the value I'm looking up is a Purchase Order number. The items are delivered over staged intervals and I need to check that the quantity on the Suppliers Spreadsheet matches what has been ordered factoring in the expected delivery dates. The suppliers sheet just has the total number on the order and how many have been delivered (Source Table) so far. The other sheet (Excel Look up) is whats on our in-house system and is split up with a new line for every staggered delivery date. I hope that makes sense.

 

[DanteAmor]

Try this, results in sheet1 column "P".
Change data in red by your information.

Sub Comparing_List()
    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("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    
    u = sh1.Range("D" & Rows.Count).End(xlUp).Row
    For i = 3 To u
        wSuma = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(sh1.Cells(i, "D"), LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                If sh2.Cells(b.Row, "E").Value < Date Then
                    wSuma = wSuma + sh2.Cells(b.Row, "D").Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
        sh1.Cells(i, "[COLOR=#ff0000]P[/COLOR]").Value = wSuma
    Next
    MsgBox "End"
End Sub

 

[steveh8204]

Wow, thanks! Works like a dream, I was ready to give up on that. Was so easy to copy and paste straight into my sub. I can't thank you enough for that.

Can you recommend any good books to learn VBA? I've still got so much to learn but not sure of the best way (apart from the good advice on here of course). I've read the Dummies book but haven't been able to find any suitable courses at my local college even, they only go up to Advanced Excel (Pivot tables etc).

Thanks again for all your help you've given me up to now.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.

The best thing is to learn in the forums, everything starts with your need and search on that topic. The courses are good but if you do not use them they did not work.

 

[steveh8204]

Yea true, its always easier to learn something that's relevant to want you need. Thanks again for your help.

 

[steveh8204]

Try this, results in sheet1 column "P".
Change data in red by your information.

Sub Comparing_List()
    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("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    
    u = sh1.Range("D" & Rows.Count).End(xlUp).Row
    For i = 3 To u
        wSuma = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(sh1.Cells(i, "D"), LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                If sh2.Cells(b.Row, "E").Value < Date Then
                    wSuma = wSuma + sh2.Cells(b.Row, "D").Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
        sh1.Cells(i, "[COLOR=#ff0000]P[/COLOR]").Value = wSuma
    Next
    MsgBox "End"
End Sub

I've just been troubleshooting a problem I've had and some of it has come from this code not drawing off certain rows which seems weird. The information it draws from is definitely there so I'm not sure why it doesn't. I've tried stepping through the code but I don't really understand most of it if I'm honest so I was wondering whether you would be able to offer any help with it please?

One example is row 10 in the "Graphic" sheet in the file below. The button at the top runs the macro.

https://www.dropbox.com/s/v6f4ypsaxa8747v/Stock Sheet Checker Graphic Fail.xlsm?dl=0

 

[DanteAmor]

The macro has the condition of adding only the values ​​that are less than today's date.
In your example, row 10 is dated May 17, May 17 is not less than May 7, so do not add that value.

 

[steveh8204]

OK I understand, I've just read the original post and realised that's what I put in the criteria. I've only just realised that it causes problems when a supplier decides to deliver early.

How do I remove this part and just make it calculate the difference regardless of date as I later discovered it isn't relevant (probably why I forgot I'd originally asked for it, whoops sorry).

Thanks