Consulting

Results 1 to 8 of 8

Thread: Help with a macro

  1. #1
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location

    Help with a macro

    I am trying to make macro to add dashes between all the letters in a word. I want to have names be spelled out with dashes between them. I do not want the name to be automatically upper case. I have a found a macro online that is similar to what I want to do and have attempted to edit it to make it do what want it to do. Every time I edit it, the If statement starting on line 17 throws a break error.


    Sub CapDashNames()
        Dim sTemp As String
        Dim sName As String
        Dim J As Integer
    
    
        sTemp = UCase(Selection.Range.Text)   ' Make all uppercase
        If Len(sTemp) > 1 Then
            sName = ""
            For J = 1 To Len(sTemp) - 1
                ' Add new character to name
                sName = sName & Mid(sTemp, J, 1)
                If Mid(sTemp, J, 1) >= "A" And Mid(sTemp, J, 1) <= "Z" Then
                    ' Add a dash if character was a letter
                    sName = sName & "-"
                Else
                    ' Character added was not a letter
                    If Mid(sName, Len(sName) - 1, 1) = "-" Then
                        ' If there is a dash just before non-letter,
                        ' get rid of it
                        sName = Left(sName, Len(sName) - 2)
                        sName = sName & Mid(sTemp, J, 1)
                    End If
                End If
            Next J
            ' Add final character
            sName = sName & Right(sTemp, 1)
            Selection = sName
        End If
    End Sub

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    I reduced your existing macro to :

    Option Explicit
    
    
    Sub AddDashes1()
        Dim Cell As Range
        Dim sTemp As String
        Dim C As Integer
    
    
        For Each Cell In Selection
            sTemp = ""
            For C = 1 To Len(Cell)
                sTemp = sTemp + Mid(Cell, C, 1) + "-"
            Next
            Cell.Value = Left(sTemp, Len(sTemp) - 1)
        Next
    End Sub
    Select the cell and click the button.
    Last edited by Logit; 10-20-2019 at 04:06 PM.

  3. #3
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location

    Note QUITE right... still

    This is for Word, not Excel. This has it looking for a Cell to edit. I'm working in Word and need to be able to just highlight a word and add dashes between the letter.

    Quote Originally Posted by Logit View Post
    I reduced your existing macro to :

    Option Explicit
    
    
    Sub AddDashes1()
        Dim Cell As Range
        Dim sTemp As String
        Dim C As Integer
    
    
        For Each Cell In Selection
            sTemp = ""
            For C = 1 To Len(Cell)
                sTemp = sTemp + Mid(Cell, C, 1) + "-"
            Next
            Cell.Value = Left(sTemp, Len(sTemp) - 1)
        Next
    End Sub
    Select the cell and click the button.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Admittedly WORD is not my forte. Apologies for not realizing you were working in WORD.

    Having said that, here is a short work around to obtain the results you are seeking :

    Sub CapDashNames()
        Dim sTemp As String
        Dim sName As String
        Dim J As Integer
    
    
    Application.ScreenUpdating = False
    
    
        sTemp = UCase(Selection.Range.Text)   ' Make all uppercase
        If Len(sTemp) > 1 Then
            sName = ""
            For J = 1 To Len(sTemp) - 1
                ' Add new character to name
                sName = sName & Mid(sTemp, J, 1)
                If Mid(sTemp, J, 1) >= "A" And Mid(sTemp, J, 1) <= "Z" Then
                    ' Add a dash if character was a letter
                    sName = sName & "-"
                Else
                    ' Character added was not a letter
                    If Mid(sName, Len(sName) - 1, 1) = "-" Then
                        ' If there is a dash just before non-letter,
                        ' get rid of it
                        sName = Left(sName, Len(sName) - 2)
                        sName = sName & Mid(sTemp, J, 1)
                    End If
                End If
            Next J
            ' Add final character
            sName = sName & Right(sTemp, 1)
            Selection = sName
        End If
        
        Selection = LCase(Selection.Range.Text)
    
    
    Application.ScreenUpdating = True
    
    
    End Sub

  5. #5
    Quote Originally Posted by Kindyr View Post
    I am trying to make macro to add dashes between all the letters in a word. I want to have names be spelled out with dashes between them. I do not want the name to be automatically upper case.
    Try the following. Put the cursor in the word and run the macro

    Sub Macro1()Dim orng As Range
    Dim i As Integer
        Set orng = Selection.Words(1)
        orng.MoveEndWhile Chr(32) & Chr(45), wdBackward
        For i = orng.Characters.Count - 1 To 1 Step -1
            orng.Characters(i).InsertAfter Chr(45)
        Next i
        Set orng = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location
    Oh! This is almost entirely perfect! The next step is to figure out how to make the hyphens non-breaking so they do not wrap at the end of a line. In side of word, I can use Shift-Ctl-HYPHEN to make a non-breaking hyphen. Is there a way to make the hyphen in this macro non-breaking?

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    I think Graham is packed up for the day. Change to:
    orng.Characters(i).InsertAfter Chr(30)
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location
    Thank you Greg, that was perfect. I had thought Chr(30) would return a 30 instead of a non-breaking hyphen. This is exactly what I wanted.

Posting Permissions

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