I am trying to determine the first column in a named table that has a date.
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 .
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
What is the right approach to finding The first column that is a date column? Currently I have it hard coded.
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
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