Excel Macro VBA: Dynamically assigning a column to a variable

KevR

I have a bit of programming experience but am new to VB/Excel Macros. I'm not even a heavy user of Excel so it could be my understanding of that which is hindering me

Basically what I am looking to try and do is:

• Search across a given row (e.g - Row 1) for a given value in one of the cells.
• When the value is found/matched the search doesn't need to continue. What I want to do is assign the column that the value was found in to a variable.
• So say if the the given value is found in column E I need to assign column E to a variable (variable name could be ColVar).

Have spent a lot of time trying to figure this out but have had no joy whatsoever. Any help would be greatly appreciated.

A B C D E F G
1Title1 Title2 Title3 Title4 Title5 Title6 Title7
2
3
4

For example, I want to search for the text 'Title5' in Row1 and when this is found I want to assign the corresponding column (which would be E in this case) to a variable (ColVar). Then I would be able use/refer to this variable (column) further on in the program.

An aweful attempt but hopefully it will help give an idea of what I'm looking for:

Sub DynamicColumnAssigning ()
Dim ColVar As String

WorksheetFunction.Match("Title5", Range("1:1"), 0)

ColVar = ActiveCell.EntireColumn

End Sub


Many thanks,
Kev

KevR

I have this which I think is giving me a column number rather than a letter reference (i.e - when I want 'E' assigned to ColVar, it's assigning 5 instead).

ActiveCell = WorksheetFunction.Match("Title5", Range("1:11"), 0)
ColVar = ActiveCell.Column

Does anyone know if the above code is on the right track at all? Do I have to somehow convert the column number into a letter reference? Don't suppose anyone would have any advice on how I could do this?

KevR

Finally got it to work.

In case anyone was wondering, for what I wanted, I did have to convert the column number back to a letter.

' Find given value
ColVar = WorksheetFunction.Match("Title5", Range("1:1"), 0)

' Convert number to letter and assign column letter to variable
If ActiveCell.Column > 26 Then
ColVar = Chr(Int((ActiveCell.Column - 1) / 26) + 64) & Chr(((ActiveCell.Column - 1) Mod 26) + 65)
Else
ColVar = Chr(ActiveCell.Column + 64)
End If