• 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.

Search Columns which have data “##.##.####” & convert all “.” To “/”

Dear p45cal.

its work if column have word "Date" but some time Dt. or can be another..
please help..

(2) Mr. Chihiro.....its require Array on "If UBound(x) = 2 Then"..

please help..

regards,
Chirag Raval..
 
Did you declare x as variant?

However, as I mentioned it's safer to use pattern checking. Modified code and added Option Explicit for your benefit.
Code:
Option Explicit
Sub Demo()
Dim myArr, x
Dim i As Long, j As Long
Dim colIndex As String
myArr = Sheet1.UsedRange.Value

For i = 1 To UBound(myArr)
    For j = 1 To UBound(myArr, 2)
        If myArr(i, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
            x = Split(myArr(i, j), ".")
            If InStr(1, colIndex, j) = 0 Then colIndex = IIf(Len(colIndex) = 0, j, colIndex & "," & j)
            myArr(i, j) = DateSerial(x(2), x(1), x(0))
        End If
    Next
Next

x = Split(colIndex, ",")
With Sheet1
    .UsedRange = myArr
    For i = 0 To UBound(x)
        .Columns(CLng(x(i))).NumberFormat = "dd/mm/yyyy"
    Next
End With
End Sub
 
Dear Sir,

I update variable myArr as variant &
x as Variant...as per below ..

Dim myArr As Variant
Dim x As Variant
Dim i As Long, j As Long
Dim colIndex As String

But...

"Type mismatch"...displayed on
" If myArr(i, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then"

Regards,
Chirag Raval
 
its work if column have word "Date" but some time Dt. or can be another..
please help..
List ALL of the possibilities that the header could contain that might signify it's a date column. That is, complete this list (it doesn't matter about upper/lower case at the moment):
Date
Dt.
?
?
?
 
Dear Sirs,

That's my big 3 mistakes I want to say sorry to you all...

(1) first i want to say Sorry Mr. Shrivallabha...I can not check your 2nd effort...
but after check.... its works....many thanks for your efforts...

as per Mr chihiro says...

"shrivallabha's code in post #14 should work regardless of system date format (you just have to apply formatting later if dd/mm/yyyy is desired).

I test that....run efficiently...as desired...many thanks for your effort....

(2) Sorry Mr Narayan....your code also work....efficiently...but in rush (shower) of codes...I forget to check it...many thanks for your effort...

(3) Mr Chihiro....sorry...previously I test your code on another sheet...that's my mistake...my sheet name differ...so type mismatch displayed...
I download you file & test that code on another sheet after match sheet name...work amazingly....many thanks for your effort....

Oh ....there are world found...3 codes ...available there to resolve this type of issue...your all's effort is precious...& world always be thank full for that...mainly I...... always respect to experts there...

this thread now meet gracefully...3 solutions ...

Regards,

Chirag Raval
 
Dear AliGW,

Oh ..your catching "may be data pre-formated other then date or number" is great..& core factor.that's very helpful to resolve this thread...

your effort always be appreciated ..

thanks again..

Regards,

Chirag Raval
 
Dear All,

There are seem mysterious feature about how excel recognise automatically
Date type data..? its seems event base process or something else ?

some time can not recognise ....also mysterious..

Regards,
Chirag Raval
 
Dear All,

Thanks ..to all above 4 solutions ...but now I received
file as format "##/##/##" (year become 2 digits)

can I modify this code if found data as "##/##/##" to "##/##/##" ???

(just dd/mm/yy)..??

I try to give OR condition in
If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then,,

as

If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" OR "[0-9][0-9].[0-9][0-9].[0-9][0-9]"Then,,..

but whole line become RED & stop..

can I modify above all 4 solutions ?

Help Will be appreciated ...


Thanks again for all your support..

Regards,
Chirag Raval
 
In a private conversation, you asked me to focus on this thread.
If you were to supply an answer to my question in msg#31 we might get somewhere.

Supplying a file as Chihiro suggests would be a good idea too.
 
Following should be changed:
Code:
If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
to
Code:
If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" or  rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9]" Then
Notice the portion after "or" which is missing in your code. VBA doesn't compile the way we would say "literally" ;)
 
Dear Chihiro Sir & P45CAL Sir,

Attached As Per Your Requirement..

Also Kindly Note Mr . Shrivallabha..its successfully covert but ...Green Corner on every cell.. when hovering & click on green...."TeXt Date is 2 digits Year"...means we have to click on cell to convert it in readl date..

Regards,
Chirag Raval
 

Attachments

  • test of order status single sheet.xlsx
    320.3 KB · Views: 13
Last edited:
Dear Chihiro Sir & P45CAL Sir,

Attached As Per Your Requirement..

Also Kindly Note Mr . Shrivallabha..its successfully covert but ...Green Corner on every cell.. when hovering & click on green...."TeXt Date is 2 digits Year"...means we have to click on cell to convert it in readl date..

Regards,
Chirag Raval
I showed the correct syntax for the line you mentioned wasn't working.

Following shall work in your case.
Code:
Public Sub ReplaceDotWithSlash()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
    If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
        rng.Value = DateValue(Mid(rng.Value, 4, 2) & "/" & Mid(rng.Value, 1, 2) & "/" & Mid(rng.Value, 7, 4))
    ElseIf rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9]" Then
        rng.Value = DateValue(Mid(rng.Value, 4, 2) & "/" & Mid(rng.Value, 1, 2) & "/" & Mid(rng.Value, 7, 2))
  End If
Next
End Sub
 
Here's updated code, from my post.
Code:
Option Explicit
Sub Demo()
Dim myArr, x
Dim i As Long, j As Long
Dim colIndex As String
myArr = Sheet1.UsedRange.Value

For i = 1 To UBound(myArr)
    For j = 1 To UBound(myArr, 2)
        If myArr(i, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
            x = Split(myArr(i, j), ".")
            If InStr(1, colIndex, j) = 0 Then colIndex = IIf(Len(colIndex) = 0, j, colIndex & "," & j)
            myArr(i, j) = DateSerial(x(2), x(1), x(0))
        ElseIf myArr(i, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9]" Then
            x = Split(myArr(i, j), ".")
            If InStr(1, colIndex, j) = 0 Then colIndex = IIf(Len(colIndex) = 0, j, colIndex & "," & j)
            myArr(i, j) = DateSerial(x(2) + 2000, x(1), x(0))
        End If
    Next
Next

x = Split(colIndex, ",")
With Sheet1
    .UsedRange = myArr
    For i = 0 To UBound(x)
        .Columns(CLng(x(i))).NumberFormat = "dd/mm/yyyy"
    Next
End With
End Sub
 
its work if column have word "Date" but some time Dt. or can be another..
please help..
In the file you've attached all date column headers have the full word Date in them so the snippet of code in msg#22 works on your most recent sheet. It doesn't matter whether there are 2 digit years or 4 digit years.
If you want it to work also on date columns with headers with other things in, then you need to list what those things are… here, as already asked for in msg#31.
:mad:
 
Last edited:
DearSirs,

Thanks to both of you experts...

P45cal sir...yes your code worked ...its natural that date column
Has header wording is limited & we can cover it is in our search for that..
..& process only those columns ..so its ..fast ..may be its better then looping all
The cells..but though ....Its depend on column header that increase risk factor..that may be header
Header have something other then our assumption...
But other positive side of this code construct is its fastness & done prices only required columns..
& any one can not avoide its fastness...& must be put as 2nd options .. Thanks again.

Chihiro sir....
I just try as testing. Your updated code in my office...
Its stuck on " if myarry ( I ,j)......"Type mismatch"....

I will be recheck it..& then revert soon...

Thanks again you all for your valuable efforts...

Regards

Chirag Raval
.
 
There's misspelling.

If you copied my code exactly, it would be...
"If myArr(i, j)..."

The code was tested on your sample file and ran without issue.
 
Dear Chihiro sir.

That's just for mentioning where problem occurring...spelling not matter..


Yes ....you are right ..perfect...

I previously tested it on General module & another sheet name ..so I face "Type Mismatch"...

Now I change all "Sheet1" to "Active sheet'..in general module & its work like a charm.....magic....amazing...

many thanks you all expert there...I found universal code now..to
convert successfully any "." type date as "/" ..

again thank....

Regards,
Chirag Raval
 
Dear sir P45Cal..

can modify your code that it search only first 5 lines for "[0-9]......instead of like " date" type words & if first found [0-9] , that whole column,s "." convert in "/" and next...

also precisely force excel to recognise that column as whole column's data type is Date

help will be appreciate...

thanks again for your effort for this..
Regards,

Chirag Raval
 
Dear Sir Chihiro

Run Time Error 7 ...Out Of memory ......displayed on

myArr = ActiveSheet.UsedRange.Value

when try to run this code on about 1100 lines & 66 columns data-base...

Actually I have normally about 60, 000 lines data & it have more then 2 columns have data like "##.##.####" or "##.##.##"...

maybe array can not catch huge database...

request to resolve please

Regards,
Chirag Raval
 
Hi !

Memory issue : shutdown (or restart) computer.
Launch Excel alone

As data came from SAP, it is far logical to ask
for a correct export with an appropriate date format !

As Excel is not a database software, better is to use Access,
at least 50 times faster than Excel to mod data …
 
@Chirag R Raval

As Marc stated, it's memory issue and not directly related to code itself.

What's your computer's spec? And is your Office installation 32 bit or 64 bit?

As a sample, on my machine, I have 16GB of RAM along with 64 bit installation of Office and haven't had issue with memory yet. And I deal with decent sized data on daily basis (anywhere from 100k~few mil rows & 5~15 columns). Using combination of VBA and PowerQuery. Though I've shifted most of it to PQ.

If data is too large for your machine to handle, consider following options.

1. Split data into manageable segments and complete operation in several stages

2. Upgrade your RAM and use 64 bit version of Office

3. Use alternate tools other than Excel. Such as MS Access, MySQL, MS SQL Express etc.

P.S. Please don't post in personal profile asking for help. As I have previously stated, I don't check forums on most weekends and I get notification from existing thread. If you need my attention, just post on existing thread and/or use @ sign before my user name.
 
Back
Top