Posts by mbecerra24

Re: Referencing columns with values only, derived from formula

Oh ok. Yeah I was trying to upload it but It was probably too large. I'll keep that in mind next time I post something. Anyways, I finally figured it out this weekend. Thanks for the reply Pike.

Hi Everyone

So I need assistance with finishing this code. The problem here is that the code is not counting the correct number of rows that contain values.
if you refer to the code below, there are 12 columns of interest. the start at row 12 and the column changes. The columns start never changes though. (e.g. 1st column of interest starts at F21, 2nd column at M21 etc). the
end of the column range varies. the columns will either have a value such as "1000.00,1450.000", "pline" or will be empty, but the empty cells were created from a formula. In other words, the range is derived from a formula, such as if( x = y, then "1000.00,1450.00", if(x=z, "pline", "")). The code should only pick if the count for th rows that contain "1000.00,1450.00" or "pline".

Thank you, Please let me know if you need further explanation.

*note: the first 3 loops, z = 1,2,3, may be different from the rest because I've been trying to figure it out, and I still am.

Sub Script_File_Export_3()
Dim myfile As String, rng As Range, cellvalue As String, i As Integer, j As Integer
Const basefilename As String = "HGL_"
Dim x As Integer
Dim z As Long
Const outputpath As String = "C:\Users\mbecerra\Documents\Batch File Folder\HGL\3) HGL Script Files\"
Set rng = Selection
Range("a1").Select
Range("f21", Range("f21").End(xlDown)).Select


'n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
' counts number of cells that i need but misses one
'x = n + 1
' this one accounts for that missing one
For z = 1 To 12
Open outputpath & basefilename & z & ".scr" For Output As #1
    If z = 1 Then
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    If z = 2 Then
    
    
    For i = 1 To rng.Rows.SpecialCells(xlCellTypeVisible).count + 1
    'For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
       
    If z = 3 Then
    Range("t21", Range("t21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    If z = 4 Then
    Range("aa21", Range("aa21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    Else
    
    If z = 5 Then
    Range("ah21", Range("ah21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    
    If z = 6 Then
    Range("ao21", Range("ao21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    Else
    
    If z = 7 Then
    Range("av21", Range("av21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    If z = 8 Then
    Range("bc21", Range("bc21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    If z = 9 Then
    Range("bj21", Range("bj21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    
    Else
    
    If z = 10 Then
    Range("bq21", Range("bq21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    Else
    
    
    If z = 11 Then
    Range("bx21", Range("bx21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    Else
    
    If z = 12 Then
    Range("cf21", Range("cf21").End(xlDown)).Select
    For i = 1 To rng.Rows.count
    cellvalue = ""
    cellvalue = rng.Cells(i, 1).Value
    Print #1, cellvalue
    Next i
    Close #1
    Else
    
    
    
    
    End If
    End If
    
    End If
    
    End If
    End If
    End If
    End If
     
 
    
    End If
    
    
    
    
    
    End If
    
    End If
    End If
    End If
    
    
    
    
Next
 


End Sub

Re: Comparing Integer values in paragraphs and bold higher value

Gotcha! Thanks, much appreciated!

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.

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..

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!

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.

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.

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

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