Search Row 1 for Text if Found Convert Column to Date

airforceone

airforceone

Board Regular
Joined
Feb 14, 2022
Messages
201
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
good day mate,

I decided to try converting my MS Access Form to Excel Userform (have a valid reason), so my would be series of question for the days ahead :) starts now...
I have a Table with a Header with different capitalization of text entry (ie. Date, DATE, DaTe, date)
what I would like to achieve is search the first row (table header) is such header contains a string of "date" in it, that particular column gets converted to DATE format (dd/mm/yyyy) in the included Sample Table four (4) columns should be converted to DATE format
I search a few dozen excel forum already but came up empty. so far code includes,

CCTO

VBA Code: 
Dim rFind As Range
With ThisWorkbook.Sheets("Sheet1").Rows(1)
    Set rFind = .Find(What:="*DATE*", Lookat:=xlPart, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        MsgBox "Col: " & rFind.Column & ", Row: " & rFind.Row
    End If
End With

'2nd example

For Each Ws In ActiveWorkbook.Worksheets
    With Ws
        If .Index <> 1 Then
        Dim rngSearch As Range, rngLast As Range, rngFound As Range
        Dim strFirstAddress As String
        Set rngSearch = .Range("A1:CH1")   
        Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)   
        Set rngFound = rngSearch.Find("*DATE*", after:=rngLast, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            If Not rngFound Is Nothing Then
                strFirstAddress = rngFound.Address
                Do
                    Set rngFound = rngSearch.FindNext(rngFound)       
                    DEBUG.PRINT strFirstAddress
                Loop Until rngFound.Address = strFirstAddress
            End If
        End If
    End With
Next Ws

Sample Table
ULTIMATE GENERATOR.xlsm
ABCDEFGHIJKLMN
1col 1col 2date enrolledcol 3col 6Date Exitedcol 8col 10DATE Returnedcol 12col 13DaTe Encodedcol 15time Encoded
2lorem sumlorem sum2016-01-14lorem sumlorem sum2016-01-14lorem sumlorem sum2016-04-06 11:56:00lorem sumlorem sum2016-01-14lorem sum10:30:00
3lorem sumlorem sum2015-12-31lorem sumlorem sum2016-01-01lorem sumlorem sum2016-04-07 02:50:55lorem sumlorem sum2015-12-31lorem sum13:15:00
4lorem sumlorem sum2016-01-03lorem sumlorem sum2016-01-03lorem sumlorem sum2016-04-07 04:50:40lorem sumlorem sum2016-01-03lorem sum07:50:00
5lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-04-07 11:15:51lorem sumlorem sum2016-01-06lorem sum11:30:00
6lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-04-07 11:44:58lorem sumlorem sum2016-01-06lorem sum19:00:00
7lorem sumlorem sum2016-01-08lorem sumlorem sum2016-01-08lorem sumlorem sum2016-04-07 12:03:21lorem sumlorem sum2016-01-08lorem sum14:20:00
8lorem sumlorem sum2016-01-09lorem sumlorem sum2016-01-09lorem sumlorem sum2016-04-07 12:31:30lorem sumlorem sum2016-01-09lorem sum19:50:00
9lorem sumlorem sum2016-01-15lorem sumlorem sum2016-01-15lorem sumlorem sum2016-04-07 13:12:07lorem sumlorem sum2016-01-15lorem sum18:40:00
10lorem sumlorem sum2016-01-16lorem sumlorem sum2016-01-16lorem sumlorem sum2016-04-07 13:25:24lorem sumlorem sum2016-01-16lorem sum21:55:00
11lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-04-07 13:39:07lorem sumlorem sum2016-01-17lorem sum23:10:00
12lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-04-07 13:57:58lorem sumlorem sum2016-01-17lorem sum23:50:00
13lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-04-07 17:35:44lorem sumlorem sum2016-01-17lorem sum09:45:00
14lorem sumlorem sum2016-01-20lorem sumlorem sum2016-01-20lorem sumlorem sum2016-04-07 17:52:26lorem sumlorem sum2016-01-20lorem sum23:35:00
15lorem sumlorem sum2016-01-19lorem sumlorem sum2016-01-19lorem sumlorem sum2016-04-08 00:46:37lorem sumlorem sum2016-01-19lorem sum04:10:00
16lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-04-08 01:40:00lorem sumlorem sum2016-01-30lorem sum00:30:00
17lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-04-08 01:57:10lorem sumlorem sum2016-01-30lorem sum14:50:00
18lorem sumlorem sum2016-01-31lorem sumlorem sum2016-01-31lorem sumlorem sum2016-04-08 02:11:56lorem sumlorem sum2016-01-31lorem sum03:00:00
19lorem sumlorem sum2016-02-01lorem sumlorem sum2016-02-03lorem sumlorem sum2016-04-08 06:45:30lorem sumlorem sum2016-02-01lorem sum12:45:00
20lorem sumlorem sum2016-02-03lorem sumlorem sum2016-02-03lorem sumlorem sum2016-04-08 10:11:47lorem sumlorem sum2016-02-03lorem sum22:05:00
Sheet1 (3)
 
Sort by date Sort by votes
No worry, but there is a difference between a Range & an Excel Table

Overview of Excel tables - Microsoft Support

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).
support.microsoft.com support.microsoft.com

Dave
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
dmt32 said:
No worry, but there is a difference between a Range & an Excel Table

Overview of Excel tables - Microsoft Support

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).
support.microsoft.com support.microsoft.com

Dave
Click to expand...
sorry mate, its been a long hectic weekend anyway just tested your code using same data under TABLE format still not working mate I'll include the same workbook, sheet and table where i tested both code. the former code works whether its table or range... I respect all members here both newbie and legends and would never hesitate extending credit and thanks to whom its due.... for whatever reason your code might be working on your side but not mine. but I always make sure to test code given by members to see if its the code I'm looking for and will always be thankful at the receiving end.... :)

dmt32.xlsx
ABCDEFGHIJKLMN
1col 1col 2date enrolledcol 3col 6Date Exitedcol 8col 10DATE Returnedcol 12col 13DaTe Encodedcol 15time Encoded
2lorem sumlorem sum2016-01-14lorem sumlorem sum2016-01-14lorem sumlorem sum2016-01-14lorem sumlorem sum2016-01-14lorem sum10:30:00
3lorem sumlorem sum2015-12-31lorem sumlorem sum2015-12-31lorem sumlorem sum2015-12-31lorem sumlorem sum2015-12-31lorem sum13:15:00
4lorem sumlorem sum2016-01-03lorem sumlorem sum2016-01-03lorem sumlorem sum2016-01-03lorem sumlorem sum2016-01-03lorem sum7:50:00
5lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sum11:30:00
6lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sumlorem sum2016-01-06lorem sum19:00:00
7lorem sumlorem sum2016-01-08lorem sumlorem sum2016-01-08lorem sumlorem sum2016-01-08lorem sumlorem sum2016-01-08lorem sum14:20:00
8lorem sumlorem sum2016-01-09lorem sumlorem sum2016-01-09lorem sumlorem sum2016-01-09lorem sumlorem sum2016-01-09lorem sum19:50:00
9lorem sumlorem sum2016-01-15lorem sumlorem sum2016-01-15lorem sumlorem sum2016-01-15lorem sumlorem sum2016-01-15lorem sum18:40:00
10lorem sumlorem sum2016-01-16lorem sumlorem sum2016-01-16lorem sumlorem sum2016-01-16lorem sumlorem sum2016-01-16lorem sum21:55:00
11lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sum23:10:00
12lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sum23:50:00
13lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sumlorem sum2016-01-17lorem sum9:45:00
14lorem sumlorem sum2016-01-20lorem sumlorem sum2016-01-20lorem sumlorem sum2016-01-20lorem sumlorem sum2016-01-20lorem sum23:35:00
15lorem sumlorem sum2016-01-19lorem sumlorem sum2016-01-19lorem sumlorem sum2016-01-19lorem sumlorem sum2016-01-19lorem sum4:10:00
16lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sum0:30:00
17lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sumlorem sum2016-01-30lorem sum14:50:00
18lorem sumlorem sum2016-01-31lorem sumlorem sum2016-01-31lorem sumlorem sum2016-01-31lorem sumlorem sum2016-01-31lorem sum3:00:00
19lorem sumlorem sum2016-02-01lorem sumlorem sum2016-02-01lorem sumlorem sum2016-02-01lorem sumlorem sum2016-02-01lorem sum12:45:00
20lorem sumlorem sum2016-02-03lorem sumlorem sum2016-02-03lorem sumlorem sum2016-02-03lorem sumlorem sum2016-02-03lorem sum22:05:00
ewan


Rich (BB code): 
Sub HanaFin()
    ColSource = Sheets("ewan").Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To ColSource
        If UCase(Cells(1, i).Value) Like "*DATE*" Then
            Debug.Print "Search value found at Column: " & i
            Last_Char00 = Split(Cells(2, i).Address, "$")(1)
            Columns(Last_Char00 & ":" & Last_Char00).TextToColumns Destination:=Range(Last_Char00 & 1), DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
            Columns(Last_Char00 & ":" & Last_Char00).NumberFormat = "YYYY/MM/DD"
        End If
    Next i
End Sub

Sub HanaFinII()
    Dim objTable    As ListObject
    Dim c           As Long
    Dim HeaderArr   As Variant, Header As Variant
    'CHANGE sheet name as required
    Set objTable = Worksheets("ewan").ListObjects(1)
    HeaderArr = objTable.HeaderRowRange.Value
    c = 1
    For Each Header In HeaderArr
        If UCase(Header) Like "*DATE*" Then objTable.DataBodyRange.Columns(c).NumberFormat = "dd/mm/yyyy"
    c = c + 1
    Next Header
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
138
SFCChase
SFCChase
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
104
vancher19
V
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
526
sharky12345
sharky12345

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top