PDA

View Full Version : [SOLVED] Summing based on data range



Tinku
06-27-2005, 07:09 AM
I know this wil be an easy thing for you Excel gurus. I am having a hard time trying to build this macro.
What I am trying to achieve is to to create a macro for summing from data which I get every week. The number of rows keep changing. I tried recording the macro but its not flexible and does not take into consideration any changes in the rows in the new data.
I have u/l a sample template sheet.
Please help me with this macro.

Regards
Tinku

mdmackillop
06-27-2005, 10:20 AM
Hi Tinku,
As a start, this should put the formulae into cell I96:M96, based on the relative position of Section 6 and Section 7 in column B. No more time just now.



Sub Macro2()
With Range("B:B")
MyStart = .Find(What:="Section 6:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
MyEnd = .Find(What:="Section 7:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd - 2 _
& "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd - 2 & ")"
Cells(MyStart + 1, "I").Range("A1:E1").FillRight
End Sub

Tinku
06-27-2005, 01:26 PM
Thank you mdmackillop

The code is working but it is adding some extra rows in the formulae. I just copied some rows and pasted them below existing rows and ran the macro. I noticed that the sumif() formulae take some extra rows into consideration in Section 6 and section 8.
Please note the 2 sections are sections 6 and section 8. I changed the section # in the code ..could that be the reason that the macro is not working properly ?

Thank you

Regards
Tinku

mdmackillop
06-27-2005, 01:44 PM
Hi Tinkyu,
I was in a bit of a rush earlier.
The code works by finding the text "Section 6:" and "Section 7:" Section 6 location gives a row number for MyStart and Section 7 for MyEnd. The formula code uses offsets from these values for the row numbers in the formula, so MyEnd - 2 would become MyEnd - 4 to give you row 167. If the space between Sections 6 and 7 is not consistent, then you could use one of the following alternatives, depending upon whether your Column B data is guaranteed to have no gaps or not. With either of these MyEnd - 2 would simply be changed to MyEnd



MyEnd = .Find(What:="Section 6:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(9, 0).End(xlDown).Row
MyEnd = .Find(What:="Section 7:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).End(xlUp).Row

mdmackillop
06-27-2005, 02:06 PM
"Finished product" (I hope!)


Option Explicit

Sub Macro2()
Dim MyStart As Long, MyEnd As Long, i As Long
Dim MySections, MS
MySections = Array(6, 8)
For Each MS In MySections
With Range("B:B")
MyStart = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
MyEnd = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(9, 0).End(xlDown).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd _
& "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd & ")"
Cells(MyStart + 1, "I").Range("A1:E1").FillRight
Cells(MyStart + 4, "I").Formula = "=SUM(I" & MyStart + 5 & ":I" & MyEnd & ")"
Cells(MyStart + 4, "I").Range("A1:E1").FillRight
Next
End Sub

Tinku
06-28-2005, 06:53 AM
Awesome mdmackillop :bow:

Its working wonderfully.. can I run(loop) this macro on 6 worksheets with different names (eg Japan, Americas, Pacific, Africa, SE Asia, Europe)

Thanks so much.. its going to save me significant amount of time. Now I can take more coffee breaks :cloud9:

Regards
Tinku

mdmackillop
06-28-2005, 11:28 AM
Hi Tinku,
I've added another loop. Replace Avent1, 2 & 3 with sheet names as required.
Now you should have time for a cream bun as well!


Option Explicit

Sub Macro2()
Dim MyStart As Long, MyEnd As Long, i As Long
Dim MySections, MS, MySheets, Sh
Dim ThsSht As String
ThsSht = ActiveSheet.Name
Application.ScreenUpdating = FalseMySheets = Array("Avent", "Avent1", "Avent2")
For Each Sh In MySheets
Sheets(Sh).Activate
MySections = Array(6, 8)
For Each MS In MySections
With Range("B:B")
MyStart = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
MyEnd = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(9, 0).End(xlDown).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd _
& "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd & ")"
Cells(MyStart + 1, "I").Range("A1:E1").FillRight
Cells(MyStart + 4, "I").Formula = "=SUM(I" & MyStart + 5 & ":I" & MyEnd & ")"
Cells(MyStart + 4, "I").Range("A1:E1").FillRight
Next
Next
Sheets(ThsSht).Activate
Application.ScreenUpdating = True
End Sub

Tinku
06-28-2005, 11:55 AM
Now you should have time for a cream bun as well!
:yes

Thanks so much mdmackillop:bow:

I think I can sneak out for a few hours every week now.. while the data is being
"PROCESSED"..:rofl:

Regards
Tinku