I sometimes use the code  below to look for a cell. It's based arounf the VBA
"Find" which is well documented in the help.
It returns a "range", which you can then do things with including going to
it and getting it's address.

You'll see that I've included a second "parameter" for the function. If the
parameter isn't given then it will default to the first found, Otherwise it
will loop around finding the Nth occurence of the search  string.

If the search string isn't found  at all then it gives a message... "Not
found".

You could call this from a sub.... Something like...

Sub subFindAndGetAddress()

Dim rlFound as range

Set rlFound = fncLookForCell("Search String")
rlFound.Select
MsgBox rlFound.Address

End Sub

This is an example but should give  you something to bite on.

Thank you for the reply. The code  works well! I wont pretend I
understand half of the code but it works auto magically!

I would like to improve  the search  though and limit the range  to a
particular range name called "InvoiceNo" which represents cells
B4:B43 (for example).

Ive tried to change the line

Set rlFromRange = Range("a1")

to any of the following but only get a runtime error

Set rlFromRange = Range("InvoiceNo")
Set rlFromRange = Range(InvoiceNo)
Set rlFromRange = Range(B4:B43)

Any clue on how I can limit the search range to just a particular
range name rather than the whole worksheet?

Check out the help on FIND in the visual basic editor. The easiest way to do
this is to click on the word find... No need to select the whole word... and
press F1.

You could also look at http://www.cpearson.com/excel/RangeFind.htm ... Chip
Pearsons site. It's a wonderful excel  VBA reference!

The help will tell you that Find is to look for stuff within a range.

To search  within a range  use something like...

Set rlFRange = Range("invoiceno")
Set rlFound = rlFRange.Find( _
What:="TTT", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Note that I've...
1 Set the range to look in at the top
2 Am NOT using the After parameter
3 Looking for "TTT". This should be replaced by your variable

Thank you for pointing me to the right
direction. Time for me to do my homework and do some research ... 8-
)

Another thing that might be relevant is
finding some record  number or word in one list and moving
information from that record to another sheet.
For example, if you have a record with other information
connected to it on the same row
(e.g. "Record#" "Date" "Address" "Phone" "Item" each in columns),
you can find that same record number  on another sheet and move the
information in column 5 ("E" column, "Item") to the other page.
This sub allows you to find the selected record on the other
page, even if it's not on the same row as the first one (e.g. it
might find it on row 36 while the first sheet had it on row 10), and
moves the coresponding "Item" value to the 5th column on the
matching record on the other page.

Sub findmatchandmove()

Dim actVal
'<----the variable of the active record to be found
Dim newVal
'<---the variable of the value that needs to be moved
Dim fc
'<---variable for the found cell  that matches
Dim fndRrow
'<---variable for the row that matches
actVal = ActiveCell.Value
'<--- actVal is located in the active cell
newVal = ActiveCell(Offset, 5).Value
'<--- location of newVal, column 5
Set fc = Worksheets("Sheet2").Columns("A").Find(what:=actVal)
'<--finds it on the other sheet
fndRrow = fc.Row
'<--identifies the row it's on in the list
Sheets("Sheet2").Cells(fndRrow, 5).Value = newVal
'<---puts the value in column 5 of the newly found  row
MsgBox "Item information is now on Sheet2 on line = " & fndRrow
End Sub

Nice suggestion but somewhat too advance for my level
and/or my simple  needs for now.

Could someone point or convert the line below
written in XLM macro  language

=FORMULA.FIND(Form!R2C6,1,1,1,,TRUE)

to its equivalent in VBA?

Maybe something like:

Sub findforms()
Dim cell  As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
If InStr(cell.Formula, "(") Then
MsgBox cell.Address
End If
Next
End Sub

then substitute what you are looking for in place of "("?

Worth a try. I use that form  to replace cells with formulas
with the values.

Thanks for your example. Ive also thought of doing my own
loop but did not want to re-invent the wheel if there was already a
subroutine/function already built in VBA.

Finally got my first VBA code  to work properly.