Previous Topic Next Topic

Not sure how to use If Then statements or

Closed
weenie - 26 Jul 2011 à 00:39
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 28 Jul 2011 à 15:50
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 responses

Réponse 1 / 2
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
26 Jul 2011 à 00:59
you have to write down two more end if before next i.
Réponse 2 / 2
weenie
27 Jul 2011 à 14:25
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.
weenie
27 Jul 2011 à 14:27
I added 2 more End If before Next i but still keep getting compile error and it is highlighting in yellow the 1st Else.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
28 Jul 2011 à 15:50
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

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
Using IF function with dates
dkayeh1 -
RayH -

18 responses
Dates from an IF statement
Ian -
Ian -

2 responses
If Statement Excel
KoTTen -
vcoolio -

1 response
Help to debug this sentence for me please ?
SherlynPeh -
TrowaD -

1 response