• 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 Chihiro Sir & Marcl Sir,

thanks for great concepts..

Spec of My Office's PC --Amd Athlon 64x2 Dual Core- 2.19 GHZ... 3GB Ram---32 bit Win. O/s.....Can not possible to upgrade....

I really love Access...I can start from .."Start"..but for just convert "." to "/"...??

SQL...its far away & really want to learn....

Okay....can you modify your code to Just find in first 3 or 4 rows for "have data like "##.##.####" or "##.##.##"..." & just process on only those whole columns only...?

so memory issue can be omitted due to code work on limited criteria..

Sorry Chihiro sir....really..your previous instructions...out of my mind ..
I will be remember next time...really sorry..

& Hope some way there...(Just process those Columns )
Regards,

Chirag Raval
 
You already have the base logic.

Instead of using .UsedRange, specify the row you want to check. Then use the column that match condition to fill array.

If after altering code, it does not produce result you expected, post your modified code and I'll help you along.
 
Dear Sir,

Search on Google for code...process on only 2nd row if found "##.##.####" then process on entire that cell's column ...not found proper solutions..

Please help ..

Regards,
Chirag Raval
 
You don't need Google search. Base logic and code is provided in many variations within this thread.

Follow each code step by step (using break points, Locals Window, Watch Window etc), to understand what it does. Then modify code to suite your need.

For an example, my code puts entire used range into array at first step.

Now, you want to check for specific row into array. Look it up and you'd find plenty of examples.

Then, you should remove outer loop for looping through row index in array, since array is now single row array. So on so forth.

Check by running code on small sample to make it easier for you to understand what it's doing.
 
Dear Sir,

I study your Code...(till working success on 2 or 4 digits year-but on small data)
feel very had to understand ...just "myArr" used many times...
I can not set in mind...can not handle..it...

as my current level for understand this, looks impossible to modify..

please help..

Regards,
Chirag Raval
 
Dear All,

What a limitation I feel...regarding my little knowledge...
though Expert Doctors (Code-Doctors)...motivate me....I can not do little
as per instructions them...i feel so sad..i can not understand which factor
stop to just test?? where are gone my confidence to do it??

really feel sorry..

Regards,

Chirag Raval
 
Dear Sir,

I already obey Mr p45Cal's Post no 771 for search "[0-9]" style instead of word "date" ...

I test it & already work on only Word "Date" but can not Merge/Fusion this search in Mr Chihiro's Array code...because I my level of understanding feel low for Mr Chihir's code ..

please understand..

Regards,

Chirag Raval
 
Try (acts on the active sheet):
Code: 
Sub blah2()
Set URng = ActiveSheet.UsedRange
Set DataBodyRng = Intersect(URng, URng.Offset(1))
For Each Colm In DataBodyRng.Columns
  IsDateColumn = False
  For rw = 1 To Application.Min(DataBodyRng.Rows.Count, 5)
    If Colm.Cells(rw).Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Or Colm.Cells(rw).Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9]" Then
      IsDateColumn = True
    Else
      IsDateColumn = False
      Exit For
    End If
  Next rw
  If IsDateColumn Then
    Colm.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
    Colm.NumberFormat = "dd/mm/yyyy"  'remove this line if you want to leave dates as your (locale) default.
  End If
Next Colm
End Sub
It tests the first 5 rows (or fewer if the data is shorter than 5 rows) of data (excludes the header from testing) of each column; if any one of the values does not match the date pattern then the whole column is not converted.
 
Last edited:
Dear P45Cal Sir,

Thank you very much for your efforts
I am now home office closed.
Tomorrow I will revert after check

Thanks to you all for effort for me..

Regards,

Chirag Raval
 
Dear Sir,

I declare following variable in your code in general module of personnel.xlsb

Dim URng As range
Dim DataBodyRng As range
Dim colm As range
Dim IsDateColumn As Boolean
Dim rw As Integer

& its work like a charm...Magic...As Desired...Amazing. Fast...

Thank you very much for effort to give universal solution forever...to me and also who face this type of issues...

work on desired columns only not whole used range...that's great...

again thanks to you all for help me ...& hope its very useful for global users ..

DearChihiro sir,

Thank you to motivate me towards this point...

again thanks to all experts who participate to resolve this thread...your effort
always help...forever..

Regards,
Chirag Raval
 
Dear Sir @Chihiro

Can you little help for your magical code
To search in first few rows & process on entire column..?

Actually ifeel that i can not leave your charming array code
Stay incomplete its really have something that also fulfilling
Precisely cover columns formatting as real date that excel recognise
& its nothing leave behind doubt ..surity is real power in your this code..
Hope little modification in your powerfulpowerful code construct..

Please help to complete your code..

Regards,

Chirag Raval
 
I'm working on urgent project that just landed on my lap today. I probably won't have time to look at your request till Friday or later.
 
FYI - Unless you are storing large array of integer, there really is very little to no benefit to declaring variable as integer. I'd recommend using Long as default declaration.
 
As a reminder, faster ways are Excel inner features
like Replace or TextToColumns and at beginner level,
just using the Macro Recorder !

Warming a couple of neurons, it is very not difficult to find
specific columns by their names, isn't it ?‼

But best of all is not to create an after procedure in Excel
but to directly mod the export in SAP to get a correct file …
​
 
Dear Sir Mark L, Sir P45Cal & Sir Chihiro,

Many thanks to throw Some light on matter.
I will change rw as long...
& try to record macro as text to column

Actually after change Dot to Shlash.
It is never neglatable to force excel to recognize
Those column's data as date.. & we can do it through Vba
Its very important concept to learn from this forum & from all
Expert's supports I really appreciate..

I surly practice on your valuable hints.
& also wait for Mr. Chihiro to get updated code as it work only
Only Required columscolums to overcome limitations
Of lake RAM memory.....

Regards

Chirag Raval
 
Here, try this. This assumes that header row is always at row 1, and that data begins on row 2.

Code: 
Sub Demo()
Dim myArr, x, dColArr
Dim i As Long, j As Long, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row
myArr = ActiveSheet.UsedRange.Resize(2).Value

For j = 1 To UBound(myArr, 2)
    If myArr(2, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
        dColArr = Range(Cells(2, j), Cells(lRow, j)).Value
        For i = 1 To UBound(dColArr)
            x = Split(dColArr(i, 1), ".")
            dColArr(i, 1) = DateSerial(x(2), x(1), x(0))
        Next
        Cells(2, j).Resize(lRow) = dColArr
        Columns(j).NumberFormat = "dd/mm/yyyy"
    ElseIf myArr(2, j) Like "[0-9][0-9].[0-9][0-9].[0-9][0-9]" Then
        dColArr = Range(Cells(2, j), Cells(lRow, j)).Value
        For i = 1 To UBound(dColArr)
            x = Split(dColArr(i, 1), ".")
            dColArr(i, 1) = DateSerial(x(2) + 2000, x(1), x(0))
        Next
        Cells(2, j).Resize(lRow) = dColArr
        Columns(j).NumberFormat = "dd/mm/yyyy"
    End If
Next

End Sub
 
Dear Sir @Chihiro,

Many Thanks ...Amazing...Powerful..Magical...as desired, its rocking on only required columns ..

But at end of every date columns "#N/A" error print...

every thing is fine except above little error....

Regards,
Chirag Raval
 
Oh right.
Change all instance of below line
Code: 
Cells(2, j).Resize(lRow) = dColArr

to (lRow - 1) or Ubound(dColArr).

So...
Code: 
Cells(2, j).Resize(UBound(dColArr)) = dColArr

Or
Code: 
Cells(2, j).Resize(lRow - 1) = dColArr
 
Dear Sir @Chihiro,

I used your first line.....

Great Its Rocking...Magical.. Charming..Amazing..many thanks...now this thread meet many solutions...

Thanks to you all who support to resolve this issue ...& also provide huge knowledge of various aspect regarding handling date type data..

many thanks again to all for give their contributions & their expertise to resolve this thread..

Regards,

Chirag Raval
 
Dear Sir @Chihiro

Though , your modified code process on only that columns that have "##/" style date but ..there are till face error on below line of code

"dColArr(i, 1) = DateSerial(x(2), x(1), x(0))'

Error no 9
Subscription out of range

please help..

Regards,
Chirag Raval
 
Dear Sir @Marc L

Its very difficult for me.... but..
I put below before run any above converter code
"Application.Union(Columns("B"), Columns("l")).NumberFormat = ""DD/MM/YYYY"

& at last I put force excel to recognise those columns as date
Code: 
FORCE TO EXCEL RECOGNISE DATE AS DATE
Columns("B").Select
selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
Columns("l").Select
selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)

but seems I must indirectly...or directly referencing columns in my code as Hard Coded which have date ...

Hope some focus

Regards,
Chirag Raval
 
You must log in or register to reply here.
Back
Top