insert column and calculate between two columns

[Hasson]

hi
I have values in two columns sales and returns I would insert a new column name the header is QTY then should subtract COL E from COL F
SHEET input

1.xlsx
	A	B	C	D	E	F
1	date	commidaty	manf	made	sales	returns
2	12/5/2021	ff-1200	it-1000	it	1000	5
3	12/6/2021	ff-1201	it-1001	ja	200	-
4	12/7/2021	ff-1202	it-1002	br	258	45
5	12/8/2021	ff-1203	it-1003	ur	20	5
6	12/9/2021	ff-1204	it-1004	tr	300	12
7	12/10/2021	ff-1205	it-1005	it	10	-
8	12/11/2021	ff-1206	it-1006	ja	50	-
9	12/12/2021	ff-1207	it-1007	br	12	-
10	12/13/2021	ff-1208	it-1008	ur	45
11	12/14/2021	ff-1209	it-1009	tr	120	10
12	12/15/2021	ff-1210	it-1010	it	10	12
input

expected result

1.xlsx
	A	B	C	D	E	F	G
1	date	commidaty	manf	made	sales	returns	QTY
2	12/5/2021	ff-1200	it-1000	it	1000	5	955
3	12/6/2021	ff-1201	it-1001	ja	200	-	200
4	12/7/2021	ff-1202	it-1002	br	258	45	213
5	12/8/2021	ff-1203	it-1003	ur	20	5	15
6	12/9/2021	ff-1204	it-1004	tr	300	12	288
7	12/10/2021	ff-1205	it-1005	it	10	-	10
8	12/11/2021	ff-1206	it-1006	ja	50	-	50
9	12/12/2021	ff-1207	it-1007	br	12	-	12
10	12/13/2021	ff-1208	it-1008	ur	45		45
11	12/14/2021	ff-1209	it-1009	tr	120	10	110
12	12/15/2021	ff-1210	it-1010	it	10	12	-2
input

 

[Peter_SSs]

Try

Sub NewCol()
  With Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .Cells(0).Value = "QTY"
    .Formula = "=E2-N(F2)"
  End With
End Sub

 

[Hasson]

wow ! it's quick , but I would the same formatting and borders like columns which precede them and no show the formula in the column

 

[Peter_SSs]

the same formatting and borders like columns which precede them and no show the formula

Then try

Sub NewCol()
  With Range("G1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=E1-N(F1)"
    .Value = .Value
    .Cells(1).Value = "QTY"
    .Offset(, -1).Copy
    .PasteSpecial xlPasteFormats
  End With
End Sub

 

[Hasson]

thanks, but can you delete the selection for column which precede it after run the macro ,please

and what means letter N in your code ?

 

[Peter_SSs]

Try this version.
The N() is to stop an error value on the rows that have a "-" in the returns column by turning that "-" into a 0.
N function

Sub NewCol()
  Application.ScreenUpdating = False
  With Range("G1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=E1-N(F1)"
    .Value = .Value
    .Cells(1).Value = "QTY"
    .Offset(, -1).Copy
    .PasteSpecial xlPasteFormats
  End With
  Application.CutCopyMode = False
  Range("G1").Select
  Application.ScreenUpdating = True
End Sub

 

[Hasson]

astonishing buddy! you're super great
many thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.