Find column (in Excel address) of matching value in array

[Lagos]

I need to find the Column, e.g. "L" (in the Excel address) for the cell in an Array read in from Excel which contains a value matching a specified variable x.



[vba]

Public Sub RetAdr()
Dim VarArray as Variant
Dim x as integer
Dim RetAddress as String

x= 12 ' As an example,... looking for the Column of cell containing the value: 12

VarArray = Worksheets("Sheet1").Range("A1:CV1") ' values in VarArray are: 1,2,3... 100
RetColAddress = WorksheetFunction.Address(WorksheetFunction.Match(x,VarArray,0)) ' Need help here!

MsgBox = RetColAddress ' Should display "L"
End Sub
[/vba]

[stanleydgrom]

Lagos,


Try this:


[VBA]

Option Explicit
Public Sub RetAdr()
Dim VarArray As Variant
Dim x As Long
Dim RetColAddress As String
x = 12
VarArray = Worksheets("Sheet1").Range("A1:CV1")
RetColAddress = Replace(Cells(1, Application.Match(x, VarArray, 0)).Address(0, 0), 1, "")
MsgBox "RetColAddress = " & RetColAddress
End Sub

[/VBA]


Have a great day,
Stan