• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA date format not working

As per below image, After running the code some of the cells date not formating as defined. How do I change the code to get "dd-mmm-yyyy" formats

65669
 

Attachments

  • Tally raw data.xlsb
    148.6 KB · Views: 2
Anbuselvam K
Eg those A12:A14-cells values are not 'dates'.
Those 'look' like dates - but those are texts.
One manually possible ...
1) add ' in front of 'mis-date' and press <ENTER>
2) remove ' in front of 'mis-date' and press <ENTER>
3) repeat steps 1 & 2 to needed rows
 
Dear Vletm

Thanks for your valuable reply.

After steps 1 & 2 it has changed 02-Jan-2020 where our expected result is 01-Feb-2020, because of it's format 2/1/2020

The original exported file has 01-02-2020 in A8 after running the code it becomes 01-Feb-2020

After that, I just used offset and retrieve the same date to next blank cells but how it is changed from date format to texts?

Is there any other options to get the same date format to the blanks cells.

Please do the needful.

Thanks in advance.
 
Anbuselvam K
Is there any other options to get the same date format to the blanks cells.
Did You 'copy & paste' those 'mis-date's?
... if use 'copy & paste' then one possible 'do the needful' is to paste only values.
or
try to use =clear(trim(mis_date)) to get more like dates.
 
Dear Vletm

I changed the code for copy-paste as below. now it is working fine.

Range("A" & i).Offset(-1, 0).Copy Range("A" & i)

Thanks for your support.
 
Hi

Some part of the below code I created by macro recording and it is taking a couple of minutes to run the code. Is there any alternative way to reduce the time.

Expecting your valuable reply.

Code:
Option Explicit

Sub Simple_For()
    Dim Startrow As Byte
    Dim LastRow As Long
    Dim i As Long
Application.ScreenUpdating = False

Columns("H:H").Select
    Selection.Replace What:="Kgs", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="ltrs", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="nos", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Range("B11").CurrentRegion.Select

    Selection.Replace What:="", Replacement:="""""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="""""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
    Startrow = 8
    Dim myValue As Date
    
    LastRow = Range("B8").End(xlDown).Row
    For i = Startrow To LastRow
        myValue = Range("A" & i).Value
            If Range("A" & i).Value = "" Then
            Range("A" & i).Offset(-1, 0).Copy Range("A" & i)
            Range("A" & i).NumberFormat = "dd-mmm-yy"
            End If
            Range("A" & i).NumberFormat = "dd-mmm-yy"
    Next i
Application.ScreenUpdating = True

End Sub
 

Attachments

  • Tally raw data1.xlsb
    87.5 KB · Views: 1
Boss,

Manually press Ctrrl+F and replace is working in seconds.

When I added in the code
Kgs = ""
Ltrs = ""
Nos = ""
Blanks = "0"
Then For Loop
it is taking couple of minutes.
 
Anbuselvam K
a) What/where are expected results?

b) case H-column ... search per column
c) with some loop
... name there at least the 1st and the last row - not the whole column

BUT
Why that H-column values are as TEXT?
Why those are not numbers?
Now, more than double work!
1st someone has written those as number & 'kgs' - and - now You'll try to take those away.
 
b) case H-column ... search per column
c) with some loop
... name there at least the 1st and the last row - not the whole column
Okay, Boss, I will fix the rows instead of the whole Column

BUT
Why that H-column values are as TEXT?
Why those are not numbers?
Now, more than double work!
1st someone has written those as number & 'kgs' - and - now You'll try to take those away.
It is been exported from the tally. by default, it gives Kgs, Ltrs and Nos in all values.
 
Back
Top