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
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
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

Similar discussions

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

1 response