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
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel Macro VBA: Dynamically assigning a column to a variable

  • 29-04-2009 10:53am
    #1
    KevR
    Registered Users, Registered Users 2 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
    Tagged:


Comments

  • #2
    KevR
    Registered Users, Registered Users 2 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?


  • #3
    KevR
    Registered Users, Registered Users 2 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