Absolute Cell Reference Using Match

[jumpbackjack]

I'm trying to find an easy way to get a cell's absolute reference using the Match function. So far I can only get a relative reference. I have an array and when I find X, I want to know the cells absolute reference. Does anyone know how that can be done. Here is an example of what I have now.

Range("I3").Value = WorksheetFunction.Match(Range("J3"), Range("F4:F13"), 0)

This returns the 2nd cell which would be F6. I can calculate it by adding 2 rows to F4, but it seems like a waste of time.

Thanks

[June7]

See if something here helps https://www.exceldemy.com/excel-vba-get-row-and-column-number-from-cell-address/#:~:text=4%20Methods%20to%20Get%20Row%20and%20Column%20Number,Column%20Numb er%20from%20Cell%20address%20in%20Excel%20

[jumpbackjack]

Thanks June7. I found this using the Find function, which would be perfect, but I get a an "invalid use of property" for Address This is making me nuts[COLOR=#333333][FONT=consolas][TABLE]

Sub Macro1()
[MsgBox Range("B3:B8").Find(Range("D3")).Address
End Sub

[June7]

Where did you find that code? What makes you think it would work? I tested. It works for me.

[mancubus]

Sub find_func()

Dim rng As Range

Set rng = Sheets("Sheet1").Range("B3:B8").Find(Range("D3").Value)

If Not rng Is Nothing Then
MsgBox rng.Address & vbLf _
& rng.Address(RowAbsolute:=False) & vbLf _
& rng.Address(ColumnAbsolute:=False) & vbLf _
& rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If


End Sub

[Paul_Hossler]

Thanks June7. I found this using the Find function, which would be perfect, but I get a an "invalid use of property" for Address This is making me nuts[COLOR=#333333][FONT=consolas][TABLE]

Sub Macro1()
[MsgBox Range("B3:B8").Find(Range("D3")).Address
End Sub



Try something like this


Sub Macro1()
MsgBox Range("B3:B8").Find(Range("D3").Address
End Sub

[jumpbackjack]

Thanks for all of your help, but I can't get it to work. Here is the latest. I'm not really sure about the Set function and what it does. It didn't work without it either.


Sub Macro1()
Dim rng As Range
Set rng = Sheets("Array").Range("B3:B8").Find(Range("D3").Value)
End Sub

[June7]

Try something like this


Sub Macro1()
MsgBox Range("B3:B8").Find(Range("D3").Address
End Sub

Paul, isn't this what OP has already tried? BTW, yours is missing a right parentheses.

Well, I just noticed OP's code in post 3 has a stray bracket in front of MsgBox.

Jack, I tested code again. and it does work, without the bracket character.