Find a Variable's(cells) address

[JapanDave]

I have been using this code to find a variables address, but is there something more efficient?

In the below code I am trying to find the variable "celval" which can reside anywhere in a range
I first find "celval" with find to get the strings value then use the second line to get the column or row number.

Please Login or Register  to view this content.

Cheers

[Simon Lloyd]

You can use just one line

Please Login or Register  to view this content.

or change column for Row

Or you can find it's address:

Please Login or Register  to view this content.

or find the address like this

Please Login or Register  to view this content.

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

[Winon]

Hi JapanDave,

I have tried the above Codes from Simon Lloyd, and it either does nothing, or one gave me some Error Message.

I have put this together, tested it and works like a charm!

Please Login or Register  to view this content.

[JapanDave]

Sorry, my explanation was bad.

My code works no problem. I was trying to find a more efficient way of writting.

Simon, I have tried this before and I can't get the column or row number.

Please Login or Register  to view this content.

This line gives you the found cell's column number no problem.

Please Login or Register  to view this content.

That is not the problem, the lenght of the code is.



@Winon,
all that your code is doing is activating the found cell. I want the row or col number. As I said before , my code works , I just want a better way to do it, if there is one?

Please Login or Register  to view this content.

[Winon]

@Simon Lloyd,

Sorry Simon, I can't get your code to work on Excel2007. The code I offered JapanDave, also only works if the word "Apple" appears somewhere within the given Range?

[Winon]

Hi JapanDave,

I can't get your code to work either. Paste this line at the Bottom of your Code.

Please Login or Register  to view this content.

Would you then please be so kind as to Post me your Complete Working Code for Excel 2007?

Thank You

[Simon Lloyd]

I ran this with no issues at all????

Please Login or Register  to view this content.

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

[snb]

Either:
c01 resulting in a range, because of using 'Set'

Please Login or Register  to view this content.

or
c01 resulting in a string:

Please Login or Register  to view this content.

[Jack in the UK]

Here You go have a play with this, just copy all the code over to a new mondule and run .. see what happens

The code open so easy to edit, any problems post back. Your code does not give the address as stated only the row unless Jacks missing soemthing

jiuk

Please Login or Register  to view this content.