VBA Excel Comparing Values in two different sheet

Question

I am looking to compare the values in two columns that are located in different sheets. When a match is found I want to decrease the value in a third column that is in the same row as the matched values. I know how to hard code it below but I would rather not have to include this code for every row in excel.

If Range("g12").Value = Worksheets("Inventory Levels").Range("b2").Value Then
    Worksheets("Inventory Levels").Range("c2").Value = Worksheets("Inventory Levels").Range("c2").Value - 1
End If

TylerH · Accepted Answer · 2017-04-23 22:06:10Z

1

You can do this in a simple loop, see my comments for details.

Dim i as Integer
' Use "With" to fully qualify your sheet objects.
With ThisWorkbook
' Loop over rows 2 to 20 (change this as necessary)
For i = 2 to 20
    ' Use ampersand (&) for concatenation of strings
    If .Sheets("Sheet1").Range("G" & (i + 10)).Value = .Sheets("Inventory Levels").Range("B" & i).Value Then
        .Sheets("Inventory Levels").Range("C" & i).Value = .Sheets("Inventory Levelts").Range("C" & i).Value - 1
    End If
Next i
End With

edited Apr 23, 2017 at 22:06

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Apr 23, 2017 at 21:43

Wolfie

30.7k7 gold badges30 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel Comparing Values in two different sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related