excel problem..macro best?

  herc182 11:12 05 Aug 2006

dear all,

Please see here:
click here

I am trying to sum the values on tab "1" to input into a summary table in tab "savings". I have colour coordinated it.
So in tab "1" i want to sum(a3 -a47) and put that value in tab "savings" in cell E4.
then sum(b3-b47) and put that in tab "savings" in cell E5. etc etc for every week, and for the rest of the rows.

can anyone help? otherwise i have to put the formulas into tab "savings" individually for all of them....which will take forever!


  herc182 11:45 05 Aug 2006

bit early for macros i suspect!


  VoG II 11:53 05 Aug 2006

'1' only has 4 weeks. 'savings' has 9 weeks.

  herc182 11:57 05 Aug 2006

yeah i havent added the other ones yet....

just want to get the macro or VBA sorted for say two weeks then i can do it for the rest.


  herc182 11:59 05 Aug 2006

no hang on i see the confusion.....

week number on tab 1 goes along row 1 as does the week number on tab "savings". week number in tab "savings" DOES NOT go down row D.


  VoG II 12:33 05 Aug 2006

Sub test()
Dim iRow, jCol, Tot As Range, sm
jCol = 0
For iRow = 4 To 6
jCol = jCol + 1
Set Tot = Sheets("1").Range(Sheets("1").Cells(3, jCol), Sheets("1").Cells(47, jCol))
sm = Application.Sum(Tot)
Sheets("savings").Range("E" & iRow).Value = sm
Next iRow
End Sub

  herc182 13:25 05 Aug 2006

can you just run that trough with me....

will i have to do that for each cell i want a sum in?


  herc182 14:44 05 Aug 2006



  VoG II 15:15 05 Aug 2006

That is 'doing' E4, E5 and E6. You would need to adapt it for E8:E10 and so on. If you didn't have the unnecessary rows 3, 7, 11 etc. it would be simple to do - just change

For iRow = 4 To 6


For iRow = 4 To X

where X is the last row to be copied to.

  herc182 15:31 05 Aug 2006

thanks VOG....i will have a look at that when i get home. time to leave work!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?