Consulting

Results 1 to 9 of 9

Thread: VBA Help with square bracket issue

  1. #1

    VBA Help with square bracket issue

    I would be very grateful for some help with an issue in the code below. The code below is part of a larger macro but after stepping through each part of the code, this is the part that is causing the issue. This part of the code inserts bold quotes around bold definition words then inserts a tab after the closing bold quote (see attachment). The issue is if there is a square bracket at the beginning it doesn't insert a tab or if there was already a tab there before the code is run that tab is being removed, I'm really stumped on how to fix this so any help would be very much appreciated.


    Set oRng = ActiveDocument.RangeWith oRng.Find
      .Text = ""
      .Replacement.Text = "^034^&^034"
      .Font.Bold = True
      .Format = True
      .MatchWildcards = True
      While .Execute
      If Not InStr(oRng.Text, Chr(13)) Then 
      While oRng.Characters.Last = Chr(32) 
      oRng.Characters.Last.Font.Bold = False
      oRng.End = oRng.End - 1
      Wend
      oRng.Text = Chr(34) & oRng.Text & Chr(34) 
      If oRng.Characters.First.Previous = Chr(13) Then 
      oRng.Collapse wdCollapseEnd
      oRng.Font.Bold = False
      oRng.Characters.Last = vbTab
      Else
      oRng.Collapse wdCollapseEnd
      End If
      End If
      Wend
    End With
    Attached Files Attached Files

  2. #2
    Try the following
    Dim oRng As Range
        Set oRng = ActiveDocument.Range
        With oRng.Find
            .Text = ""
            .Font.Bold = True
            .Format = True
            .MatchWildcards = True
            While .Execute
                With oRng
                    .MoveStartWhile "["
                    .MoveEndWhile Chr(13), wdBackward
                    .MoveEndWhile Chr(9), wdBackward
                    .MoveEndWhile Chr(32), wdBackward
    
                    If Not .Characters(1) = Chr(34) Then
                        .Text = Chr(34) & .Text
                        .Text = .Text & Chr(34)
                        .Font.Bold = True
                        .End = .End + 1
                    End If
                    .Collapse 0
                End With
            Wend
        End With
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Hi Graham, many thanks for getting back to me it is very much appreciated. Unfortunately that did not work, it ended up removing all the tabs from each definition after the bold closing quote instead of inserting a tab after the closing quote

  4. #4
    The macro should not add nor remove any tabs and does not here. It merely locates the bold text and adds quotes where they are not already present, and in the case of an opening square bracket, the opening quote is inserted after that bracket.

    2022-01-08_08-47-08.jpg
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Hi Graham, thanks for clarifying but unfortunately quotes were not the issue I had in my original post. My original code inserts a tab after the closing bold quote which is what I want it to do, but the issue is, if there is a square bracket at the beginning (before the opening bold quote) the tab part of the code isn't recognising this to insert a tab after the closing bold quote, this is where I need some help as I can't figure out why its not inserting a tab.


    oRng.Text = Chr(34) & oRng.Text & Chr(34) 'Quotation marks  If oRng.Characters.First.Previous = Chr(13) Then 'Carriage return
      oRng.Collapse wdCollapseEnd
      oRng.Font.Bold = False
      oRng.Characters.Last = vbTab
      Else
      oRng.Collapse wdCollapseEnd
    EDIT: Graham I think I've fixed the issue as per below

    .Text = .Text & Chr(34) & Chr(9)
    This seems to be working but has thrown up another issue if there is an additional definition within the same paragraph, it is inserting a tab but my code below should remove the tab before 'shall' but isn't working now, any ideas?

    Proper Functioning"[tab]means, in respect of each PV System, the proper and correct functioning and the good commercial operation that would be expected from the Commencement Date, including but not limited to the proper and correct energy conversion, electrical generation, PV System monitoring, functioning of each and every Component Part, and structural stability and integrity and "Properly Functioning"[tab]shall be construed accordingly;

    Set oRng = ActiveDocument.Range  With oRng.Find
        .Text = "^t"
        Do While .Execute
          oRng.Start = oRng.Paragraphs(1).Range.Start
          If Len(.Text) - Len(Replace(.Text, vbTab, "")) > 1 Then oRng.Characters.Last.Text = " "
          oRng.Collapse wdCollapseEnd
        Loop
      End With
    Last edited by Shelley_Lou; 01-09-2022 at 03:40 AM.

  6. #6
    Better not to insert the second tab if not required e.g.
      If oRng.Start = oRng.Paragraphs(1).Range.Start Then
          oRng.Text = Chr(34) & oRng.Text & Chr(34) & vbTab
      Else
          oRng.Text = Chr(34) & oRng.Text & Chr(34)
      End If
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    Hi Graham, thank you so much for helping me with this and yes you are correct, there should not be a second tab within the definition. So we are very nearly there but it still isn't inserting a tab after the closing quotes if it begins with a square bracket, any ideas?

    Image.jpg


    Set oRng = ActiveDocument.Range    With oRng.Find
            .Text = ""
            .Font.Bold = True
            .Format = True
            .MatchWildcards = True
            While .Execute
        With oRng
            .MoveStartWhile "["
            .MoveEndWhile Chr(13), wdBackward
            .MoveEndWhile Chr(9), wdBackward
            .MoveEndWhile Chr(32), wdBackward
        If oRng.Start = oRng.Paragraphs(1).Range.Start Then
          oRng.Text = Chr(34) & oRng.Text & Chr(34) & vbTab
        Else
          oRng.Text = Chr(34) & oRng.Text & Chr(34)
        End If
          .Font.Bold = True
          .End = .End + 1
          .Collapse 0
        End With
        Wend
        End With

  8. #8
    OK I see what the problem is there
    Dim oRng As Range
        Set oRng = ActiveDocument.Range
        With oRng.Find
            .Text = ""
            .Font.Bold = True
            .Format = True
            .MatchWildcards = True
            While .Execute
                With oRng
                    .MoveStartWhile "["
                    .MoveEndWhile Chr(13), wdBackward
                    .MoveEndWhile Chr(9), wdBackward
                    .MoveEndWhile Chr(32), wdBackward
                    Select Case True
                        Case Is = oRng.Start = oRng.Paragraphs(1).Range.Start, _
                             oRng.Start = oRng.Paragraphs(1).Range.Start + 1 And _
                             oRng.Paragraphs(1).Range.Characters(1) = "["
                            oRng.Text = Chr(34) & oRng.Text & Chr(34) & vbTab
                        Case Else
                            oRng.Text = Chr(34) & oRng.Text & Chr(34)
                    End Select
                    .Font.Bold = True
                    .End = .End + 1
                    .Collapse 0
                End With
            Wend
        End With
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    Hi Graham, you are a legend, thank you so much for helping me sort this issue. I've run the code several times and all seems to be working brilliantly, I can't thank you enough for your help, it is very much appreciated. Best wishes, Shelley

Posting Permissions

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