Previous Topic Next Topic

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 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
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
Answer 2 / 2
MaxvdP
Jun 5, 2014 at 08:04 PM
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Formula to subtract one cell value from another.
autigers71 -
vcoolio -

1 response
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses