Return cell address value

[Todd Huttenstine]

Hey guys

Below is a code that looks in Range A3:A3000 and sets
FindRange = Cell1. How would I return the actual cell
address value of FindRange?


Dim Cell1 As Object
Dim FindRange

FindRange = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("WOW Reps").Range("A3:A3000").Find(Cell1)

Thanks
Todd Huttenstine

 

[Frank Kabel]

Hi Todd
msgbox findRange.address

 

[Leo Heuser]

Hi Todd

FindRange.Address

 

[Todd Huttenstine]

Thats what I thought but its not doing anything. Below is
my entire code.


Dim Rng As Range
Dim Rng2 As Range
Dim Cell As Object
Dim Cell2 As Object
Dim FindRange
Dim FindRange2
On Error Resume Next

Set Rng = Sheets("WOW Reps").Range("A3:A3000")
Set Rng2 = Sheets("Followup Summary").Range("A7:A3000")

For Each Cell In Rng
FindRange = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("WOW Reps").Range("A3:A3000").Find(Cell)
FindRange2 = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("Followup Summary").Range("A7:A3000").Find
(FindRange)

Next

 

[Todd Huttenstine]

Well I got it. I had to add .address to the very end.
Below is what I chaged the code to:

FindRangeAddress = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("WOW Reps").Range("A3:A3000").Find
(Cell).Address

 

[Bob Phillips]

Todd,

FindRange is a range object, so you need to Set it. You can also test for
found, all like so

For Each Cell In Rng
With Workbooks("Completed Followup Detail CSS.XLS")
Set FindRange = .Sheets("WOW Reps").Range("A3:A3000"). _
Find(Cell)
If Not FindRange Is Nothing Then
Set FindRange2 = .Sheets("Followup Summary").Range"A7:A3000"). _
Find(FindRange)
If Not FindRange2 Is Nothing Then
MsgBox FindRange2.Address(False,False)
End If
End If
Set FindRange = Nothing
Set FindRange2 = Nothing
End With
Next Cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Leo Heuser]

FindRange and FindRange2 are
ranges, so you have to use Set:

Dim FindRange As Range
Dim FindRange2 As Range

Set FindRange = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("WOW Reps").Range("A3:A3000").Find(Cell)
Set FindRange2 = Workbooks("Completed Followup Detail
CSS.XLS").Sheets("Followup Summary").Range("A7:A3000").Find
(FindRange.Address.Value)

 

[Bob Phillips]

See my reply it is (IMO better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)