Excel VBA: Finding column number of Nth field name

Question

I have a function where I specify the field I want and the header row number and it returns the column. E.g. =findField("Region",1) would return the column number containing the header "Region". This worked well until I encountered a report containing duplicate names in the header row. E.g. instead of 1st and last name it would have "Name" for both fields so I needed to specify the occurrence I wanted as in =findField("Name",1,2) for the 2nd occurrence. I came up with a solution but it has 2 issues. The first is that if the field is in the first column it won't work properly. E.g. if columns A and B have "Name" then =findField("Name",1,1) would return the second field instead of the first and =findField("Name",1,2) would wrap around and return the 1st which is not what I want. The second issue is that it wraps around which I would prefer it not to do at all. What I came up with is as follows:

Function findField2(fieldName As String, Optional rowStart As Long = 0, Optional occurrence As Long = 1)
    Dim Found As Range, lastRow As Long, count As Integer, myCol As Long

    If rowStart = 0 Then rowStart = getHeaderRow()
    myCol = 1

    For count = 1 To occurrence
        Set Found = Rows(rowStart).Find(what:=fieldName, LookIn:=xlValues, lookat:=xlWhole, After:=Cells(rowStart, myCol))

        If Found Is Nothing Then
            MsgBox "Error: Can't find '" & fieldName & "' in row " & rowStart
            Exit Function
        Else
            myCol = Found.Column
        End If
    Next count

    lastRow = Cells(Rows.count, Found.Column).End(xlUp).Row
    findField2 = Found.Column

What do I need to do to allow for the field being in column A? Putting in 0 for myCol doesn't work. The initial finding function was based off https://www.mrexcel.com/forum/excel-questions/629346-vba-finding-text-row-1-return-column.html and I was tweaking it to suit my needs.

Thanks, Ben

Your best bet would be to use the last cell in the row as your start for the `After` parameter — Tim Williams, Dec 15 '18 at 22:51
@BigBen `myCol` updates as the loop progresses but Yes some reworking would be needed — Tim Williams, Dec 15 '18 at 22:55
Only for the initial `Find()`. There's also currently nothing to stop the Find() looping back, so it will return a "10th" value even if there are only 3 present. — Tim Williams, Dec 15 '18 at 22:58

score 1 · Answer 1 · answered Dec 15 '18 at 23:50

1

Here's something not using Find() which should still meet your goals:

Function findField2(fieldName As String, Optional rowStart As Long = 0, _
                                           Optional occurrence As Long = 1)
    Dim a, rw As Range, m

    If rowStart = 0 Then rowStart = getHeaderRow()

    With ActiveSheet 'might be better to pass the sheet as a parameter
        Set rw = Application.Intersect(.Rows(rowStart), .UsedRange)
        a = .Evaluate("=IF(" & rw.Address & "=""" & fieldName & _
                            """,COLUMN(" & rw.Address & "),FALSE)")
    End With

    m = Application.Small(a, occurrence) 'find the n'th match (will return an error if none)

    If IsError(m) Then MsgBox "No occurrence #" & occurrence & " of '" & _
                              fieldName & "' on row# " & rowStart, vbExclamation

    findField2 = IIf(IsError(m), 0, m)
End Function

Sub Tester()
    Debug.Print findField2("A", 5, 40)
End Sub

answered Dec 15 '18 at 23:50

Tim Williams

154,628
8
97
125

What 'getHeaderRow()'? – YasserKhalil Dec 16 '18 at 18:42
@YasserKhalil I have no idea - it's in the OP's code though. The `rowStart` parameter is optional, sio I'd guess it provides a default value for that. – Tim Williams Dec 16 '18 at 19:29
@YasserKhalil getHeaderRow() is a function I wrote to work out the header row in the sheet if it isn't specified as a function parameter. It works out the last row/column and then based on the last column searches down from the 1st row until it hits a non-empty cell. – Ben Dec 17 '18 at 02:35
Thanks a lot. Can you provide us with the function to have a look? – YasserKhalil Dec 17 '18 at 03:53
@YasserKhalil I provided the function at the end of the answer I just posted. – Ben Dec 17 '18 at 06:22
@TimWilliams In your code does m become a variant type since nothing is specified? – Ben Dec 17 '18 at 06:23
I didn't find it ..I am sorry for disturbing you – YasserKhalil Dec 17 '18 at 06:38
1

@YasserKhalil I had edited it in to my response afterwards and checking now it wasn't there. I have edited my response again to include the function. – Ben Dec 17 '18 at 14:08
@Ben - yes it's purposely a variant. – Tim Williams Dec 17 '18 at 15:58

VBasic2008 · Answer 2 · 2018-12-16T14:05:48.973

Found-in-row Column feat. the Wrap Around Issue

No object references here i.e. everything refers to the ActiveSheet (of the ActiveWorkbook).

Find (After)

By default the Find method starts the search from the next cell (6. SearchDirection xlNext or 1) of the supplied cell range parameter of the After argument (2. After) i.e. in case you use cell A1 by row (5. SearchOrder xlByRows or 1), the search will start from B1, continue until the last column, wrap around and continue with A1 last. Therefore the last cell of the row has to be used to start the search with the first cell A1.

Wrap Around

The Wrap Around issue is solved with an If statement only if the Occurrence Number is greater than 1. If no occurrence was found, 0 is returned.
The column number of the found cell (intCol) is passed to a variable (intWrap) and every next occurrence of the value, they are checked against each other. Now, if the variable is equal to the column number, the function returns -1, indicating that the value was found but the specified occurrence has not been found.

'*******************************************************************************
' Purpose:    Finds the Nth occurrence of a value in cells of a row            * 
'             to return the column number of the cell where it was found.      *
'*******************************************************************************
' Inputs                                                                       *
'   FindValue:          The value to search for.                               *
'   FindRow:            The row to search in.                                  *
'   OccurrenceNumber:   The occurrence number of the value to search for.      *
'*******************************************************************************
' Returns:    The column number of the Nth occurrence of the value.           *
'              0 if value was not found.                                       *
'             -1 if value was found, but not the specified occurrence of it.   *
'             -2 if worksheet has no values (-4163).                           *
'             -3 if workbook is add-in (No ActiveSheet).                       *
'*******************************************************************************
Function FoundinrowColumn(FindValue As Variant, Optional FindRow As Long = 0, _
    Optional OccurrenceNumber As Integer = 1) As Integer

  Dim intCol As Integer     ' Search Start Column Number
  Dim intCount As Integer   ' OccurrenceNumber Counter
  Dim intWrap As Integer    ' Wrap Around Stopper

  ' Check if ActiveSheet exists.
  If ActiveSheet Is Nothing Then FoundinrowColumn = -3: Exit Function
  ' Check if sheet has no values.
  If Cells.Find("*", Cells(Rows.count, Columns.count), -4163, 1, 1) _
      Is Nothing Then FoundinrowColumn = -2: Exit Function

  ' Find first used row if no FindRow parameter.
  If FindRow = 0 Then
    FindRow = Cells.Find("*", Cells(Rows.count, Columns.count)).Row
  End If
  ' Set initial Search Start Column Number.
  intCol = Columns.count

  ' Try to find the Nth occurence of 'FindValue' in 'FindRow'.
  For intCount = 1 To OccurrenceNumber
    If Not Rows(FindRow).Find(FindValue, Cells(FindRow, intCol)) Is Nothing Then
      intCol = Rows(FindRow).Find(FindValue, Cells(FindRow, intCol)).Column
      If intCount > 1 Then
        If intCol = intWrap Then FoundinrowColumn = -1: Exit Function
       Else
        intWrap = intCol
      End If
     Else
      FoundinrowColumn = 0: Exit Function
    End If
  Next

  FoundinrowColumn = intCol

End Function
'*******************************************************************************

Ben · Answer 3 · 2018-12-17T14:06:47.190

Thanks for your responses. I am picking up useful techniques which is of great help. I actually fixed the first issue based on @TimWilliams to set myCol to the last column so it starts the find at the first column and added a check for the wrap around per the below. I also changed the msgBox to return a value instead per @VBasic2008.

Function findField2(fieldName As String, Optional rowStart As Long = 0, Optional occurrence As Long = 1)
    Dim Found As Range, lastRow As Long, count As Integer, myCol As Long

    If rowStart = 0 Then rowStart = getHeaderRow()
    myCol = 16384

    For count = 1 To occurrence
        Set Found = Rows(rowStart).Find(what:=fieldName, LookIn:=xlValues, lookat:=xlWhole, After:=Cells(rowStart, myCol))

        ' Check if nothing found or for wrap around and Nth occurrence not found
        If Found Is Nothing Or count > 1 And Found.Column <= myCol Then
            findField2 = 0
            Exit Function
        Else
            myCol = Found.Column
        End If
    Next count


    lastRow = Cells(Rows.count, Found.Column).End(xlUp).Row
    findField2 = Found.Column
End Function

Here is the getHeaderRow function mentioned in the findField function above:

Function getHeaderRow() As Long
    Dim i As Long, lastCol As Long, lastRow As Long
    lastCol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
    lastRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
    i = 1

    Do While Cells(i, lastCol).Value = ""
        i = i + 1
        If i > lastRow Then
            i = 0
            Exit Do
        End If
    Loop

    getHeaderRow = i
End Function

Might be worth having a look at `FindNext` to find anything after the first occurrence. — Darren Bartrup-Cook, Dec 17 '18 at 10:21
If your sheet is blank `lastCol` will try and return row 0 which will throw an `Object variable or With block variable not set` error. Have a look at this [question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for some examples of finding the last cell. — Darren Bartrup-Cook, Dec 17 '18 at 14:13

score 0 · Answer 4 · answered Dec 17 '18 at 11:20

This version uses FindNext to search for occurrences after the first.
It searches Sheet1 of the workbook that the code is in (ThisWorkbook):

Sub Test()

    Dim MyCell As Range

    'Second occurrence default row.
    Set MyCell = FindField("Date", Occurrence:=3)

    If Not MyCell Is Nothing Then
        MsgBox "Found in cell " & MyCell.Address & "." & vbCr & _
            "Row: " & MyCell.Row & vbCr & "Column: " & MyCell.Column & vbCr & _
            "Sheet: '" & MyCell.Parent.Name & "'" & vbCr & _
            "Workbook: '" & MyCell.Parent.Parent.Name & "'", vbOKOnly + vbInformation
    Else
        MsgBox "Value not found."
    End If

End Sub

Public Function FindField(FieldName As String, Optional RowStart As Long = 0, _
    Optional Occurrence As Long = 1) As Range

    Dim rFound As Range
    Dim x As Long
    Dim sFirstAdd As String

    If RowStart = 0 Then RowStart = 1
    x = 1

    With ThisWorkbook.Worksheets("Sheet1").Rows(RowStart)
        Set rFound = .Find( _
            What:=FieldName, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            After:=.Cells(RowStart, .Columns.Count))

        If Not rFound Is Nothing Then
            Set FindField = rFound
            If Occurrence <> 1 Then
                sFirstAdd = rFound.Address
                Do
                    Set rFound = .FindNext(rFound)
                    x = x + 1

                Loop While x <> Occurrence And rFound.Address <> sFirstAdd
                If rFound.Address = sFirstAdd Then
                    Set FindField = Nothing
                End If
            End If
        End If
    End With

End Function

Excel VBA: Finding column number of Nth field name

4 Answers4

Found-in-row Column feat. the Wrap Around Issue

Find (After)

Wrap Around