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 D18) (SUM E1:E8) (SUM
F1:F8)
01/09/2006
01/10/2006
01/11/2006
01/12/2006
01/13/2006
Weekly Subtotal (SUM D1014) (SUM E10:E14) (SUM
F10:F14)

Can anyone Help? PLEASE

 

[Guest]

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 & "" & thisrow - 1 & ")"
End Sub

 

[Guest]

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]

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