Column Searching
 

I am looking for a way to find any time "weekly subtotal" is found in
column (A). If it is found for the given row I want to sum Columns
D,E,and F from the first date to the row with the "weekly subtotal in
it.

01/02/2006
01/02/2006
01/03/2006
01/03/2006
01/04/2006
01/05/2006
01/06/2006
01/06/2006
Weekly Subtotal (SUM D1:D8) (SUM E1:E8) (SUM
F1:F8)
01/09/2006
01/10/2006
01/11/2006
01/12/2006
01/13/2006
Weekly Subtotal (SUM D10:D14) (SUM E10:E14) (SUM
F10:F14)

Can anyone Help? PLEASE


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=519594

Column Searching
 

try this, not very neat

Dim i
Dim thisrow

Sub Macro1()

lastrow = Range("a65536").End(xlUp).Row
Range("A1").Select
Columns("A:A").Select
Selection.Find(What:="weekly", After:=ActiveCell).Activate
thisrow = ActiveCell.Row
i = i + 1
insertformula
Do While thisrow < lastrow
i = thisrow + 1
Selection.Find(What:="weekly", After:=ActiveCell).Activate
thisrow = ActiveCell.Row
insertformula
Loop
End Sub

Sub insertformula()
Range("B" & thisrow).Formula = "=sum(B" & i & ":B" & thisrow - 1 & ")"
Range("C" & thisrow).Formula = "=sum(C" & i & ":C" & thisrow - 1 & ")"
Range("D" & thisrow).Formula = "=sum(D" & i & ":D" & thisrow - 1 & ")"
End Sub


"parteegolfer" wrote:


I am looking for a way to find any time "weekly subtotal" is found in
column (A). If it is found for the given row I want to sum Columns
D,E,and F from the first date to the row with the "weekly subtotal in
it.

01/02/2006
01/02/2006
01/03/2006
01/03/2006
01/04/2006
01/05/2006
01/06/2006
01/06/2006
Weekly Subtotal (SUM D1:D8) (SUM E1:E8) (SUM
F1:F8)
01/09/2006
01/10/2006
01/11/2006
01/12/2006
01/13/2006
Weekly Subtotal (SUM D10:D14) (SUM E10:E14) (SUM
F10:F14)

Can anyone Help? PLEASE


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=519594

Column Searching
 

Hi,
Try this:

Dim rng As Range
Dim lastrow As Long, r As Long, i As Integer
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
r = 1
srow = r
Do
Do
r = r + 1
Loop Until .Cells(r, "a") = "Weekly Subtotal" Or r = lastrow
For i = 4 To 6
Set rng = .Range(.Cells(srow, i), .Cells(r - 1, i))
Cells(r, i) = Application.Sum(rng)
Next i
srow = r + 1
Loop Until srow lastrow
End With

"parteegolfer" wrote:


I am looking for a way to find any time "weekly subtotal" is found in
column (A). If it is found for the given row I want to sum Columns
D,E,and F from the first date to the row with the "weekly subtotal in
it.

01/02/2006
01/02/2006
01/03/2006
01/03/2006
01/04/2006
01/05/2006
01/06/2006
01/06/2006
Weekly Subtotal (SUM D1:D8) (SUM E1:E8) (SUM
F1:F8)
01/09/2006
01/10/2006
01/11/2006
01/12/2006
01/13/2006
Weekly Subtotal (SUM D10:D14) (SUM E10:E14) (SUM
F10:F14)

Can anyone Help? PLEASE


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=519594

Column Searching
 

Thanks Toppers, This worked out well for me. All i had to do was change
the worksheet name. Once again, thanks


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=519594