Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Macro VBA: Dynamically assigning a column to a variable

  • 29-04-2009 9:53am
    #1
    Registered Users Posts: 3,110 ✭✭✭


    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


Comments

  • Registered Users Posts: 3,110 ✭✭✭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?


  • Registered Users Posts: 3,110 ✭✭✭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


Advertisement