Find a partial string in row 1, find/replace string in column, reformat column, next

[Shiseiji]

I receive a report that has row 1 headings that include "date" with cell text strings of "yyyy-mm-dd." What I produce is a series of date based windows. So the need is to look in row 1 for all occurrences of the string "date", select the column, find "-" & replace with "/" then format the column as Date. But nothing I've tried to simplify the recorded macro code, remove the recorded column numbers, etc. works.

So I'm asking for help . . . I've left in everything I've tried as remarks to assist in pointing out the errors of my thoughts.
Bottom Line Up Front:
3 problems
1. The error message is that I'm not correctly setting the Object
2. Next isn't working. Column 1 is selected then the macro stops.
3. I can't figure out how to change "ReplaceFormat:=False" as seen below . . .

As my guidance for setting the range, I'm attempting to use a posting I found at StackOverFlow https://stackoverflow.com/questions...ce-between-dim-and-set-in-vba/3884929#3884929

Set statement is only used for object variables (like Range, Cell or Worksheet in Excel)
The simple equal sign '=' is used for elementary datatypes like Integer.
Dim declares the variable.
Dim r As Range
Set sets the variable to an object reference.
Set r = Range("A1")

TIA

Ron

Sub tm_Find_Change_Date_Format()
'test macro to fix text dates
    
     
    Dim LastCol                         As Integer
    Dim LastRow                         As Long
    Dim hRow                            As Range
    Dim thiswksht                       As Worksheet
   
    '
    Set thiswksht = ActiveSheet
    Set thiswb = ActiveWorkbook
    '
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    ' ---------------- Failed -----------------------------
     'Set hRow = Range(Cells(1, LastCol))
     'Cells(1,1).Activate
     'Set hRow = Range(ActiveCell.EntireRow) 
    'Range(Cells(1, LastCol)).Name = "hRow"
    'Range(Selection, Selection.End(xlToRight)).Select
    'Selection.Name = "hRow"
    'Range("hRow").Select
    'Rows("1").Select
    'Range("1:1").Select
    ' ---------------------------------------------------

    For Each cell In Selection
    If InStr(cell.value, "date") > 0 Then
    ActiveCell.EntireColumn.Select
    Selection.Replace What:="-", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "mm/dd/yyyy"
    Cells(1, ActiveCell.Column).Select
    End If
    Next cell
    '
    Cells(1, 1).Activate
End Sub

 

[Rick Rothstein]

Rick, 99%. The column strings all turned to "#Value."

I think this modification should fix that problem...

Sub tm_Find_Change_Date_Format()
  Dim C As Long
  Application.ScreenUpdating = False
  For C = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    If InStr(1, Cells(1, C).Value, "date", vbTextCompare) Then
      With Intersect(ActiveSheet.UsedRange, Columns(C))
        .NumberFormat = "mm/dd/yyyy"
        .Value = Evaluate(Replace("IF(LEFT(@)=""0"",0+""2""&MID(@,2,9),IF(@="""","""",IF(ISTEXT(@),@,0+@)))", "@", .Address))
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[Shiseiji]

Ah!

If InStr(Cells(1, s), "Date") Or InStr(Cells(1, s), "Update") Then

Now to combine this with Rick's date fix . . .

 

[Shiseiji]

Rick, thank you!
I treat them all as lessons, and for 99% I can read and understand what's been provided. I don't expect to ever be a real coder, but they all get passed forward, and I pass on my co-worker's appreciation for making their lives just a little bit easier.
77 questions posted, an untold number answered by the generosity of all.

Thank you everyone!

Ron
In the land of the blind . . .

 

[Shiseiji]

Final code for this solution:

Sub m_FixAllDates() 'test macro to fix text dates
    
     
    Dim LastCol As Integer
    Dim LastRow As Long
    Dim thiswksht As Worksheet
   
    Set thiswksht = ActiveSheet
    Set thiswb = ActiveWorkbook
    
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    
    Dim s As Integer
    For s = 1 To LastCol
        Cells(1, s).Select
            If InStr(Cells(1, s), "Date") > 0 Or InStr(Cells(1, s), "Update") > 0 Then
                ActiveCell.EntireColumn.Select
                With Selection
                .Replace What:="-", Replacement:="/", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
                .NumberFormat = "mm/dd/yyyy"
                .Value = Evaluate(Replace("IF(LEFT(@)=""0"",0+""2""&MID(@,2,9),IF(@="""","""",IF(ISTEXT(@),@,0+@)))", "@", .Address))
            End With
            End If
    Next
End Sub