excelvbaexcel-365

IsError not capturing error returned by expression


What I am trying to do

I am trying to determine the first column in a named table that has a date.

What I have tried

Since all data in a table header row are stored as strings I figured I would have to first strip off the date numbers as a string, then convert them to a number, Then check if it is an error incase no numbers were in the pulled string.

My code to find the column of the table is as follows and blows up at the IF statement.

For Each HeaderCell In LO.HeaderRowRange
    If Not IsError(CLng(Left(HeaderCell.value, 4))) Then
        lngFirstDateCol = HeaderCell.Column
        Exit For
    End If
Next HeaderCell

I added each part of the check to the watch window. HeaderCell.Value returns "Project No". Left returns "Proj". CLng shows as a . IsError shows as a .

enter image description here

My example header row looks like the following

Proj No | TTTT | Office | Staff | RFP | 2024/10/4 | 2024/10/11

With the Example header I would like to return the value of 6

Question

What is the right approach to finding The first column that is a date column? Currently I have it hard coded.


Solution

  • Use IsDate to validate the value.

    Sub demo()
        Dim c As Long, bFound As Boolean
        For c = 1 To Columns.Count
            If IsDate(Cells(1, c)) Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
            MsgBox "The first date column (index) is " & c
            ' your code
        Else
            MsgBox "Can't locate date in the first row."
        End If
    End Sub
    

    enter image description here


    Locate the first date col in ListObject header row.

    Sub demo()
        Dim c As Range, bFound As Boolean
        Dim LO As ListObject
        Set LO = Sheet1.ListObjects(1)
        For Each c In LO.HeaderRowRange
            If IsDate(c) Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
            MsgBox "The first date column (index) in LO is " & LO.ListColumns(c.Value).Index
            ' your code
        Else
            MsgBox "Can't locate date in the header."
        End If
    End Sub
    

    enter image description here