Column Searching

[parteegolfer]

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

[KC]

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
>
>

[Toppers]

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
>
>

[parteegolfer]

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