PDA

View Full Version : Find column (in Excel address) of matching value in array



Lagos
08-26-2011, 10:09 AM
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.





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

stanleydgrom
08-26-2011, 12:44 PM
Lagos,


Try this:




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




Have a great day,
Stan