CODEWITHSUNDEEP
vbaexcel
MyName
MyName

Reputation: 370

Looping through columns to get column numbers based on headers

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.

Upvotes: 0

Views: 1125

Answers (2)

newacc2240
newacc2240

Reputation: 1425

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

Upvotes: 1

0m3r
0m3r

Reputation: 12499

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

Upvotes: 0

Related Questions