One way:

Sub a()


With Worksheets(1).Range("a1:D500") ' <=== change to suit
Set c = .Find("Grand Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Address
MsgBox c.Row
MsgBox c.Column
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


HTH
"ppbedz" wrote:


I would like to retrieve the address of a cell that contains a specific
text value ie. "Grand Total". I then want to substitue the row number
portion of the address into a macro so that I can copy everything in my
spreadsheet PRIOR to the "Grand Total" row. I am just getting into
macros and more complicated spreadsheet processing so I could really
use some help.

Thank you


--
ppbedz
------------------------------------------------------------------------
ppbedz's Profile: http://www.excelforum.com/member.php...o&userid=33559
View this thread: http://www.excelforum.com/showthread...hreadid=533456