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

 

[JLGWhiz]

see if this will work for you

Sub t()
Dim fn As Range
With ActiveSheet
    Set fn = .Range("A1", .Cells(1, Columns.Count).End(xlToLeft)).Find("Date", , xlValues, xlPart)
        If Not fn Is Nothing Then
            Intersect(.Columns(fn.Column), .UsedRange).Replace "-", "/"
            Intersect(.Columns(fn.Column), .UsedRange).NumberFormat = "Date"
        End If
End With
End Sub

 

[Rick Rothstein]

Does this do what you want...

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 Columns(C).SpecialCells(xlConstants)
        .NumberFormat = "General"
        .Value = .Value
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[KennyGreens]

Hi, not sure if JLGWhiz's reply works for you, but if it does not, try this. There may be an easier way to do this, but I based it on your code:

Sub tm_Find_Change_Date_Format()'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 Then
                ActiveCell.EntireColumn.Select
                Selection.Replace What:="-", Replacement:="/", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
                Selection.NumberFormat = "mm/dd/yyyy"
            End If
    Next
End Sub

 

[Shiseiji]

First, thank you everyone! I'm working through all and will post results as tested.

JLG, interesting result. Found and changed the first date, "but": The cell displays "6at2009", the formula field displays "3/6/2009" and the Number Format is "custom." Changing the number format to Short Date results in a cell display of "3/6/2009"

 

[Shiseiji]

Hi Rick, I use your posting quite often, thanks for responding.
I changed

[COLOR=#333333].NumberFormat = "General"[/COLOR]

to

[COLOR=#333333].NumberFormat = "mm/dd/yyyy"[/COLOR]

It appears to have converted all the dates properly, some of the original strings had "0014-mm-dd" and I was leaving that mess for a later project. But it repeated the row 1 string at, best I can determine, in random "empty" cells.
The team that provides the report refuses to let me see their query or data structure, "security," so I have to just deal with what I'm given. The date format changed from "mm/dd/yyyy" without explanation or notification . . . and it's a guess what might be in the "empty" cells.

 

[Shiseiji]

Kenny, thank you as it helps me to see what I was doing incorrectly. "Integer" vs. Range. More study.
Inconsistent results with the search, "but", it could be something weird in the cell.
It skipped the first two date strings, "Background Inv Complete Date" & "Fingerprint Inv Complete Date" then did the find/replace on the 3rd string column, and then it skipped past five strings and ended in row 1 of the last column.

 

[Shiseiji]

Kenny, I missed that an exact match is required. "Date" fixed everything!
One string is "Last Updated." Any ideas?

Ron

 

[Rick Rothstein]

Hi Rick,
It appears to have converted all the dates properly, some of the original strings had "0014-mm-dd" and I was leaving that mess for a later project. But it repeated the row 1 string at, best I can determine, in random "empty" cells.

I did not know you had blank cells within your data... they screwed up my use of SpecialCells

In the code below, I have assumed those dates starting with 00 all take place in the year 2000 and beyond (that is, the 00 is never replaced by 19). With that in mind, see if this code works for you...

[table="width: 500"]
[tr]
	[td]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(@="""","""",0+@))", "@", .Address))
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Shiseiji]

Rick, 99%. The column strings all turned to "#Value."
For some of my work, I've resorted to filing the cell with "1/1/2009" as the real dates in that field start in 9/1/2010 . . . I'm putting your code on the "To Do" list for those reports.