jaeremata
New Member
- Joined
- Jan 20, 2021
- Messages
- 24
- Office Version
- 2019
- 2016
- Platform
- Windows
Hi Everyone, I need you help regarding with this code. I just got it also here. I have a macro that will import another excel, all the text covert into data. It works, but when I import another excel for the next month, some date still remain in text. If you see on the screen shot, what I want to happen is to convert text to date. for column J and K
Here's the code:
Sub MyDateConvert()
Dim myFirstCol As String
Dim myAllCols As Variant
Dim myLastRow As Long
Dim i As Long
Dim myCol As String
Dim myCell As Range
Application.ScreenUpdating = False
' Specify columns to apply to
myAllCols = Array("J", "K")
' Find last row with data
myLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' Loop through all columns
For i = LBound(myAllCols) To UBound(myAllCols)
myCol = myAllCols(i)
' Format column as text
Columns(myCol).NumberFormat = "YYYY-MM-DD"
' Loop through cells in column, converting the ones that have values to text in "yyyy-mm-dd" format
For Each myCell In Range(Cells(2, myCol), Cells(myLastRow, myCol))
If Len(myCell) > 1 Then myCell = Format(myCell, "YYYY-MM-DD")
Next myCell
Next i
Application.ScreenUpdating = True
End Sub
Here's the code:
Sub MyDateConvert()
Dim myFirstCol As String
Dim myAllCols As Variant
Dim myLastRow As Long
Dim i As Long
Dim myCol As String
Dim myCell As Range
Application.ScreenUpdating = False
' Specify columns to apply to
myAllCols = Array("J", "K")
' Find last row with data
myLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' Loop through all columns
For i = LBound(myAllCols) To UBound(myAllCols)
myCol = myAllCols(i)
' Format column as text
Columns(myCol).NumberFormat = "YYYY-MM-DD"
' Loop through cells in column, converting the ones that have values to text in "yyyy-mm-dd" format
For Each myCell In Range(Cells(2, myCol), Cells(myLastRow, myCol))
If Len(myCell) > 1 Then myCell = Format(myCell, "YYYY-MM-DD")
Next myCell
Next i
Application.ScreenUpdating = True
End Sub