Excel Macro to search for specific cell and subtract value

Closed
MaxvdP
Posts
1
Registration date
Wednesday June 4, 2014
Status
Member
Last seen
June 4, 2014
- Jun 4, 2014 at 05:36 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 10, 2014 at 10:28 AM
Good evening guys,

My first question here. I'm being beaten by an Excel Macro, I've done some before but this appears to be beyond me.

I'm trying to create a macro that will search one spread sheet for a specific code created by another spreadsheet. Once it matches the two, I want it to subtract the value that's associated to the first code from the value associated with the second code.

An example...

I type in ABC into a cell on spreadsheet 2 (has several codes and values) and a value of 3 comes up in a different cell. I want to find ABC in spreadsheet 1 (has all codes and different values) which has a value of 10 in the same row and subtract 3 from it. I need this to happen for several values in spread sheet 1 and 2, moving through rows in spreadsheet 2 and subtracting from all associated specific codes in spreadsheet 1.

Thanks for any help you can provide. I'm sure it involves a lookup and a loop but I'm just not sure where to start... and then where to go from there.

Max

2 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 5, 2014 at 11:03 AM
Hi Max,

What column do you use for your "codes" and what column for your values or are they scattered across your sheets?

Best regards,
Trowa
0
Good morning Trowa,

The information is in columns. So the code (serial number) on the first sheet is in one column with the associated number (items used) in a column next to it. It's the same on the other sheet, the codes are down one column, with the number associated with it (items in stock) in a column next to it.

Thanks for any help you can give,

Max
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 10, 2014 at 10:28 AM
Hi Max,

You didn't provide the specifics.
So the first sheet is named "Sheet1". The codes are in column A and the values are in column B. Row 1 used as header. Same setup for the second sheet which is called "Sheet2". The subtractions will happen on sheet1.

Here is the code:
Sub RunMe()
Dim x As Integer
Dim lRow1, lRow2 As Long

lRow1 = Sheets("Sheet1").Range("A1").End(xlDown).Row
lRow2 = Sheets("Sheet2").Range("A1").End(xlDown).Row

Sheets("Sheet2").Select

For Each cell In Range("A2:A" & lRow2)
    x = 1
    Do
        x = x + 1
        If cell.Value = Sheets("Sheet1").Cells(x, "A") Then
            Sheets("Sheet1").Cells(x, "B").Value = Sheets("Sheet1").Cells(x, "B").Value - cell.Offset(0, 1).Value
        End If
    Loop Until x = lRow1
Next cell

End Sub


Best regards,
Trowa
0