add / subtract new price in cell to multiple cells in column based on two cells values

[abdo meghari]

Hello,
I want macro to add new price to multiple cells in column E based on H2 after adding should clear H2 cell
example:

Microsoft Excel‫‬.xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL		ADD	SUBTRACT
2	1	1221	BS 205/70R15C R623 THI	62.00	405.00	25,110.00
3	2	1227	BS 215/70R15C R623 THI	22.00	425.00	9,350.00
4	3	1227	BS 215/70R15C R623 THI	4.00	544.00	2,176.00
5	4	1241	BS 1200R20 G580 JAP	220.00	2,035.00	447,700.00
6	5	1284	GC 1200R20 AZ0026 CHI	80.00	895.00	71,600.00
7	6	1284	GC 1200R20 AZ0026 CHI	50.00	1,125.00	56,250.00
8	7	1285	GC 1200R20 AZ0183 CHI	40.00	925.00	37,000.00
9	8	1285	GC 1200R20 AZ0183 CHI	10.00	1,225.00	12,250.00
10	9	1285	GC 1200R20 AZ0183 CHI	60.00	1,205.00	72,300.00
11	10	1385	GC 315/80R22.5 AT161 CHI	20.00	735.00	14,700.00
12	11	1294	GC 315/80R22.5 AZ188 CHI	20.00	745.00	14,900.00
13	12	1287	GC 315/80R22.5 AZ126 CHI	60.00	735.00	44,100.00
14	13	1305	BS 700R16 R230 JAP	2.00	770.00	1,540.00
15	14	1306	BS 750R16 R230 JAP	10.00	775.00	7,750.00
16	15	1306	BS 750R16 R230 JAP	4.00	780.00	3,120.00
17	TOTAL					819,846.00
BRANDS

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2
F17	F17	=SUM(F2:F16)

fill H2

Microsoft Excel‫‬.xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL		ADD	SUBTRACT
2	1	1221	BS 205/70R15C R623 THI	62.00	405.00	25,110.00		10
3	2	1227	BS 215/70R15C R623 THI	22.00	425.00	9,350.00
4	3	1227	BS 215/70R15C R623 THI	4.00	544.00	2,176.00
5	4	1241	BS 1200R20 G580 JAP	220.00	2,035.00	447,700.00
6	5	1284	GC 1200R20 AZ0026 CHI	80.00	895.00	71,600.00
7	6	1284	GC 1200R20 AZ0026 CHI	50.00	1,125.00	56,250.00
8	7	1285	GC 1200R20 AZ0183 CHI	40.00	925.00	37,000.00
9	8	1285	GC 1200R20 AZ0183 CHI	10.00	1,225.00	12,250.00
10	9	1285	GC 1200R20 AZ0183 CHI	60.00	1,205.00	72,300.00
11	10	1385	GC 315/80R22.5 AT161 CHI	20.00	735.00	14,700.00
12	11	1294	GC 315/80R22.5 AZ188 CHI	20.00	745.00	14,900.00
13	12	1287	GC 315/80R22.5 AZ126 CHI	60.00	735.00	44,100.00
14	13	1305	BS 700R16 R230 JAP	2.00	770.00	1,540.00
15	14	1306	BS 750R16 R230 JAP	10.00	775.00	7,750.00
16	15	1306	BS 750R16 R230 JAP	4.00	780.00	3,120.00
17	TOTAL					819,846.00
BRANDS

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2
F17	F17	=SUM(F2:F16)

RESULT

Microsoft Excel‫‬.xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL		ADD	SUBTRACT
2	1	1221	BS 205/70R15C R623 THI	72.00	405.00	29,160.00
3	2	1227	BS 215/70R15C R623 THI	32.00	425.00	13,600.00
4	3	1227	BS 215/70R15C R623 THI	14.00	544.00	7,616.00
5	4	1241	BS 1200R20 G580 JAP	230.00	2,035.00	468,050.00
6	5	1284	GC 1200R20 AZ0026 CHI	90.00	895.00	80,550.00
7	6	1284	GC 1200R20 AZ0026 CHI	60.00	1,125.00	67,500.00
8	7	1285	GC 1200R20 AZ0183 CHI	50.00	925.00	46,250.00
9	8	1285	GC 1200R20 AZ0183 CHI	20.00	1,225.00	24,500.00
10	9	1285	GC 1200R20 AZ0183 CHI	70.00	1,205.00	84,350.00
11	10	1385	GC 315/80R22.5 AT161 CHI	30.00	735.00	22,050.00
12	11	1294	GC 315/80R22.5 AZ188 CHI	30.00	745.00	22,350.00
13	12	1287	GC 315/80R22.5 AZ126 CHI	70.00	735.00	51,450.00
14	13	1305	BS 700R16 R230 JAP	12.00	770.00	9,240.00
15	14	1306	BS 750R16 R230 JAP	20.00	775.00	15,500.00
16	15	1306	BS 750R16 R230 JAP	14.00	780.00	10,920.00
17	TOTAL					953,086.00
BRANDS

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2
F17	F17	=SUM(F2:F16)

and when fill I2 then should subtract from Cells in column E for each brand and clear I2
like this

Microsoft Excel‫‬.xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL		ADD	SUBTRACT
2	1	1221	BS 205/70R15C R623 THI	72.00	405.00	29,160.00			10
3	2	1227	BS 215/70R15C R623 THI	32.00	425.00	13,600.00
4	3	1227	BS 215/70R15C R623 THI	14.00	544.00	7,616.00
5	4	1241	BS 1200R20 G580 JAP	230.00	2,035.00	468,050.00
6	5	1284	GC 1200R20 AZ0026 CHI	90.00	895.00	80,550.00
7	6	1284	GC 1200R20 AZ0026 CHI	60.00	1,125.00	67,500.00
8	7	1285	GC 1200R20 AZ0183 CHI	50.00	925.00	46,250.00
9	8	1285	GC 1200R20 AZ0183 CHI	20.00	1,225.00	24,500.00
10	9	1285	GC 1200R20 AZ0183 CHI	70.00	1,205.00	84,350.00
11	10	1385	GC 315/80R22.5 AT161 CHI	30.00	735.00	22,050.00
12	11	1294	GC 315/80R22.5 AZ188 CHI	30.00	745.00	22,350.00
13	12	1287	GC 315/80R22.5 AZ126 CHI	70.00	735.00	51,450.00
14	13	1305	BS 700R16 R230 JAP	12.00	770.00	9,240.00
15	14	1306	BS 750R16 R230 JAP	20.00	775.00	15,500.00
16	15	1306	BS 750R16 R230 JAP	14.00	780.00	10,920.00
17	TOTAL					953,086.00
BRANDS

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2
F17	F17	=SUM(F2:F16)

result

Microsoft Excel‫‬.xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL		ADD	SUBTRACT
2	1	1221	BS 205/70R15C R623 THI	62.00	405.00	25,110.00
3	2	1227	BS 215/70R15C R623 THI	22.00	425.00	9,350.00
4	3	1227	BS 215/70R15C R623 THI	4.00	544.00	2,176.00
5	4	1241	BS 1200R20 G580 JAP	220.00	2,035.00	447,700.00
6	5	1284	GC 1200R20 AZ0026 CHI	80.00	895.00	71,600.00
7	6	1284	GC 1200R20 AZ0026 CHI	50.00	1,125.00	56,250.00
8	7	1285	GC 1200R20 AZ0183 CHI	40.00	925.00	37,000.00
9	8	1285	GC 1200R20 AZ0183 CHI	10.00	1,225.00	12,250.00
10	9	1285	GC 1200R20 AZ0183 CHI	60.00	1,205.00	72,300.00
11	10	1385	GC 315/80R22.5 AT161 CHI	20.00	735.00	14,700.00
12	11	1294	GC 315/80R22.5 AZ188 CHI	20.00	745.00	14,900.00
13	12	1287	GC 315/80R22.5 AZ126 CHI	60.00	735.00	44,100.00
14	13	1305	BS 700R16 R230 JAP	2.00	770.00	1,540.00
15	14	1306	BS 750R16 R230 JAP	10.00	775.00	7,750.00
16	15	1306	BS 750R16 R230 JAP	4.00	780.00	3,120.00
17	TOTAL					819,846.00
BRANDS

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2
F17	F17	=SUM(F2:F16)

when the cells H2,I2 are empty, then nothing happens.
one of cell will be filled(not together filled)

 

[kvsrinivasamurthy]

May be this. In G2 copy down.

=E2+$H$2-$I$2

 

[Trebor76]

If you have to use a macro here's one way (needs to be run while on the sheet with the data):

Option Explicit
Sub Macro1()

    Dim i As Long, j As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        On Error Resume Next
            .ShowAllData
        On Error GoTo 0
        .Columns.EntireColumn.Hidden = False
        .Rows.EntireRow.Hidden = False
        j = .Cells(Rows.Count, "D").End(xlUp).Row
        For i = 2 To j
            .Range("D" & i).Value = Range("D" & i).Value + Range("H2").Value - Range("I2").Value
        Next i
    End With
    
    Range("H2:I2").ClearContents
    
    Application.ScreenUpdating = True

End Sub

 

[bebo021999]

Use this in Worksheet module (not in general module (sub))
Triggered once typing value into those 2 cells

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Intersect(Target, Range("H2:I2")) Is Nothing Or Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Or (Range("H2") <> "" And Range("I2") <> "") Then Exit Sub
If Range("H2") = "" And Range("I2") = "" Then Exit Sub
If MsgBox("Qty will be added/subtracted by " & (Range("H2") + Range("I2")) & ". Do you want to continue?", vbYesNo) = vbNo Then Exit Sub
For Each cell In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    cell = cell + Range("H2") - Range("I2")
Next
End Sub

 

[Trebor76]

Hi bebo021999,

Nice code

I would also use EnableEvents to stop the macro firing off again when the cell values in Col. D are changed:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Intersect(Target, Range("H2:I2")) Is Nothing Or Target.Count > 1 Then Exit Sub
    If Not IsNumeric(Target) Or (Range("H2") <> "" And Range("I2") <> "") Then Exit Sub
    If Range("H2") = "" And Range("I2") = "" Then Exit Sub
    If MsgBox("Qty will be added/subtracted by " & (Range("H2") + Range("I2")) & ". Do you want to continue?", vbYesNo) = vbNo Then Exit Sub
    Application.EnableEvents = False
    For Each cell In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        cell = cell + Range("H2") - Range("I2")
    Next cell
    Application.EnableEvents = True
End Sub

 

[abdo meghari]

@kvsrinivasamurthy
thanks, but I would vba as I said.

​

 

[abdo meghari]

@Trebor76
that's great !
many thanks

 

[abdo meghari]

@bebo021999
excellent !
many thanks for your help.