Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

Finding the addres of a cell

Posted by Michael Robson on February 16, 2000 10:31 AM
How can I find the address of a cell from a users input. Heres my example,

A B C D E F G H
1 Jim 23 24 25 26 27 28 29
2 Mark 33 34 35 36 37 38 39
3 Bill 43 44 45 46 47 48 49
4 Fred 53 54 55 56 57 58 59
5 Steve 63 64 65 66 67 68 69

I want the user to select a name, lets say the person selects "Steve". I would then like to have a variable set for the ADDRESS of "Steve", in this case A5. I have used Vlookup to find "Steve" and every cell around it, but I cant find something to tell me the ADDRESS of the cell. I have tried MATCH, but again that tells me the contents, but not the ADDRESS, or something like that.

Thank you for any help on this =)

michael Robson


Check out our Excel VBA Resources

Re: Finding the addres of a cell

Posted by Celia on February 16, 2000 4:36 PM

Michael
I donít know how to do this with worksheet functions but it is easy with VBA code.
For example, the following macro will display a message box that shows the contents and address of the active cell.
Hope this helps.
Celia

Sub CellDetails()
MsgBox "The selected cell reads '" & ActiveCell.Value & "' and the cell address is '" & ActiveCell.Address(False, False) & "'"
End Sub


Re: Finding the addres of a cell

Posted by Michael Robson on February 17, 2000 10:56 AM

Thank you so much! That is a very big step in the right direction. I am sorry I forgot to mention that it is VBA I need and not a worksheet function.

What you gave me absolutely gives me the cell address, but I still have a problem. When I say the user selects a cell what I mean is the user is prompted with an input box asking what name he wants info for. The person then types that name into the box, the macro finds the name from the list, assuming its there of course, then returns the address. So my question should have been, how can I write a macro that selects the cell and then returns the address of the cell? I mentioned before I tried using Vlookup to find the cell, but that wouldn't let me select it also, or at least I have had no success using that method yet. How do I lookup a cell, then select it too?

Thank you for your wonderful help so far, you have sent me in the right direction! =)

Michael Robson


Re: Finding the addres of a cell

Posted by bill roberts on February 17, 2000 12:21 PM

Mike,

1) The list must be sorted for VLOOKUP to work,

2) I prefer LOOKUP to VLOOKUP because it's more flexible. You are not tied to the first column.

3) Once the Name list is complete, use VALIDATION from the DATA menu to delimit the list.

4) Really consider #3; if the user value is not on the list, chances are that the info returned will be erronious.


Re: Finding the addres of a cell

Posted by Ivan Moala on February 17, 2000 3:17 PM

Micheal
Have you tried using Application.inputbox method
Have a look @ this example;Sub tester()
Dim NmRg
Dim Nm
Dim NmCell
Dim Found As Boolean


Again:
On Error Resume Next
Nm = Application.InputBox("Enter Name to search for", "Search for Name", Type:=2)
If IsEmpty(Nm) Then End

Set NmRg = Range(Range("A1"), Range("A1").End(xlDown))
For Each NmCell In NmRg.Rows
If NmCell = Nm Then
MsgBox Nm & " found @ address:= " & NmCell.Address
Found = True
End If
Next
If Not Found Then MsgBox "No matches for " & Nm & " found !?"
End Sub

Ivan



Re: Finding the addres of a cell

Posted by Michael Robson on February 18, 2000 12:20 PM
Cell

Thank you very much! I will try this out and let you know how it goes. =)

Michael Robson


Re: Finding the addres of a cell NEW!

Posted by Michael Robson on February 24, 2000 8:04 AM

Cell

Sub Mywhileloop()

Dim Startingname
Dim Endingname
Dim Startname
Dim Endname

Sheets("Sheet1").Activate
Range("A1").Select
Startdname = Application.InputBox("Please enter the first name to search for.", Type:=1)
If IsEmpty(Startname) Then End

Do While ActiveCell.Value <> Startname
ActiveCell.Offset(1, 0).Select
Loop
MsgBox Startname & " found @ address " & ActiveCell.Address
Startingname = ActiveCell.Address
Sheets("Sheet1").Activate

Range("A1").Select
Endname = Application.InputBox("Please enter the second name to search for.", Type:=1)
If IsEmpty(Endname) Then End

Do While ActiveCell.Value <> Endname
ActiveCell.Offset(1, 0).Select
Loop
MsgBox Endname & " found @ address " & ActiveCell.Address
Endingname = ActiveCell.Address
MsgBox Startingname & " & " & Endingname & " Found!"
End Sub

Thank you Ivan! I tinkered around with what you gave me for a few days. One thing I found is that by using a For Next loop the macro would continue checking for names even after it had found the correct match. The problem with this is that if I want to return the address as a variable I would always end up getting the address of the last cell in the range. What I tried is using a Do While loop. That solved my problem big time! I want to thank you for sending me in the right direction. I also borrowed heavily from your original macro so thanks again! =)

Now comes my next problem. I have set it up to find two separate names. That part works fine. I have put the address's of the names into two variables. These variables keep their information till then end of the macro as confirmed by my last MsgBox. The problem I am having is how do I select these two cells and all the cells inbetween? I cant use Range because they aren't named Ranges. Can I declare them as Ranges somehow? I haven't been able to find any information on this. Thanks again for any help you or someone might have. =)

Michael Robson


Re: Finding the addres of a cell NEW!

Posted by Celia on February 24, 2000 8:09 PM

Cell Dim Startingname Dim Endingname Dim Startname Dim Endname Sheets("Sheet1").Activate Range("A1").Select Startdname = Application.InputBox("Please enter the first name to search for.", Type:=1) If IsEmpty(Startname) Then End Do While ActiveCell.Value <> Startname ActiveCell.Offset(1, 0).Select Loop MsgBox Startname & " found @ address " & ActiveCell.Address Startingname = ActiveCell.Address Sheets("Sheet1").Activate Range("A1").Select Endname = Application.InputBox("Please enter the second name to search for.", Type:=1) If IsEmpty(Endname) Then End Do While ActiveCell.Value <> Endname ActiveCell.Offset(1, 0).Select Loop MsgBox Endname & " found @ address " & ActiveCell.Address Endingname = ActiveCell.Address MsgBox Startingname & " & " & Endingname & " Found!" Thank you Ivan! I tinkered around with what you gave me for a few days. One thing I found is that by using a For Next loop the macro would continue checking for names even after it had found the correct match. The problem with this is that if I want to return the address as a variable I would always end up getting the address of the last cell in the range. What I tried is using a Do While loop. That solved my problem big time! I want to thank you for sending me in the right direction. I also borrowed heavily from your original macro so thanks again! =) Now comes my next problem. I have set it up to find two separate names. That part works fine. I have put the address's of the names into two variables. These variables keep their information till then end of the macro as confirmed by my last MsgBox. The problem I am having is how do I select these two cells and all the cells inbetween? I cant use Range because they aren't named Ranges. Can I declare them as Ranges somehow? I haven't been able to find any information on this. Thanks again for any help you or someone might have. =) Robson

Michael
Add the following line at the end of your macro :-

Range(Startingname & ":" & Endingname).Select

Celia



Re: Finding the addres of a cell NEW!

Posted by Michael Robson on February 25, 2000 7:46 AM


That works great! Thank you very much! One thing I also had to do was declare the variables as Strings, like this

Dim Startingname As String
Dim Endingname As String

Thanks again for the wonderful help! =)

Michael Robson


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.