I'll do my best to explain this. I'm working in a spreadsheet that contains roughly 150 columns and 2,500 rows. In column D, there is an identification number that is unique to that line. I need to filter on that column for a specific ID number (say B5555) then subtract the value in column AH of that row from the values in columns AB, O and M of that row. Then I need to zero out the value in column AH.
Filtering for the ID number is no problem but B5555 could show up in any row. IE, one week it shows up in row 2,300 and the next it is in 1,500, so I can't just make the formula say subtract AH2300 from AB 2300 because the next week that will act on the wrong cells. How do I make the formulas subtract specifically from the line that B5555 shows up in? The code I have so far only filters for that ID number so it isn't much but is shown below. I'm new to VBA os nay help would be much appreciated.
Sub ManualAdjustments()
Dim wbTarget As Workbook
Dim wbThis As Workbook
Dim strName As String
Dim rownum As Integer
Set wbTarget = Workbooks("Weekly Data")
wbTarget.Activate
Worksheets("Raw").Activate
rownum = Application.WorksheetFunction.Match("B5555",
Sheets("Raw").Range("D:D"), 0)
The above code is what I have so far. How do I reference "rownum" in a simple subtraction formula? Would it be: Worksheets("Raw").Range("AHrownum")-Worksheets("Raw").Range("ABrownum")
You could do this as below:
Sub ManualAdjustments()
Dim rownum As Long
Dim ws As Worksheet: Set ws = Worksheets("Raw")
'Get the row number where value "B5555" is found in Column D
rownum = ws.Range("D:D").Find(What:="B5555", Lookat:=xlWhole).Row
'Subtract AH minus AB, O & M
NewValue = ws.Range("AH" & rownum) - ws.Range("AB" & rownum) - ws.Range("O" & rownum) - ws.Range("M" & rownum)
MsgBox "Your New Value is: " & NewValue
End Sub