VBA and passing values

Question

I'm trying to compare data between two worksheets. Each Worksheet has three column: A is a concatenation of a Customer and a SKU, B is the sales volume and C is for measuring volume discrepancies. I aim to do two things, check Sheet1 for SKUs that are not in Sheet2 and then, if SKUs match on both sheets, check their volume for quantity differences. If Sheet 1 has a SKU not in Sheet2, I want the record highlighted. I've accomplished this in a primitive way, the entire row gets highlighted. I am, however, having trouble getting the code to check volumes if the Customer & SKU match. I was hoping VBA would retain the values of the cells it was checking, where have I gone wrong and what is the proper implementation? Sorry for being such a n00b.

Sub Again()

Dim lastRow As Integer
Dim rng As Range
lastRow = Sheets("Sheet1").Range("A65000").End(xlUp).Row

For i = 1 To lastRow
    Set rng = Sheets("sheet2").Range("A:A").Find(Sheets("Sheet1").Cells(i, 1))
    If rng Is Nothing Then
        Sheets("Sheet1").Cells(i, 3) = "Item not in sheet2"
        Sheets("Sheet1").Cells(i, 1).EntireRow.Interior.Color = vbRed
    ElseIf Not rng Is Nothing Then
        If Sheets("sheet1").Cells(i, 2).Value - Sheets("sheet2").Cells(i, 2).Value < -5 Then
            Sheets("sheet1").Cells(i, 3) = "Sheet2 reports " & Sheets("sheet1").Cells(i, 2).Value - Sheets("sheet2").Cells(i, 2).Value & " more units of volume."
        ElseIf Sheets("sheet1").Cells(i, 2) - Sheets("sheet2").Cells(i, 2) > 5 Then
            Sheets("sheet1").Cells(i, 3) = "Sheet1 reports " & Sheets("sheet1").Cells(i, 2) - Sheets("sheet2").Cells(i, 2) & " more units of volume."
        Else: Sheets("sheet1").Cells(i, 3) = "No or insignificant discrepancy"
        End If
    End If
Next

End Sub

You found a range which matched with the set rng =.... Use that variable rng again somewhere?! Like Dim foundRangeRow as long....foundRangeRow=rng.Row... etc — MacroMarc

Brendan.H Brendan.H · Accepted Answer · 2018-06-24T07:07:23

I think you need to reuse rng like this:

rng.offset(2,0).value

in place of:

Sheets("sheet2").Cells(i, 2).Value

Because all your currently doing is assuming that the matching cell is in exactly the same row as in sheet1.

Your code should then look something like this:

Sub Again()

Dim lastRow As Integer
Dim rng As Range
lastRow = Sheets("Sheet1").Range("A65000").End(xlUp).Row

For i = 1 To lastRow
    Set rng = Sheets("sheet2").Range("A:A").Find(Sheets("Sheet1").Cells(i, 1))
    If rng Is Nothing Then
        Sheets("Sheet1").Cells(i, 3) = "Item not in sheet2"
        Sheets("Sheet1").Cells(i, 1).EntireRow.Interior.Color = vbRed
    ElseIf Not rng Is Nothing Then
        If Sheets("sheet1").Cells(i, 2).Value - rng.offset(0, 2).Value < -5 Then
            Sheets("sheet1").Cells(i, 3) = "Sheet2 reports " & Sheets("sheet1").Cells(i, 2).Value - rng.offset(0, 2).Value & " more units of volume."
        ElseIf Sheets("sheet1").Cells(i, 2) - rng.offset(0, 2).Value > 5 Then
            Sheets("sheet1").Cells(i, 3) = "Sheet1 reports " & Sheets("sheet1").Cells(i, 2) - rng.offset(0, 2).Value & " more units of volume."
        Else: Sheets("sheet1").Cells(i, 3) = "No or insignificant discrepancy"
        End If
    End If
Next
End Sub

VBA and passing values

2 Answers