1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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 “/”

Discussion in 'VBA Macros' started by Chirag R Raval, Aug 9, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    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.
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    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.
    Chirag R Raval likes this.
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  8. p45cal

    p45cal Well-Known Member

    Messages:
    841
    Try (acts on the active sheet):
    Code (vb):
    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: Sep 5, 2017
    Chirag R Raval likes this.
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    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.
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    Dear Sir @Chihiro

    Best Of Luck ..God Bless You & my best wishes to success in your project..
    I will be wait....

    Regards,
    Chirag Raval
    Tim Hanson likes this.
  14. p45cal

    p45cal Well-Known Member

    Messages:
    841
    Make it
    Dim rw as Long
    since you have 60k+ rows, an integer won't be big enough.

    Not sure where the "nothing leave behind doubt" is in blah2.
    Chirag R Raval and NARAYANK991 like this.
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    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.
    Chirag R Raval likes this.
  16. Marc L

    Marc L Excel Ninja

    Messages:
    3,175
    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 …
    Chirag R Raval likes this.
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  18. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    Here, try this. This assumes that header row is always at row 1, and that data begins on row 2.

    Code (vb):
    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
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  20. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,688
    Oh right.
    Change all instance of below line
    Code (vb):
    Cells(2, j).Resize(lRow) = dColArr
    to (lRow - 1) or Ubound(dColArr).

    So...
    Code (vb):
    Cells(2, j).Resize(UBound(dColArr)) = dColArr
    Or
    Code (vb):
    Cells(2, j).Resize(lRow - 1) = dColArr
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  22. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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
  23. Marc L

    Marc L Excel Ninja

    Messages:
    3,175
    Hi !

    Data just not suit the code !
    As you can easily check within Locals VBE window …
  24. Chirag R Raval

    Chirag R Raval Member

    Messages:
    339
    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 (vb):

    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
  25. Marc L

    Marc L Excel Ninja

    Messages:
    3,175


    Again, just check variables values of the codeline where error occurs …​

Share This Page