1

I was working on an Excel project with multiple Tabs. One of the worksheets has a column called "Names". My users usually move the columns every now and then. So to extract the correct column I have used this particular VBA code.

HEADER = Sheets("WORKSHEET").Range("A1:Z1").Address
SourceDataColumn = Application.WorksheetFunction.Match("Name", HEADER, 0)
SourceColumnLetter = Split(Cells(1, SourceDataColumn).Address(True, False), "$")(0)

Example: In this case, the output would be [ SourceColumnLetter = C ] containing the "Names"

It works fine, I was just wondering if there is a way to set the 'SourceColumnLetter' as a global variable so that I don't have to use the same whole block of code again and again.

Any help would be appreciated. Thanks in Advance.

Improve this question
1
  • If the users are adjusting the columns by adding or removing columns, you could use: Sheet3.UsedRange.Columns.Count (where Sheet3 is the sheet you want to track) in Worksheet_Change event. If the column number changes, call the macro to reset SourceColumnLetter Commented Sep 10, 2020 at 13:22

2 Answers 2

Reset to default
3

Make a function GetColumnNumberByTitle that finds the column number for you. You can then easily access it by its number.

You can use GetColumnNumberByTitle("Name") in any of your procedures now to get the column by its title. Note that it returns 0 if it does not find the "Name".

See example below:

Option Explicit

Public Sub test()
    Dim ColNo As Long
    ColNo = GetColumnNumberByTitle("Name") 'get the column number by its title in row 1
    
    If ColNo <> 0 Then
        'access the column by its number
        Debug.Print ThisWorkbook.Worksheets("WORKSHEET").Cells(1, ColNo)
        'this should return the column name

        Debug.Print ThisWorkbook.Worksheets("WORKSHEET").Cells(2, ColNo)
        'this should return the columns first value
    Else
        MsgBox "Column Name was not found."
    End If
End Sub

Public Function GetColumnNumberByTitle(ByVal ColumnTitle As String) As Long
    On Error Resume Next 'next line errors if title is not found
    GetColumnNumberByTitle = Application.WorksheetFunction.Match(ColumnTitle, ThisWorkbook.Worksheets("WORKSHEET").Rows(1), 0)
    On Error GoTo 0 're-enable error reporting!
    'if title was not found this function returns 0
End Function

Another approach could be using formatted tables (ListObjects):

enter image description here

And you can then easily access a column by its title with the following code:

Worksheets("Sheet1").ListObjects("Table1").ListColumns("Name").Range.Select

or to select a specific row

Worksheets("Sheet1").ListObjects("Table1").ListColumns("Name").Range(3).Select
'selects row 3 in column "Name"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Any variable (even Global, Public etc.) must receive somehow a value...

If columns are inserted or deleted its reference is changed even if it has been defined on, let us say, Workbook_Open event.

I would suggest you to make it a named range. Select it, go to the left formula bar side (Name Box), write the name you need (HD, for instance) and press Enter.

Than, you can use it like:

 SourceColumnLetter = Split(Range("HD").Address(True, False), "$")(0)

or better try using Cells instead of Range, doing it in the next way (without the column letter):

Cells(x, range("HD").Column)

without any preliminary calculation...

Improve this answer

5 Comments

Thanks, it is a good approach to get the headers, I will modify my code, however, as my users change the columns I need to dynamically find the Column Letter for "Name" column and set it as a global variable. So that I can use it on multiple macros.
@Wish Well since your users change the columns you would need to find the "Name" column everytime directly before using it. Because you never know when the user changed/moved it. Example you find the "Name" column on workbook open, the user changes the column, your global variable links to the wrong column now and your macro fails.
@Wish: If you insist to create a variable, you only risk to use its value which sometimes it may not be the correct one. Since, following my suggestion, Range("HD").Column will always be automatically updated by Excel after any Insert/Deletion... PEH is right!
@peh, FaneDuru Thanks, In that case, if I assign a button for the users called "Process" which is triggers the macro Sub getColumnLetters() to get the values instead of Workbook_Open event, can I assign the values globally? The problem is there are other columns as well one for names, age, Salary etc etc.. Total columns in the worksheet is from "A: AZ" that is 56 columns. My workbook contains around 15 modules and at least 10 of them use the code block as above to get the Column letters for "Names" "Age" "Salary" etc. If I declare these globally, I can reduce some code repetition.
@Wish: In such a case, you may use a Function to be called by all Subs. It should return the letter or the column number. You will put all the headers in discussion in an array and the function will return another array of columns numbers, or each of them number/letter, according to your specific need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.