Subtraction Without Formula Independently for Each Row

[excelnoobiez]

I currently have a range of cells I2:M2. There are no formulas in these cells, just values. I need to input a number in the corresponding cell in column H, hit ENTER, and have this number subtracted from the cells in column I2:M2, and display the result.

Example: I2 contains the number 15000. I type in the number 250 in cell H2 and hit ENTER. I2 should change to number 14750. J2 goes from 30000 to 29750. K2 goes from 45000 to 44750. L2 goes from 60000 to 59750. M2 goes from 150000 to 149750.

I need each row to have its own code so that the number I enter in column in H only affects that specific row.

Example: I3 contains the number 15000. I type in the number 250 in cell H3 and hit ENTER. I3 should change to number 14750. J3 goes from 30000 to 29750. K3 goes from 45000 to 44750. L3 goes from 60000 to 59750. M3 goes from 150000 to 149750.

Can someone help with this? I'm sure it has to be done in code.

Thank you in advance!

 

[Z51]

You can probably use the Worksheet_Change event like so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Dim ce As Range
    Application.EnableEvents = False
        For Each ce In Range("I" & Target.Row).Resize(, 5)
            ce.Value = ce.Value - Target.Value
        Next ce
    Application.EnableEvents = True
End If
End Sub

 

[Z51]

Another option without the (albeit small) loop:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Application.EnableEvents = False
    Target.Copy
    Range("I" & Target.Row).Resize(, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract
    Application.CutCopyMode = False
    Application.EnableEvents = True
End If
End Sub

 

[excelnoobiez]

You can probably use the Worksheet_Change event like so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Dim ce As Range
    Application.EnableEvents = False
        For Each ce In Range("I" & Target.Row).Resize(, 5)
            ce.Value = ce.Value - Target.Value
        Next ce
    Application.EnableEvents = True
End If
End Sub

This works well, thank you. Is there a way to have the cell automatically delete its contents after the command is run?

 

[Z51]

Yes, after the "Next ce" line, before re-enabling events, add:

Target.Value = ""

If using the second code option I provided (without the For/Next loop), add that line between the 'CutCopyMode' and 'EnableEvents' lines.

 

[excelnoobiez]

Yes, after the "Next ce" line, before re-enabling events, add:

Target.Value = ""

If using the second code option I provided (without the For/Next loop), add that line between the 'CutCopyMode' and 'EnableEvents' lines.

Perfect thanks!