Finding the addres of a cell

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

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

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.

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

Cell

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

Michael Robson

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

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

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