Replacing < and > with numbers using If Then loop

[VAPALIPOT]

I want to scan a column of numbers that occasionally contain some values that have either "< 0.000" or a "> 10" listed. When I find them, I need to write a slightly different string set to an adjacent column. I also need to write an integer to a third column as a FLAG. So when I find the "< 0.000", I need to replace it with a "<" and the cell value I have stored elsewhere (e.g., "N31"), then write a 5 to an adjacent cell on the same row. A similar activity occurs with the "> 10.4" except there is a multiplication that occurs to create the inserted value after the ">". Finally, an integer 2 is inserted into the same column as the 5.

Sub TEST1()
' This sub designed to screen for occurrences of "<" and ">" in Columns "6" and "8" respectively & insert appropriate answers in Column "8" and then insert corresponding flags in Col "9"
    Dim patternLESS As String
    Dim patternMORE As String
    Dim j As Integer    'j = row
    Dim I As Integer    'i = column
    Dim DilFactor As Long

    patternLESS = "<"
    patternMORE = ">"

    For j = 77 To 86
        If Cells(j, 6) = patternLESS Then
            Cells(j, 8) = "<" & "N31"
            Cells(j, 9) = 5
        End If
    Next j

    For j = 77 To 86
        If Cells(j, 6) = patternMORE Then
            DilFactor = Cell(j, 7).Value * Cell("N22").Value
            Cells(j, 8) = ">" & "DilFactor"
            Cells(j, 9) = 2
        End If
    Next j
End Sub

I tried to run this code but it came back with 'undefined function' at the DilFactor line where I was trying to multiply two cell values. My apologies as I couldn't find the section that indicated that I had code to upload on the Post New Thread section.

Thanks for your help.

 

[VAPALIPOT]

I rewrote the VBA code and came up with a simpler(?) solution although it still halts with an error code of "argument not optional" at the first line with "Left" in it. I am wondering how to redefine the need to search for that "<" symbol in the values listed in column 6, ("F"). Any help would be appreciated.

Sub TEST2()
  ' This sub designed to test replacement of "<" and ">" and insert appropriate flags in Col "I"
 
  Dim r1 As Range
  Set r1 = Range("F77:F150")
  
        For j = 77 To 86
            If Left(r1.Cells(j, 6)).Value = Chr(60) Then
                Cells(j, 8).Value = "<" & Cells(31, 14).Value
                Cells(j, 9) = 5
                End If
             Next j
            
        For j = 77 To 86
            If Left(r1.Cells(j, 6)).Value = Chr(62) Then
                Cells(j, 8).Value = ">" & Cells(j, 7).Value * Cells(22, 14).Value
                Cells(j, 9) = 2
                End If
             Next j
End Sub

Thanks for looking at this.

 

[Joe4]

The LEFT function has two arguments. You only have one. If you want to pull just the first character, use:

Left(r1.Cells(j, 6)[COLOR=#ff0000][B],1[/B][/COLOR])

 

[VAPALIPOT]

Thanks for your quick reply. I'll give a try.

 

[VAPALIPOT]

Ok, I added the second argument in the first IF statement and the error code changes to "Object required" on the same line. It seems like there is another part of the If expression I am missing or I have it configured wrong?

Sub TEST2()
  ' This sub designed to test replacement of "<" and ">" and insert appropriate flags in Col "I"
 
  Dim r1 As Range
  Dim LeftStrnL As String
  Dim LeftStrnG As String
 
  Set r1 = Range("F77:F150")
  
        For j = 77 To 86
                LeftStrnL = Left(r1.Cells(j, 6), 1).Value
                If LeftStrnL = Chr(60) Then
                Cells(j, 8).Value = "<" & Cells(31, 14).Value
                Cells(j, 9) = 5
                End If
             Next j
            
        For j = 77 To 86
            If Left(r1.Cells(j, 6), 1).Value = Chr(62) Then
                Cells(j, 8).Value = ">" & Cells(j, 7).Value * Cells(22, 14).Value
                Cells(j, 9) = 2
                End If
             Next j
End Sub

 

[Norie]

Left returns a string, not an object - try dropping the .Value.

 

[VAPALIPOT]

I corrected the Left statements to position the ".value" portion in its correct place. So now I have gotten to where the program doesn't throw errors and it apparently executes (in the loops) as expected, but nothing happens in the EXCEL sheet. I then inserted a MSGBOX to allow me to see what the value of LeftStrnL and there is apparently no value for the variable "LeftStrnL" as I step through the program. So it is not extracting the "<" from the results in column 6. Is there a way to determine where the program is actually tracking in the target file?

Sub TEST3()
  ' This sub designed to test replacement of "<" and ">" and insert appropriate flags in Col "I"
 
  Dim r1 As Range
  Dim LeftStrnL As String
  Dim LeftStrnG As String
 
  Set r1 = Range("F77:F150")
  
        For j = 77 To 86
                LeftStrnL = Left(Cells(j, 6).Value, 1)
               
                MsgBox LeftStrnL
               
                If LeftStrnL = "<" Then
                Cells(j, 8).Value = "<" & Cells(31, 14).Value
                Cells(j, 9) = 5
                End If
             Next j
            
        For j = 77 To 86
                LeftStrnG = Left(r1.Cells(j, 6).Value, 1)
                If LeftStrnG = " > " Then
                Cells(j, 8).Value = ">" & Cells(j, 7).Value * Cells(22, 14).Value
                Cells(j, 9) = 2
                End If
             Next j
End Sub

 

[Joe4]

In your examples in your first post, it looks like you were showing a space between the ">" or "<" sign and the number.
But you do not appear to be accounting for that in building it in VBA.

Perhaps you need to change things like this:

"<" & Cells(31, 14).Value

to this:

"< " & Cells(31, 14).Value

 

[VAPALIPOT]

Thanks for the insight and observation. I'll go over the program again to remove any extra spaces.

 

[VAPALIPOT]

I wanted to return with a copy of the functioning program so it could be viewed by others. I also wanted to thank Norie and Joe4 for their comments which were crucial to getting converting the earlier version of this program into functioning status. Norie's comment about .value not being part of the Left function essentially made the program work. Joe4's comment about the spacing in the "< " caused me to reexamine closely what the second loop was screening for and I found an extra space where it should not have been and was essentially silencing the 2nd For-If loop. So I learned three things from this excercise: 1) SYNATX RULES - every line of code must be closely examined for possible discrepancy, 2) Use MsgBox to monitor variable values during execution, 3) Use this message board as it is a wealth of experience and will greatly reduce time to create a functioning program.

Sub TEST4()   
 
' This sub designed to test replacement of "<" and ">" in col 6.
‘ If discovered, then insert appropriate response either extracted from cell 
‘ or calculated, then inserted into col 8.
‘ Finally, set corresponding number flags (“5” or “2”) in Col 9 when discovered.
 
       Dim j as Integer
Dim LeftStrnL As String   
Dim LeftStrnG As String     
 
   For j = 77 To 86                 
LeftStrnL = Left(Cells(j, 6), 1)                                 
MsgBox LeftStrnL                                 
If LeftStrnL = "<" Then                 
Cells(j, 8).Value = "< " & Cells(31, 14).Value
Cells(j, 9) = 5                 
End If              
   Next j                      
 
   For j = 77 To 86                 
LeftStrnG = Left(r1.Cells(j, 6), 1) 
If LeftStrnG = ">" Then                 
Cells(j, 8).Value = "> " & Cells(j, 7).Value * Cells(22, 14).Value
Cells(j, 9) = 2                 
End If              
   Next j 
END Sub

Thanks again for all of the comments and suggestions!