VBA - Search table headers for date

[cooper645]

Hi guys,

I think I'm nearly there but can't seem to get it to do what I want.
I am trying to find the first instance of a date or date format in column headers, from left to right.

Here's the code I have managed so far, but it doesn't do as I need.

Sub InsertG1Dates()


Dim tbl As String


tbl = "_2018" 'Amend this to whatever the table name is


For i = 1 To 20
    If ActiveSheet.ListObjects(1).HeaderRowRange(i).Value = Date Then
        ActiveSheet.ListObjects(1).HeaderRowRange(i).Select
    Else
    End If
Next


End Sub

Any steer or help would be appreciated.

Thanks in advance.

Coops

 

[mumps]

Try:

Sub FindDate()
    Dim strdate As String
    strdate = CStr(Date)
    Dim foundDate As Range
    Set foundDate = Rows(1).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    foundDate.Select
End Sub

 

[cooper645]

Thanks, it works if the information is not in a table, however I receive a run-time error '91'. object variable or with block variable not set

 

[mumps]

I tried the macro on a sample table I created and it worked properly. Are the dates in row 1?

 

[cooper645]

No, Row 9, but I amended your code to suit as below.

Sub FindDate()
    Dim strdate As String
    strdate = CStr(Date)
    Dim foundDate As Range
    Set foundDate = Rows(9).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    foundDate.Select 
[COLOR=#333333]End Sub[/COLOR]

My code works to find a specific text, but it needs to be dynamic and find the first string that is a date. I don't think I can format the header as a date which is my issue. maybe I need to search for a string format like "##/##/####"

I should say I am writing this on a Mac Excel 2016 but will ultimately be used on Windows Excel 2016

 

[mumps]

You can format the header as a date. I tried the following on a sample table with the header row in row 1 formatted as 'Date' and it worked properly:

Sub FindDate()
    Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim rng As Range
    For Each rng In Range(Cells(1, 1), Cells(1, lCol))
        If IsDate(rng) Then
            rng.Select
        End If
    Next rng
End Sub

 

[cooper645]

I amended it for row 9, and it works to find the furthest right date, not the furthest left. Also I can confirm I can and have formatted headers as date.

I amended my code also to work partially, it just doesn't exit upon finding the first matching string

Sub InsertG1Dates()




Dim tbl As String


tbl = "_2018" 'Amend this to whatever the table name is


For i = 1 To 20
    If ActiveSheet.ListObjects(1).HeaderRowRange(i).Value Like "##/##/####" Then
        ActiveSheet.ListObjects(1).HeaderRowRange(i).Select
    Else
    End If
Next




End Sub

 

[cooper645]

Issue solved I believe, this seems to work;

Sub InsertG1Dates()


For i = 1 To 20
    If IsDate(ActiveSheet.ListObjects(1).HeaderRowRange(i)) Then GoTo continue
    Next


continue:
ActiveSheet.ListObjects(1).HeaderRowRange(i).Select


End Sub

Thanks Mumps for your help, it gave me options to clean my code and simplify.
If you can see any reason I would have troubles I am all ears.

 

[mumps]

My pleasure.