vendredi 5 janvier 2018

Unexpected control flow of If Statement Excel data type check

I have numeric values in two Ranges; Range M and Range O. I have to make simple comparison between data present at these two ranges. However First I need ensure data is correct (Correct data should always be a integer), So I am using an IF condition to check whether data is number and then compare the two values, else print a message that wrong data. But condition is always gong to else statement regardless of IF being true. Help is much appreciated. Below is the code:

Sub CheckDataType()

Dim i As Long
Dim var As String
Dim var2 As String
Dim var3 As String
For i = 3 To 89 'To loop from range 3 to 89

   var = Range("M" & i)
   var2 = Range("O" & i)
   If IsNumeric(var) And IsNumeric(var2) Then ' First If
        If Sheet1.Range("M" & i).Value = Sheet1.Range("O" & i).Value Then ' Second If
            Sheet1.Range("R" & i).Value = "No Chnage"

            ElseIf Sheet1.Range("M" & i).Value > Sheet1.Range("O" & i).Value Then
                Sheet1.Range("R" & i).Value = "Decrease"

            ElseIf Sheet1.Range("M" & i).Value < Sheet1.Range("O" & i).Value Then
                Sheet1.Range("R" & i).Value = "Increase"

            Else
            Sheet1.Range("R" & i).Value = "Other"
        End If 'Second If

    Else

        var3 = Range("M" & i).Offset(0, -3)
        MsgBox "Wrong data of " & var3 & vbCrLf & "Click Ok to proceed and 
        enter Correct Data Latter manually", vbOKOnly

 End If 'First If
Next
lRow = Sheet1.Range("R" & Rows.Count).End(xlUp).Row
Sheet1.Range("R2").Value = "Two Months comparison"



End Sub

Aucun commentaire:

Enregistrer un commentaire