Cell Echoing Help in VBA

[Dave_D]

Is there a VBA routine that will echo back the cell
location based on the value in another cell ? For
example, consider the following

A B C D
1 101 110.5
2 102
3 103.9
4 104
5 110.5
6 121

I'm looking for the cell location for the value (110.5)
contained in cell C1 and place the returned location (A5)
in cell D1.

 

[William]

Hi Dave

To use a formula on the worksheet....
In cell D1 enter
=IF(COUNTIF(A:A,C1)>0,"A"&MATCH(C1,A:A,0),"No match")

Using code....

Sub test()
If Application.CountIf(Range("A:A"), Range("C1") * 1) > 0 Then
Range("D1") = Columns("A:A").Find(What:=Range("C1") * 1).Address
Else
Range("D1") = "No Match"
End If
End Sub

OR

Sub test2()
Range("D1").FormulaR1C1 = _
"=IF(COUNTIF(C[-3],RC[-1])>0,""A""&MATCH(RC[-1],C[-3],0),""No match"")"
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Is there a VBA routine that will echo back the cell
| location based on the value in another cell ? For
| example, consider the following
|
| A B C D
| 1 101 110.5
| 2 102
| 3 103.9
| 4 104
| 5 110.5
| 6 121
|
| I'm looking for the cell location for the value (110.5)
| contained in cell C1 and place the returned location (A5)
| in cell D1.
|

 

[Guest]

THanks for your help. It works just fine.