+ Reply to Thread
Results 1 to 5 of 5

Working-script modification

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Working-script modification

    I have been using the following script which keeps the first seven word of the string and replaces the rest of it with ten dots, for example:

    these first seven words will be kept and the rest of this sentence will be replaced with ten dots
    these first seven words will be kept ..........

    This script, though, halts once the string is less than seven (which can be modified as needed), thus I need:

    1. to have the script modified to ignore and leave any string that has a number of words less than the required number (which is seven for now) without changing it or adding any dots to it.

    2. to have the new modified script to be modified again as a second script to work from the end of the string, so it keeps the last seven words and replaces the rest of the string to the beginning with ten dots, accordingly our previous example will be:

    .......... sentence will be replaced with ten dots

    Sub KeepFirstSeven()
    Dim sn, tempArr, tempArr2
    Application.ScreenUpdating = False
    sn = ActiveSheet.Range("X1", Range("X" & Rows.count).End(xlUp))
    For i = 1 To UBound(sn)
        If sn(i, 1) <> vbNullString Then
            tempArr = Split(sn(i, 1), " ")
            For j = 0 To 6
                tempArr2 = tempArr2 & tempArr(j) & " "
            Next
            sn(i, 1) = tempArr2 & ".........."
        End If
        tempArr2 = vbNullString
    Next
    ActiveSheet.Range("Y1").Resize(UBound(sn)) = sn
    Application.ScreenUpdating = True
    End Sub
    Can I get some precious help with these two requests?

    Many thanks in advance ..
    T.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,527

    Re: Working-script modification

    Hi Terry (and fellow Sydneyite),

    Try these macros:

    Option Explicit
    Sub KeepFirstSeven()
        
        Dim sn, tempArr, tempArr2
        Dim i As Long, j As Long
        
        Application.ScreenUpdating = False
        
        sn = ActiveSheet.Range("X1", Range("X" & Rows.Count).End(xlUp))
        
        For i = 1 To UBound(sn)
            If sn(i, 1) <> vbNullString Then
                tempArr = Split(sn(i, 1), " ")
                If UBound(tempArr) >= 6 Then 'There must be a minimum of six spaces for the following code to work
                    For j = 0 To 6
                        tempArr2 = tempArr2 & tempArr(j) & " "
                    Next j
                    sn(i, 1) = tempArr2 & ".........."
                End If
            End If
            If UBound(tempArr) >= 6 Then
                tempArr2 = vbNullString
            End If
        Next i
        
        ActiveSheet.Range("Y1").Resize(UBound(sn)) = sn
        
        Application.ScreenUpdating = True
    
    End Sub
    Sub KeepLastSeven()
        
        Dim sn, tempArr, tempArr2
        Dim i As Long, j As Long
        
        Application.ScreenUpdating = False
        
        sn = ActiveSheet.Range("X1", Range("X" & Rows.Count).End(xlUp))
        
        For i = 1 To UBound(sn)
            If sn(i, 1) <> vbNullString Then
                tempArr = Split(sn(i, 1), " ")
                If UBound(tempArr) >= 6 Then 'There must be a minimum of six spaces for the following code to work
                    For j = UBound(tempArr) To LBound(tempArr) Step -1
                        If j = UBound(tempArr) - 6 - 1 Then 'Account for zero based array
                            Exit For
                        Else
                            tempArr2 = tempArr(j) & " " & tempArr2
                        End If
                    Next j
                    sn(i, 1) = ".........." & tempArr2
                End If
            End If
            If UBound(tempArr) >= 6 Then
                tempArr2 = vbNullString
            End If
        Next i
        
        ActiveSheet.Range("Y1").Resize(UBound(sn)) = sn
        
        Application.ScreenUpdating = True
    
    End Sub
    Note there must be at least two entries in Col. X or else the code will error out due to the upper limit of the sn array needing to be 1 or higher.

    Thanks,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Working-script modification

    Hi Robert,
    Just got the chance to run your masterpiece codes .. they smoothly did the job as exactly as desired. Now I can go ahead with my work : )
    I really don't know how to thank you mate, but please accept my deep respect and gratitude.
    My best regards,
    T.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,527

    Re: Working-script modification

    Thanks Terry

    Thanks also for the kind words both here and for the rep you gave me. I'm glad I was able to provide you with a satisfactory solution

  5. #5
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Working-script modification

    That was more than a satisfactory solution Robert. You do have a great value added to this world .. always ahead ..

    T.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Script not working as Expected
    By Blackhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2016, 09:01 PM
  2. VBA Script not working - Please help
    By Blackhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2016, 08:18 PM
  3. Ribbon modification not working on Mac
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2016, 05:51 AM
  4. VB script not working
    By ssschaar in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2014, 03:28 PM
  5. Help with Conditional script modification
    By Rabbitstew in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2013, 03:36 PM
  6. This script isn't working, why not?
    By ledworld in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2013, 06:04 PM
  7. [SOLVED] my if-then-else script is not working
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2013, 02:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1