Consulting

Results 1 to 3 of 3

Thread: Duplicating/modifying text to prevent endless retyping

  1. #1

    Duplicating/modifying text to prevent endless retyping

    Hello,

    Currently my group performs a daily database search with specific terms only,(database limitations require this) causing us to have to retype the same information over and over each day. What I'm hoping for is a macro that can do the following:

    Duplicate and modify highlighted text as follows:

    00001 ---> "0001", "00001B", "00001C", "00001D", "00001E", "00001F", "00001G"

    In our case the quotations, commas, spacing, and letters are specific and exactly what is needed for our search. Often times we will have up to 2 dozen of these batch numbers, so if the macro could be used on an entire block of numbers to generate one long list that can be copy pasted into our search bar that would be best; i.e.

    00001
    00002
    00003
    ....

    Turns into:

    "0001", "00001B", "00001C", "00001D", "00001E", "00001F", "00001G", "0002", "00002B", "00002C", "00002D", "00002E", "00002F", "00002G", "0003", "00003B", "00003C", "00003D", "00003E", "00003F", "00003G", ...

    I've tried searching/learning for this sort of Macro but am clearly out of my element.

    Thank you!
    Steve

  2. #2
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    103
    Location
    If that's exactly how it's done on the document and
    as long as it's A-G and that never changes, it should be pretty straight forward.

    Check this out (someone more talented could probably do it more elegantly):
    Sub process()
        Dim myPar As Paragraph
        Dim myStr As String
        Dim myStr2 As String
        For Each myPar In ActiveDocument.Paragraphs
            myPar.Range.Select
            Selection.End = Selection.End - 1
            If Selection Like "#####" Then
                myStr = Selection
                If myStr <> "" Then
                    myStr = Chr(34) & myStr & Chr(34) & ", " & Chr(34) & myStr & "A" & Chr(34) & ", " & Chr(34) & myStr & "B" & Chr(34) & ", " & Chr(34) & myStr & "C" & Chr(34) & ", " _
                    & Chr(34) & myStr & "D" & Chr(34) & ", " & Chr(34) & myStr & "E" & Chr(34) & ", " & Chr(34) & myStr & "F" & Chr(34) & ", " & Chr(34) & myStr & "G" & Chr(34)
                    If myStr2 = "" Then
                        myStr2 = myStr
                    Else
                        myStr2 = myStr2 & ", " & myStr
                    End If
                End If
            End If
        Next
        MsgBox myStr2
        Selection.WholeStory
        Selection = myStr2
    End Sub
    
    Last edited by Mavila; 11-15-2019 at 01:11 PM.

  3. #3
    Mavila, elegant or not this worked like a charm. Thank you for the hours of my life you have given me back.

Posting Permissions

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