Find the location of cell

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

hi

can any one help me to find the location of the cell which contain certain value in the excel sheet and then print its location at specific location!!

vba or Function

Regards

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
suppose data is from A1 to a7 as follows

1
2
3
rv
4
5
6

now you want fo find out the cell address of "rv" and enter that cell address in some other location

use this formula

="A"&MATCH("rv",$A$1:$A$7,0)

as you have not given where the data is, what data is to be found etc you have to modify the formula to suit you.
hi thanks for the help and it works great but can you give the vba code for the same..
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
for this particular problem macro is NOT necessary, formula will do
A constraint is that id more "rv" are in the column it will find the cell address of only the first occurrence.

if you still need the macro
assuming data is from A1 to a7 as given in previous message
the macro will be

Sub test()
Dim r As Range, cfind As Range, add As String
Worksheets("sheet1").Activate
Set r = Range(Range("a1"), Range("a1").End(xlDown))
Set cfind = r.Find(what:="rv", lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
MsgBox add
End If
End Sub