Vba Minus

[erdow]

Hi to all experts,

i have a file with multiple sheets with same format and it subtracts automatically C-D using vba and puts the answer E when i paste new data on it
What i want to do is apply this vba code to F-G=H and I-J=K and P-Q=R on each sheet but couldn't

I use this for consolidating datas which i get every month from branches and i just want to copy and paste whole sheet at once (not column by column) whitout controlling if there are any wrong formulas on it..
I couldnt attach my exact file but have a wetransfer link for it.

https://wetransfer.com/downloads/6e042427420d4274366afc6a9295fed920211123070850/706911dd6926a8cf70831d8afcda69c920211123070909/98cde3

Thanks a looottt in advance.
Best regards..

 

[erdow]

There is two code on each sheet, one is,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRw As Long
LastRw = ListObjects(1).DataBodyRange.Rows.Count + 10 'Last table row + 10
If Not Intersect(Target, Range("C11:d" & LastRw)) Is Nothing Then

Call CminusO(LastRw)
End If
End Sub


second code is like that,

Sub CminusO(LastRw As Long)
Dim LastE As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

LastE = Cells(Rows.Count, "E").End(xlUp).Row
Range("e11:e" & LastE) = vbNullStr 'clear any stray E data below table if exists?

Range("e11:e" & LastRw) = Evaluate("C11:C" & LastRw & "-d11:d" & LastRw)

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

 

[AlanY]

this is a simpler solution using Table instead of VBA.
let say you've 2 columns of data like this;

10	1
20	2
30	3
40	4
50	5
60	6
70	7
80	8
90	9
100	10

Select all the data and enter with control-T to create a table of the data;

Column1	Column2
10	1
20	2
30	3
40	4
50	5
60	6
70	7
80	8
90	9
100	10

input the formula in Cell C2, it will automatically copy down to the end of the table;

Book1
	A	B	C
1	Column1	Column2	Column3
2	10	1	9
3	20	2	18
4	30	3	27
5	40	4	36
6	50	5	45
7	60	6	54
8	70	7	63
9	80	8	72
10	90	9	81
11	100	10	90
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=[@Column1]-[@Column2]

whenever you add data to the end of the table, formula will copy down automatically;

Book1
	A	B	C
1	Column1	Column2	Column3
2	10	1	9
3	20	2	18
4	30	3	27
5	40	4	36
6	50	5	45
7	60	6	54
8	70	7	63
9	80	8	72
10	90	9	81
11	100	10	90
12	40	4	36
13	50	5	45
14	60	6	54
15	70	7	63
Sheet1

Cell Formulas
Range	Formula
C2:C15	C2	=[@Column1]-[@Column2]

 

[erdow]

Thanks AlanY but it does not work for me, its just using formula but every time i copy and paste b11:r35 from the sheets which sent by branches to me and if there are any wrong or corrupted formulas on tables which i copy datas from, my table shows wrong report. All those 3 columns must calculate and put the correct result even i paste a corrupted formula on it. You can see my file on link, it just does it for first copumn but i want to apply it to other columns using same code. Thanks for your inerest.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Subtract using vba minus0
and Subtract using vba minus0 - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.