Help - retrieve cell address

[ppbedz]

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

 

[Guest]

One way:

Sub a()


With Worksheets(1).Range("a1500") ' <=== 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

 

[Guest]

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

 

[ppbedz]

Thank you.

Next question....

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


Range("A5:J65000").Select


Thanks Again! :

 

[ppbedz]

Thank you.

Next question....

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


Range("A5:J65000").Select


Thanks Again! :

 

[Dave Peterson]

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.