Comparing Integer values in paragraphs and bold higher value

mbecerra24 · May 30th 2015

Hi Everyone,

I'd like to get some guidance on how I can develop a code to compare integer values in different paragraphs, then bold the one that is higher.

I've been working on this for a while and I've got some progress, but the code fails when it reads into the values on the title paragraphs. (See attachment and run code and you'll see what I mean). Another thing to note is that the "range().words" that I use was all I could find in my research to bold the characters I wanted, but I realized that if I have a "Q (cfs)" in the hundreds, it will bold the first number, and I do not want that. I've attached the code and the word document that I'm working with. If anyone could please make this work, or have any comments and feed back, I would greatly appreciate it. I've been learning about macros and VBA for over 2 months and this code in particular will really help me with other vba projects that I want to get started on.

PS: I am a newb and I realize that my coding may be unprofessional or weird looking so please excuse me. I will get better with your help.

Thank you

Code

Sub Elevation_bold_beta1()
 Dim MyDoc As Document
 Dim parajx As Paragraph
 Dim paraq1 As Paragraph
 Dim paraq2 As Paragraph
 Dim a As String
 Dim b As String
 Dim e1 As Integer
 Dim e2 As Integer
 


 Set MyDoc = ActiveDocument
 
 
For p = 1 To MyDoc.Paragraphs.Count
 
    Set parajx = MyDoc.Paragraphs(p)
    Set paraq2 = MyDoc.Paragraphs(p + 2)
    Set para = MyDoc.Paragraphs(p + 14)
    
     
    b = Mid(paraq2, 41, 9)
    Z = Mid(parajx, 2, 9)
    
 
 
 
 
     If Z = "JUNCT STR" Then
    
     Set paraq1 = MyDoc.Paragraphs(p - 2)
     a = Mid(paraq1, 41, 9)
        If a = b Then
        Else
 
    
            
         e2 = Mid(paraq2, 32, 8)
         e1 = Mid(paraq1, 32, 8)
        
         If e1 > e2 Then
         
        ActiveDocument.Paragraphs(p - 2).Range.Words(11).Select
        Selection.Range.Bold = True
        ActiveDocument.Paragraphs(p - 2).Range.Words(12).Select
        Selection.Range.Bold = True
        ActiveDocument.Paragraphs(p - 2).Range.Words(13).Select
        Selection.Range.Bold = True
         


        Else
                
        If e2 > e1 Then
        ActiveDocument.Paragraphs(p + 2).Range.Words(11).Select
        Selection.Range.Bold = True
        ActiveDocument.Paragraphs(p + 2).Range.Words(12).Select
        Selection.Range.Bold = True
        ActiveDocument.Paragraphs(p + 2).Range.Words(13).Select
        Selection.Range.Bold = True
        


        End If
        End If
       End If
    End If
                
           
 
                
                
                
                
                
                
 


         
Next
            
End Sub

Display More

**pike** · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

Hi mbecerra24

Welcome to the forum

Its a little unclear what you are trying to do. You have to remember that no one has the same affinity to your work as you do.

is it where the JUNCT STR values cross over a page title or are the JUNCT STR only then next two values?

**pike** · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

quick look at the data you want to process some data when the heading is JUNCT STR
then stop when the title is HYDRAULIC JUMP or wall entrance??

**pike** · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

also FILE: 532PH4W100A and L/Elem could be used as triggers/flags to skip to the next value

**pike** · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

all that said your very close

**pike** · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

heaps of ways to deal with and find the charactors to bold

Code

Debug.Print paraq1.Range.Text
           Debug.Print paraq2.Range.Characters.Count
  
           Debug.Print paraq1.Range.Words.Count
 
            Debug.Print paraq2.Range.Words.Item(1)
             Debug.Print paraq2.Range.Words.Item(2)

cytop · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

Not sure if this is a potential issue or not...

You use

Code

....Range.Words(11).Select

to select a value to make bold. Problem is Word treats the decimal as the 'end of a word' but the actual value in the document might be 1471.442 - you're only going to bold the integer portion of that number.

mbecerra24 · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

First off, thank you for taking the time to look at the issue and the suggestions you have posted thus far.

Let me further explain: I'm using the "junct str" as a trigger to analyze the 2 numbers in the "water surface elevation" column. This output describes the water surface elevation for storm drains and the water surface elevation value is needed at "junct str" when there is a lateral introduced to the system, to further analyze that lateral in the next analysis ( another output data that is). The output doesn't tell you if there's a lateral, but you can tell easily by looking at the "Q (cfs)" column, since the Q value will change. So really, i need to do something like ...if this paragraph has "junct str", then if these 2 other paragraphs area equal( the Q values), then do nothing, otherwise if the elevation in the paragraph x > elevation i paragraph y, then bold the greater value, otherwise bold the other value. Do you think theres another way to do all this besides using "if else" statements? I will look into the suggestions that you have posed so far. Just thought I'd reply as soon as possible.

I hope this makes sense.

mbecerra24 · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

First off, thank you for taking the time to look at the issue and the suggestions you have posted thus far.

Let me further explain: I'm using the "junct str" as a trigger to analyze the 2 numbers in the "water surface elevation" column. This output describes the water surface elevation for storm drains and the water surface elevation value is needed at "junct str" when there is a lateral introduced to the system, to further analyze that lateral in the next analysis ( another output data that is). The output doesn't tell you if there's a lateral, but you can tell easily by looking at the "Q (cfs)" column, since the Q value will change. So really, i need to do something like ...if this paragraph has "junct str", then if these 2 other paragraphs area equal( the Q values), then do nothing, otherwise if the elevation in the paragraph x > elevation i paragraph y, then bold the greater value, otherwise bold the other value. Do you think theres another way to do all this besides using "if else" statements? I will look into the suggestions that you have posed so far. Just thought I'd reply as soon as possible. :smile:

I hope this makes sense.

mbecerra24 · May 30th 2015

Re: Comparing Integer values in paragraphs and bold higher value

Quote from cytop;747407
Not sure if this is a potential issue or not...

You use
Code
....Range.Words(11).Select
to select a value to make bold. Problem is Word treats the decimal as the 'end of a word' but the actual value in the document might be 1471.442 - you're only going to bold the integer portion of that number.

Hi!

Interesting. I wasn't aware of that fact. I'll keep that in mind and look further into it. Thanks for the reply!

**pike** · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

you will have to explain exactly what you mean
find junct str
then compare
what to what
the "Q (cfs)" is the next row down?
I really don't have any idea with out explanation
Remember I have no understanding of the process and only need the step explained of exactly which values and where they are

**pike** · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

mbecerra24

the easiest way for me to understand is if you can highlight the word to look for and the numbers to compare in red : then attach it

your on the right path .. its just the affinity thing

**pike** · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

Think I got it

find junct str
compare "Q (cfs)" above the "junction str" to the "Q (cfs)" below "junction str"
if equal do nothing
if not then
compare "water level" above to the" water level" below and highlight the highest value

is that it?

**pike** · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

maybe ...

Code

Option Explicit
Sub Elevation_bold_Test()
    Dim MyDoc As Document
    Dim parajx As Paragraph, paraq1 As Paragraph, paraq2 As Paragraph
    Dim p As Long, xItem As Long, zItem As Long, e1 As Long, e2 As Long
   
   Set MyDoc = ActiveDocument


    For p = 1 To MyDoc.Paragraphs.Count
        Set parajx = MyDoc.Paragraphs(p)
         If parajx.Range.Text Like "*JUNCT STR*" Then
            Do
               xItem = xItem + 1
            Set paraq1 = MyDoc.Paragraphs(p - xItem)
               If IsNumeric(paraq1.Range.Words.Item(2)) And InStr(paraq1.Range.Words.Item(3), ".") > 0 Then
                  e1 = Mid(paraq1, 32, 8)
                  Exit Do
               End If
            Loop


            Do
               zItem = zItem + 1
             Set paraq2 = MyDoc.Paragraphs(p + zItem)
               If IsNumeric(paraq2.Range.Words.Item(2)) And InStr(paraq2.Range.Words.Item(3), ".") > 0 Then
                   e2 = Mid(paraq2, 32, 8)
                  Exit Do
               End If
            Loop
                If e1 > e2 Then
                       ActiveDocument.Paragraphs(p - xItem).Range.Words(11).Bold = True
                       ActiveDocument.Paragraphs(p - xItem).Range.Words(12).Bold = True
                       ActiveDocument.Paragraphs(p - xItem).Range.Words(13).Bold = True


                ElseIf e2 > e1 Then
                        ActiveDocument.Paragraphs(p + zItem).Range.Words(11).Bold = True
                        ActiveDocument.Paragraphs(p + zItem).Range.Words(12).Bold = True
                        ActiveDocument.Paragraphs(p + zItem).Range.Words(13).Bold = True
                         
                         
                    End If
                End If
     
          xItem = 0
          zItem = 0
    Next
     
End Sub

Display More

mbecerra24 · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

Yes that's it! Here's the word document as you wanted. i also put a comment on pg 7. Let me know if this helps. seems like you got it though haha..

**pike** · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

Hi mbecerra24
Try this
only way to deal with the large number ( in the Thousands) was with regular expression

Code

Option Explicit
Sub Elevation_bold_Test()
    Dim MyDoc As Document
    Dim parajx As Paragraph, paraq1 As Paragraph, paraq2 As Paragraph
    Dim p As Long, xItem As Long, zItem As Long, e1 As Long, e2 As Long, x1 As String, x2 As String
    Dim regex As Object, myRange As Range, xMatches, zMatches
    
    Set MyDoc = ActiveDocument
    Set regex = CreateObject("VBScript.RegExp")
        regex.IgnoreCase = True
        regex.Global = True
        regex.Pattern = "(\d{0,3})+(\,?\d{0,3})*(\.\d+)"


    For p = 1 To MyDoc.Paragraphs.Count
        Set parajx = MyDoc.Paragraphs(p)
        If parajx.Range.Text Like "*JUNCT STR*" Then
            
            Do
                xItem = xItem + 1
                Set paraq1 = MyDoc.Paragraphs(p - xItem)
                If IsNumeric(paraq1.Range.Words.Item(2)) And InStr(paraq1.Range.Words.Item(3), ".") > 0 Then
                   Set xMatches = regex.Execute(paraq1.Range)
                    e1 = xMatches(4)
                    x1 = xMatches(3)
                    
                    Exit Do
                End If
            Loop
             
            Do
                zItem = zItem + 1
                Set paraq2 = MyDoc.Paragraphs(p + zItem)
                If IsNumeric(paraq2.Range.Words.Item(2)) And InStr(paraq2.Range.Words.Item(3), ".") > 0 Then
                 Set zMatches = regex.Execute(paraq2.Range)
                    e2 = zMatches(4)
                    x2 = zMatches(3)
                    Exit Do
                End If
            Loop
            If e1 > e2 Then
            
            Set myRange = ActiveDocument.Paragraphs(p - xItem).Range
              myRange.Find.Execute FindText:=x1, Forward:=True, MatchWholeWord:=True
               If myRange.Find.Found = True Then myRange.Font.Color = vbRed
           ElseIf e2 > e1 Then
              Set myRange = ActiveDocument.Paragraphs(p + zItem).Range
              myRange.Find.Execute FindText:=x2, Forward:=True, MatchWholeWord:=True
               If myRange.Find.Found = True Then myRange.Font.Color = vbRed
            End If
        
        End If
         
        xItem = 0
        zItem = 0
    Next
    
    Set zMatches = Nothing
    Set xMatches = Nothing
    Set myRange = Nothing
    Set paraq1 = Nothing
    Set paraq2 = Nothing
    Set MyDoc = Nothing
    Set regex = Nothing
End Sub

Display More

mbecerra24 · May 31st 2015

Re: Comparing Integer values in paragraphs and bold higher value

Hi Pike

Just wanted to let you know that it worked great and thank you for your help. I've never heard of using "regular expressions " but now that i know what to look for, i will study and look into that since i will be dealing
with word processing in the future. Thanks again, i appreciate your help. Have a good day.

**pike** · Jun 1st 2015

Re: Comparing Integer values in paragraphs and bold higher value

Hi mbecerra24

No thank you for posting an interesting puzzle

have remove all your quotes .. in future just remember that they are not needed and just clutter the threads

improvement on this snippet of syntax

Code

Do
                xItem = xItem + 1
                Set paraq1 = MyDoc.Paragraphs(p - xItem)
                Set xMatches = regex.Execute(paraq1.Range)
               If xMatches.Count > 4 Then
                    e1 = xMatches(4)
                    x1 = xMatches(3)
                    Exit Do
                End If
            Loop
             
            Do
                zItem = zItem + 1
                Set paraq2 = MyDoc.Paragraphs(p + zItem)
                Set zMatches = regex.Execute(paraq2.Range)
               If zMatches.Count > 4 Then
                    e2 = zMatches(4)
                    x2 = zMatches(3)
                    Exit Do
                End If
            Loop

Display More

mbecerra24 · Jun 4th 2015

Re: Comparing Integer values in paragraphs and bold higher value

Gotcha! Thanks, much appreciated!

Comparing Integer values in paragraphs and bold higher value

Participate now!

Share