summing and subtracting among sheets by code insted of formula

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hi

i truly appreciate if any body help about sum and subtract among five sheets from a2:e and show the result in sheet6 i would like this formula "=sheets1!E2:d1000+sheets2!E2:d1000-sheets13!E2:d1000-sheets4!E2:d1000+sheets5!E2:d1000 i prefer by code becuase fast and dynamic
JJ.JPG
 
sorry it gives me error in this line code run time error13
VBA Code:
sht6(i, 5) = sht1(i, 1) + sht2(i, 1) - sht3(i, 1) - sht4(i, 1) + sht5(i, 1)
i ment from a2: d the range is dynamic not specific range as your code from a2: e999 every time add a new data not finish to e999
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Thanks for finally explaining what you want , try this:
VBA Code:
Sub test()
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

sht1 = Range(.Cells(2, 1), .Cells(lastrow, 5))
End With
With Worksheets("Sheet2")
sht2 = Range(.Cells(2, 5), .Cells(lastrow, 5))
End With
With Worksheets("Sheet3")
sht3 = Range(.Cells(2, 5), .Cells(lastrow, 5))
End With
With Worksheets("Sheet4")
sht4 = Range(.Cells(2, 5), .Cells(lastrow, 5))
End With
With Worksheets("Sheet5")
sht5 = Range(.Cells(2, 5), .Cells(lastrow, 5))
End With
With Worksheets("Sheet6")
sht6 = Range(.Cells(2, 1), .Cells(lastrow, 5))
For i = 1 To lastrow - 1
sht6(i, 5) = sht1(i, 5) + sht2(i, 1) - sht3(i, 1) - sht4(i, 1) + sht5(i, 1)
For j = 1 To 4
sht6(i, j) = sht1(i, j)
Next j
Next i

  Range(.Cells(2, 1), .Cells(lastrow, 5)) = sht6


End With
End Sub
 
Upvote 0
your code works but not extremely sometimes what exist in sheet1 not existed in sheet2 or other sheet for instance i have code"vv1" in colum e=200 in sheet1
sheet2=" " sheet3 =20 sheet4=40 sheet5=5 so sheet6 for this code"vv1" supposes 200+0-20-40+5=145 but it gets me 200 is wrong
 
Upvote 0
I am sorry but I give up , I have tried, but you have failed to explain the problem fully and now you seem to adding other conditions which I don't understand
 
Upvote 0
this is normal when the data not existed in other sheet it supposes the value is 0 then it should sum and subtract correctly without any mistakes i don't add any other condition anyway it's up to you if you answering or not i would thank you for your time and your effort
have a great day
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top