Hi,

I have written the following macro. What I need is that when the nex
"Result" is found the "temp" range should change to that row.

Currently temp stays at Range("G13")

Please help.

Thanks

Sub testcalc()
Dim temp As Range

Set temp = Range("G13")
total = 0

Cells(13, "D").Select
While ActiveCell.Value < ""
If Cells(ActiveCell.Row, "D").Value = "Result" Then
Cells(ActiveCell.Row, "G").Value = 0
temp = Cells(ActiveCell.Row, "G")
total = Cells(ActiveCell.Row, "G").Value
temp = Cells(ActiveCell.Row).Address
Else
If ActiveCell.Value < "Result" Then
total = total + Cells(ActiveCell.Row, "G").Value

End If
End If
Cells(ActiveCell.Row + 1, "D").Select
temp = total

Wend
End Su

--
Message posted from http://www.ExcelForum.com

here is code for find:
Cells.Find(What:="Results", After:=ActiveCell,
Lookin:=xlFormulas,_
LookAt:=xlPart, SearchOrder:=xlByRows,
searchDirection:=xlnext,_
MatchCase:=False).Activate

-----Original Message-----
Hi,

I have written the following macro. What I need is that
when the next
"Result" is found the "temp" range should change to that
row.

Currently temp stays at Range("G13")

Please help.

Thanks

Sub testcalc()
Dim temp As Range

Set temp = Range("G13")
total = 0

Cells(13, "D").Select
While ActiveCell.Value < ""
If Cells(ActiveCell.Row, "D").Value = "Result" Then
Cells(ActiveCell.Row, "G").Value = 0
temp = Cells(ActiveCell.Row, "G")
total = Cells(ActiveCell.Row, "G").Value
temp = Cells(ActiveCell.Row).Address
Else
If ActiveCell.Value < "Result" Then
total = total + Cells(ActiveCell.Row, "G").Value

End If
End If
Cells(ActiveCell.Row + 1, "D").Select
temp = total

Wend
End Sub


---
Message posted from http://www.ExcelForum.com/

.