Return cell address value
 

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

Return cell address value
 

Hi Todd
msgbox findRange.address

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
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

Return cell address value
 

Hi Todd

FindRange.Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine" skrev i en
meddelelse ...
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

Return cell address value
 

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


-----Original Message-----
Hi Todd

FindRange.Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine"
skrev i en
meddelelse ...
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


.

Return cell address value
 

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


-----Original Message-----
Hi Todd

FindRange.Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine"
skrev i en
meddelelse ...
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


.

Return cell address value
 

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)

"Todd Huttenstine" wrote in message
...
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


-----Original Message-----
Hi Todd

FindRange.Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine"
skrev i en
meddelelse ...
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


.

Return cell address value
 

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)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine" skrev i en
meddelelse ...
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

Return cell address value
 

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)

"Todd Huttenstine" wrote in message
...
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


-----Original Message-----
Hi Todd

FindRange.Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Todd Huttenstine"
skrev i en
meddelelse ...
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


.