Help - retrieve cell address

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

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

This finds the first occurance of a cell containing Grand Total and outputs
the row number of that cell:


Sub Macro1()
Dim r As Range
For Each r In Selection
If r.Value = "Grand Total" Then
Exit For
End If
Next
MsgBox (r.Row)
End Sub

--
Gary''s Student


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

Thank you.

Next question....

How do I substitute r.Value into my range statement below? I essentially want to say from A5 through r.Value


Range("A5:J65000").Select


Thanks Again!

Thank you.

Next question....

How do I substitute r.Value into my range statement below? I essentially want to say from A5 through r.Value


Range("A5:J65000").Select


Thanks Again!

If you record a macro when you do Edit|Find, you can search the range (a single
column or the whole sheet) for that phrase. Then if it's found, you can use
that cell to get the row number (and row number - 1).

Option Explicit
Sub testme02()

Dim FoundCell As Range
Dim wks As Worksheet
Dim RngToCopy As Range
Dim DestCell As Range

Set wks = Worksheets("sheet1")

With Worksheets("Sheet2")
Set DestCell = .Range("a1")
End With

With wks
With .Range("A:A") '.usedrange '????
Set FoundCell = .Cells.Find(What:="grand total", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With

If FoundCell Is Nothing Then
'do nothing
Else
If FoundCell.Row > 1 Then
Set RngToCopy = .Rows("1:" & FoundCell.Row - 1)
RngToCopy.Copy _
Destination:=DestCell
End If
End If
End With

End Sub

I only looked in column A, but you could look in any range.


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

--

Dave Peterson