Need Help with this Macro

rsrasc · #1 09-23-2018, 07:52 AM

Hi all,

I was wondering if the below macro can be adapted so it can be run only with a selected text in a document.

As always, thank you for your assistance and cooperation.

Cheers!

Code:

Sub Macro6()
'
' Macro3 Macro
'
'
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "^p"
        .Replacement.Text = " "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.Font.Bold = wdToggle
    Selection.Font.Bold = wdToggle
    Selection.WholeStory
    Selection.Font.Bold = wdToggle
    Selection.EscapeKey
End Sub

gmaxey · #2 09-23-2018, 11:24 AM

Not sure exactly what you are trying to do, but something like this:

Code:

Sub Macro6()
Dim oRng As Range
Dim oRNg2 As Range
  Selection.Find.ClearFormatting
  Selection.Find.Replacement.ClearFormatting
  Set oRng = Selection.Range
  Set oRNg2 = oRng.Duplicate
  With oRng.Find
    .Text = "^p"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    Do While .Execute
      If oRng.InRange(oRNg2) Then
        oRng.Text = " "
        oRng.Font.Bold = wdToggle
        oRng.Collapse wdCollapseEnd
      Else
        oRng.Collapse wdCollapseEnd
        Exit Do
      End If
    Loop
  End With
End Sub

rsrasc · #3 09-23-2018, 12:19 PM

[QUOTE=gmaxey;133567]Not sure exactly what you are trying to do, but something like this:

Mr. GMaxey,

Thank your for the code. Much appreciated.

I'm translating some text from English to Spanish using "Google Translate", and after adding the text in Spanish to the file, the macro that I was initially using was formatting the whole document, including the paragraphs.

Now with your code at least I can select the text that I'm adding without having to format the the whole text.

When copying or adding the Spanish text to the document, there are a whole bunch of spaces that I was initially removing them manually.

Your code is working as intended.

Of course, if I have more than two or three paragraphs, the selected text will not keep the paragraphs. If there is a way to keep the paragraphs in place that will be great.

Thank you for your effort.

Regards,
rsrasc

gmayor · #4 09-23-2018, 08:49 PM

Quote:

Originally Posted by rsrasc

if I have more than two or three paragraphs, the selected text will not keep the paragraphs. If there is a way to keep the paragraphs in place that will be great.

Given that the macro replaces paragraph breaks with spaces it is difficult to see how you might delete them and yet retain them.
It is also hard to imagine what

Code:

    Selection.Font.Bold = wdToggle
    Selection.Font.Bold = wdToggle
    Selection.WholeStory
    Selection.Font.Bold = wdToggle
    Selection.EscapeKey

is meant to achieve.
What is it that is wrong with the formatting that you are trying to address?

rsrasc · #5 09-24-2018, 07:03 AM

Basically, what I was trying to do was to copy the translated text from Spanish to a document, and when I was doing that some of text were truncated and in some other cases I have to delete the spaces manually in order to make a complete sentence.

Since I had the first code that I posted (given to me previously in this site) I try to use it, but then I realized that after adding new text to the document, it was formatting the whole document.

So, I decided to ask for a code that will only run with the new selected text that was added to the document. In some cases I was adding two or more paragraphs but when you apply the code to the document with the new (and old) code, it will also deleting the spaces between paragraphs.

So to answer your question, there is nothing wrong with the formatting that I was trying to address.

Not sure if there is a code available that will retain the paragraphs when formatting.

Thank you for time and cooperation.

Regards,
rsrasc

Jefgorbach · #6 09-24-2018, 12:08 PM

I think this accomplishes what you're trying to do.
I changed the Find-N-Replace code to a subroutine for easier reading and reuse.

Code:

Sub Macro6()
'Select your desired text to be changed then run the macro.
‘
Options.Pagination = False               'turn off background page counts for speed
Application.ScreenUpdating = False  'turn off screen changes for speed

With Selection
   .Font.Bold = wdToggle     'toggle selection's Bold state
   Call Swap(“^p^p”,”<P>”) ‘placeholder between paragraphs
   Call Swap(“^p”,” “)          ‘condense sentences
   Call swap(“<P>”,”^p^p”) ‘separate paragraphs again
End with
Selection.WholeStory.Font.Bold = wdToggle  'toggle entire document Bold state; why?
Selection.EscapeKey                                   ‘unsure if you need to exit selection?
Application.ScreenUpdating = True  'turn screen changes back on
Options.Pagination = True               'turn background page counts back on
End Sub


Sub Swap(First, Second)
With Selection
    .Find.ClearFormatting
    .Find.Replacement.ClearFormatting
    With Selection.Find
           .Text = First
           .Replacement.Text = Second
           .Forward = True
           .Wrap = False
           .Format = False
           .MatchCase = False
           .MatchWholeWord = False
           .MatchWildcards = False
           .MatchSoundsLike = False
           .MatchAllWordForms = False
       End With
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

gmayor · #7 09-24-2018, 10:40 PM

On the face of it the problem relates to the way you have pasted the text. If I understand the problem correctly, the following macro will paste the text from Google translate with the text formatted with the format at the cursor position

Code:

Sub PasteUnfText()
    On Error GoTo err_Handler
    Selection.PasteSpecial DataType:=wdPasteText, _
                           Placement:=wdInLine
lbl_Exit:
    Exit Sub
err_Handler:
    Beep
    Err.Clear
    GoTo lbl_Exit
End Sub

If you want to apply particular formatting then you can do that at the same time e.g.

Code:

Sub PasteSpanishText()
Dim oRng As Range, oStart As Range
    On Error GoTo err_Handler
    Set oRng = Selection.Range
    Set oStart = oRng.Duplicate
    With oRng
        .PasteSpecial DataType:=wdPasteText, _
                      Placement:=wdInLine
        .Start = oStart.Start
        .LanguageID = wdSpanishModernSort
        .NoProofing = False
        Application.CheckLanguage = False
        .Collapse 0
        .Select
    End With
lbl_Exit:
    Set oRng = Nothing
    Set oStart = Nothing
    Exit Sub
err_Handler:
    Beep
    Err.Clear
    GoTo lbl_Exit
End Sub

Better still create a Spanish style (or styles) that has the characteristics required and apply it. You could do that as you paste also bu formatting the range with the style.

Jefgorbach · #8 09-25-2018, 01:15 PM

Interesting.
so if Im comprehending correctly, the wdPasteText keyword not only strips all of the original formatting (font, bold, etc) but also removes all but the final paragraph mark, thereby consolidating the selection into a single paragraph?

If the selection spans several paragraphs, do they remain distinct because of the double-mark or become merged into a single one?

macropod · #9 09-25-2018, 03:07 PM

Quote:

Originally Posted by Jefgorbach

if Im comprehending correctly, the wdPasteText keyword not only strips all of the original formatting (font, bold, etc) but also removes all but the final paragraph mark, thereby consolidating the selection into a single paragraph?

That is basically correct. Although all font & paragraph formatting is removed, other than a final paragraph break, no characters are deleted. Hence tabs, non-terminating paragraph breaks, & line-breaks in the copied data are output (the latter are converted to paragraph breaks). End-of-cell markers in copied tables are converted to tabs when pasted as unformatted text. If the copied data include two or more paragraph breaks at the end, only the final one is deleted.

rsrasc · #10 09-26-2018, 02:34 AM

Hi Jefgorbach,

Thank you for your code. One question, I tried your code in two different ways.

First, I highlighted the text selected, but I'm getting a Compile Error: Sub or Function not define.

Second, I tried to run your code without highlighting the text I'm still getting the same error code.

Any ideas why this is happening?

Regards,
rsrasc

gmayor · #11 09-26-2018, 03:47 AM

That code has several issues, most notably the use of smart quotes in the code and the line

Code:

Selection.WholeStory.Font.Bold = wdToggle

is not a valid VBA command
I guess he meant

Code:

Selection.WholeStory
Selection.Font.Bold = wdToggle

I have not attempted to establish if the code actually does what you require as that remains vague.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Footnote extraction macro [Why is this macro so slow? / anyway to make it faster?]	Le_Blanc	Word VBA	10	03-22-2021 11:38 AM
Spell check macro within macro button field doesn't work in one document	samuelle	Word VBA	0	07-20-2016 02:27 AM
Macro Question: Need help making a macro to highlight the first word in every sentence	LadyAna	Word	1	12-06-2014 10:39 PM
Macro Needed to bold specific lines and Macro to turn into CSV	anewteacher	Word VBA	1	05-28-2014 03:59 PM
custom icon, undo/redo for macro, permanent macro	Rapier	Excel	0	08-05-2013 06:30 AM