Merging Match and Split Formulas

[HGDantes]

Hello
Do you think that would be possible to merge both formulas/functions in order to obtain my ColumnLetter value ? If so, what would be the proper syntax?

Dim ColumnNumber As Long 
Dim ColumnLetter As String 
ColumnNumber = WorksheetFunction.Match("Column Name", Rows("1:1"), 0) 
ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)

Thanks

 

[johnnyL]

Can you explain, with words, what you are trying to do?

 

[HGDantes]

Sure.
I want to search an excel sheet for specific header names and put the letter of the column in a Variable.
With the Match Function, I'm able to retrieve the numerical value of the column.
Then with the Split I'm able to get the letter of the column from it's numerical value.
I would like to be able to get the result with a single operation.

 

[johnnyL]

I thought so, just wanted to make sure.

Dim ColumnLetter As String
ColumnLetter = Split(Cells(1, WorksheetFunction.Match("Column Name", Rows("1:1"), 0)).Address, "$")(1)

 

[Peter_SSs]

Welcome to the MrExcel board!

Since you are using vba, I would be inclined to stick to features that are naturally available there rather than reverting to worksheet functions. Perhaps ..

Dim ColumnLetter As String

ColumnLetter = Split(Rows(1).Find(What:="Column Name", LookAt:=xlWhole, MatchCase:=False).Address, "$")(1)

 

[HGDantes]

Welcome to the MrExcel board!

Since you are using vba, I would be inclined to stick to features that are naturally available there rather than reverting to worksheet functions. Perhaps ..

Dim ColumnLetter As String

ColumnLetter = Split(Rows(1).Find(What:="Column Name", LookAt:=xlWhole, MatchCase:=False).Address, "$")(1)

Super Thanks A lot. It's working perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.