Not sure how to use If Then statements or

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have data in Column M and if it finds exact values I entered below then Put the following text in next Column "N" (be the same row ). But I am not good with IF Then statements and when I try to run gives me a Else without If error. I have read that maybe a Select case could be better in this situation but at a loss where to start.

Sub Macro15()
'To look up values in a column M then enter a text on another column N

Dim LR As Long, i As Long

With Sheets("Failure Data")
LR = .Range("M" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If .Range("M" & i).Value = 1E+17 And .Range("N" & i).Value = "" Then
With .Range("N" & i).Value = "No Break"
Else
If .Range("M" & i).Value = 1E-17 And .Range("N" & i).Value = "" Then
With .Range("N" & i).Value = "Pretest Leakage"
Else
If .Range("M" & i).Value = 1E+30 And .Range("N" & i).Value = "" Then
With .Range("N" & i).Value = "Undefined"
Else
End With
End If
Next i
End With
End Sub

Thanks,
Weenie

2 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
you have to write down two more end if before next i.
I'm still getting complie error Else with out if even after I added the 2 End if before Next i. It highlights in yellow the 1st Else.
I added 2 more End If before Next i but still keep getting compile error and it is highlighting in yellow the 1st Else.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You got wrong "With", like With .Range("N" & i).Value = "Pretest Leakage" . You need to only say

.Range("N" & i).Value = "Pretest Leakage"


you can try this

Sub Macro15()
'To look up values in a column M then enter a text on another column N

   Dim LR As Long, i As Long
   
   With Sheets("Failure Data")
      LR = .Range("M" & Rows.Count).End(xlUp).Row
      For i = 2 To LR
         If .Range("N" & i).Value = vbNullString _
         Then
            Select Case .Range("M" & i).Value
               Case Is = 1E+17
                  .Range("N" & i).Value = "No Break"
               Case Is = 1E-17
                  .Range("N" & i).Value = "Pretest Leakage"
               Case Is = 1E+30
                  .Range("N" & i).Value = "Undefined"
            End Select
         End If
      Next i
   End With
End Sub