Replacing < and > with numbers using If Then loop

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14
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.

Code:
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14
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.

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
The LEFT function has two arguments. You only have one. If you want to pull just the first character, use:
Code:
Left(r1.Cells(j, 6)[COLOR=#ff0000][B],1[/B][/COLOR])
 

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14

ADVERTISEMENT

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?

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
Left returns a string, not an object - try dropping the .Value.
 

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14

ADVERTISEMENT

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?

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
"<" & Cells(31, 14).Value
to this:
Code:
"< " & Cells(31, 14).Value
 

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14
Thanks for the insight and observation. I'll go over the program again to remove any extra spaces.
 

VAPALIPOT

New Member
Joined
Jan 18, 2010
Messages
14
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.

Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,196
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top