Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: A Painfull String operation taking ages to complete - Please help

  1. #1
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A Painfull String operation taking ages to complete - Please help

    Hi Forum - Salutations to the excel masters, MVPs.

    Looking to seek your help with something giving me pain from a long time. Very often I need to append apostrophe (') character to a column containing 700,000+ rows. So i have written this simple macro to do the job. But the problem is it takes lot of time (2-5 minutes).

    Therefore I wanted to get your opinion if there is an efficient and faster way to do this. Maybe instead of cell by cell operation, is there a way to do a range operation - Range = "'" & Range & "'".

    Sub AddApostrophie()
    Dim cell As Range

    For Each cell In Selection
    cell.Value = "''" & cell.Text & "'"
    Next cell

    End Sub

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,782
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Look at the Range.Replace Method in VBA Help.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,782
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Quote Originally Posted by JoeMo View Post
    Look at the Range.Replace Method in VBA Help.
    On second thought, that won't work. I would put the selection into a variant array (call it V), V =Selection.Value, then loop through the items in the array, change them and then go back (Selection.Value = V). The item by item changes are all done in memory which is very fast compared to what you are currently doing.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Negative. Range.replace will replace an existing character/string with new character/string. In my case there is nothing to replace. Need to concatenate apostrophe "'" to the start and end of text.

  5. #5
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    JoeMo, Thank you soooooooooo much. did it with Variant array. Time reduced from 49 seconds to 2 seconds.

    Sub AddApostrophie2()
    StartTime = Timer
    Dim cell As Range
    Dim v As Variant

    v = Selection.Value

    Dim i As Long

    For i = 1 To UBound(v)

    v(i, 1) = "''" & v(i, 1) & "'"


    Next i

    Selection.Value = v
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


    End Sub

  6. #6
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Quote Originally Posted by JoeMo View Post
    On second thought, that won't work. I would put the selection into a variant array (call it V), V =Selection.Value, then loop through the items in the array, change them and then go back (Selection.Value = V). The item by item changes are all done in memory which is very fast compared to what you are currently doing.
    JoeMo- Got into a situation when using the Variant array method. My earlier method, which was a cell by cell operation, was able to handle dates properly. Because I could use the cell.text property which returns formatted date rather than serial number. But with when I use v(variant array) = selection.text it just doesn't work. Any thoughts how i can overcome this?

  7. #7
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Quote Originally Posted by gagan_blue View Post
    Negative. Range.replace will replace an existing character/string with new character/string. In my case there is nothing to replace. Need to concatenate apostrophe "'" to the start and end of text.

    If i use v = selection.value
    typename(v) returns variant

    but if i use v = selection.text
    typename(v) returns string.

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    For the code you gave in post #5, what exactly is it doing incorrectly with dates? Examples?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Quote Originally Posted by gagan_blue View Post
    JoeMo- Got into a situation when using the Variant array method. My earlier method, which was a cell by cell operation, was able to handle dates properly. Because I could use the cell.text property which returns formatted date rather than serial number. But with when I use v(variant array) = selection.text it just doesn't work. Any thoughts how i can overcome this?
    Assuming you will always be specifying the range by selecting it (we can have VB do that for you if you tell us the starting cell address), will your selection always contain only formatted dates? Also, what is the format of those dates?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Sep 2014
    Location
    Bangalore, India
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A Painfull String operation taking ages to complete - Please help

    Quote Originally Posted by Peter_SSs View Post
    For the code you gave in post #5, what exactly is it doing incorrectly with dates? Examples?
    Hi Peter,

    My objective is to concatenate apostrophe ("'" ) to the beginning and end of a large number of cells (mix of numbers, dates, text). So I am transferring entire range to a variant array. But while doing that, dates are stored as serial number in the variant array. I need to store the dates in their original format eg "15-Apr-1986". I was able to do that with my earlier macro (mentioned in my opening post) where I was using cell.text property. But that macro was very slow so i dont want to use that.

    So bascially why cant i use v(variant array) = selection.text

    Sub AddApostrophie()

    StartTime = Timer

    Dim cell As Range
    Dim v As Variant

    v = Selection.Value


    Dim i As Long

    For j = 1 To UBound(v, 2)

    For i = 1 To UBound(v, 1)

    v(i, j) = "''" & v(i, j) & "'"

    Next i

    Next j

    Selection.Value = v

    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "Time taken" & SecondsElapsed & " seconds", vbInformation


    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •