Looping through columns to get column numbers based on headers

Question

I have a template with a set number of columns (170) and title headers (row 1 cell name's). This is always the same, until users add columns in between (they're instructed not to change headers). The idea is to make it tamperproof as far as the adding of columns is involved.

I'd like to make variables to hold some of the headers (with the capacity to hold all) and check these with the template to find out the column number (in a loop I reckon). It's probably wisest to make a function to call upon it?

Dim ColHeader1Str as string 'literal row 1, column 1 value (which is always 
'the same string and position in the template)
Dim iColHeader1 as integer 'holds the (to be set) value of the column number

Set ColHeader1Str = "ColHeader1"

Now I'd like a loop where it loops trough all the columns (last column = 200) and checks to see what the column number is that matches the ColHeader1Str and store this in the iColHeader1

So something like:

Function find_columnNmbr
  Dim i As Integer
    For i = 1 To 200 Step 1
     If 'ColHeader1Str matches actual column header name
        'set found integer as iColHeader1 and so forth
        Exit For
     End If
    Next
End Function`

I know I'm missing a few steps and I'm hoping you guys can help me out.

Update: The template has set column headers. When users interact with it a result could be that columns shift position, or they add more. I have a workbook that needs to load data out of the user's altered template.

I.E. The template has columns 1, 2, 3, 4 and the names are column1, column 2 etc. A user ads a random column so now there are 5. The loop needs to loop through the names of the column headers and identify the column number of the original template columns 1, 2 etc based on a string variable with the original names, which I've hard coded beforehand. These are public constants.

Not sure what you like to achieve. Say A1="Header A", B1="Header B" and so on, would you like to find the specific header name like Header G and store 7 in iColHeader1 since column G is the 7th column? — newacc2240
– newacc2240, Commented Nov 10, 2017 at 8:44
If so, Range("1:1").Find(ColHeader1Str) can return the range where ColHeader1Str is found. — newacc2240
– newacc2240, Commented Nov 10, 2017 at 8:45
So you are doing a reference chart between the user-edited one and the template? — newacc2240
– newacc2240, Commented Nov 10, 2017 at 9:41

newacc2240 · Accepted Answer · 2017-11-10 09:58:45Z

1

What function LookForHeaders do: input a string, then search for the string in usersheet.range(1:1). If it is found, return the column number of that cell, otherwise it returns 0.

Private Function LookForHeaders(ByVal headerName As String) As Long
    Dim rng As Range
    Dim userSheet As WorkSheet
    Set userSheet = 'userSheet here'

    On Error GoTo NotFound
    LookForHeaders = userSheet.Range("1:1").Find(headerName).Column
    Exit Function
NotFound:
    LookForHeaders = 0
End Function

Private Sub Test()
    Dim rng As Range
    Dim template As WorkSheet
    Set template = 'template here'
    For Each rng In template.Range(Cells(1,1), Cells(1,200))
        iColHeader1 = LookForHeaders(rng.Value)
        'Do something with iColHeader1
    Next rng
End Sub

answered Nov 10, 2017 at 9:58

newacc2240

1,4351 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

0m3r · Accepted Answer · 2017-11-10 08:52:21Z

0

Not sure what your looking for but here is example

Option Explicit
Public Sub Example()
    Dim LastCol As Long
    Dim i As Long

    LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

    For i = 1 To LastCol
        If Cells(i) = "Name" Then
            Debug.Print Cells(i).Address
        End If
    Next
End Sub

answered Nov 10, 2017 at 8:52

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

1 Comment

MyName Over a year ago

Updated the question

Collectives™ on Stack Overflow

Looping through columns to get column numbers based on headers

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related