Recorded macro does not execute on full range?

[danieldk]

Hi,

I have been struggling with this error for weeks.

I have an Excel document where I copy a generated data workbook into a new workbook where i paste it into a dedicated DATA tab.

From here I have recorded a macro that selects column B and replaces every instance of "." with "/". This is to change dd.mm.yyyy to dd/mm/yyyy. I have tried simply changing the format but it does not work. The goal of doing this is to have Excel recognize the cell value as a date and compiling the dates when i put a filter on the header. Somehow when i do this manually, or even when recording the macro, it works perfectly, but when i run the macro, it seems to only replace half (or less) of the cells, and it changes cell values to date that have not yet been such as 06/12/2022.

I have tried debugging, I have compared the lines in VBA to other examples found on google but i cant find what the error is.

Can anyone help me?


VBA also posted beneath
Sub UPDATE_DATA()
'
' UPDATE_DATA Macro
'

'
Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[FormR]

Hi, it's probably something to do with the way VBA is expecting dates to be in the US format of mm/dd/yyyy - you could try this alternative method of converting the text dates to proper dates.

Sub UPDATE_DATA()
'
' UPDATE_DATA Macro
'
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

 

[danieldk]

You sir, are an Excel wizard! It works!

Your title of MrExcel MVP is earned :D

 

[danieldk]

It seems i replied to quickly. The date format is now correct in all cells but it still changes the some cells to dates that have not been yet?

 

[FormR]

but it still changes the some cells to dates that have not been yet?

Can you post some before and after examples for where it's going wrong?

 

[danieldk]

It changes dd.mm.yyyy to mm/dd/yyyy if im not wrong. So the code correctly made Excel recognize the values as dates, but also converted the value to a new format. In order to have this work together with the rest of the workbook, it would be a great help if it could remain, dd/mm/yyyy format.

Thanks for you willingness to help, its much appreciated!!

Have taken screenshot of the values that gets changed to December

12.01.2022 becomes 01/12/2022
12.05.2022 becomes 05/12/2022
12.07.2022 becomes 07/12/2022

 

[FormR]

It might be best to do them one by one.

Here's something you can try, it assumes you only have the text dates in column B and that they start on row 2.

Sub UPDATE_DATA()
'
' UPDATE_DATA Macro
'
Dim V As Variant, I As Long, D As Variant
V = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For I = 1 To UBound(V)
    D = Split(V(I, 1), ".")
    V(I, 1) = DateSerial(D(2), D(1), D(0))
Next I
Range("B2").Resize(UBound(V)).Value = V
End Sub

 

[danieldk]

Just tested it, and now everything works!

Thanks for you help!!