Return of row and column

[Gerry]

I need some code that will return to a variable, the row
and column of my selection. I have a worksheet with over
1500 rows and 1200 columns, I also have a userform with a
single textbox and a command button. The user can enter
anything in the box (date, name, phone number, etc). When
the button is clicked I want the worksheet to be searched
and when a match is found I want the row and column of the
match to be returned to a variable in the VB code. I have
the userform designed and the search code is all working
ok. I just need the code for the second part of my
project. Thanks

 

[Tom Ogilvy]

Dim rw as Long, col as Long
rw = ActiveCell.Row
col = ActiveCell.Column

 

[Gerry]

Thanks Tom, now I need to take this rw and col and tell
the application where to begin its execution.

I have Set AddressRng = Worksheets("Sheet4").Range(rw, col)
but I get an run time error 1004. I'm assuming it has to
do with the Range(rw,col) statement. Do you know how this
Range should be specified?

Thanks

 

[Chip Pearson]

Gerry,

You use row and column numbers with the Cells property, not the
Range property. So,

Set AddressRng = Worksheets("Sheet4").Cells(rw, col)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Gerry]

Hi, this only gives me the location of my cursor not the
location of my find. Sorry I should have been more clear
as to what I need.

Let's say the user inputs "Tom" to a userform and hits
enter, I have the code to search through the worksheet to
find Tom, now I need the code to return the row and column
of that match to a variable or two variables. I will then
use this row and column in the next part of my code.

Does this make sense?

 

[Tom Ogilvy]

The assumption was that your find code activates the found cell.

Worksheets("Sheet1").Activate
Cells.Find(What:="Tom").Activate

or

Worksheets("Sheet1").Activate
Cells.Find(What:=Userform1.Textbox1.Text).Activate

is what people usually come up with. So rw = ActiveCell.row works fine.

Without seeing your find/search code, it would be difficult to tell you
specifically how to get the row and column. but it is also hard to see how
your find code could work and you not know everything you need to know about
where it was found.

Post your code and perhaps I can give you a better answer.