mercredi 25 octobre 2017

How can I improve my multiple “if and else if” VBA code

I'm new to VBA and to this forum.

So, I have a series of data and depending on it I have to decide if it's a TE or LE edge. I have several conditions which I tried to sum up in the following code. The code is working but I have some blank spaces left which means certain conditions are not take, into account.

More specifically I think it's this one:

"ElseIf Val(Range("G" & i).Value) > 0.02149 Then Range("N" & i).Value = "TE".

Guess it's a problem of all this If and ElseIf but I couldn't find a solution to it.

So let me know if you have any suggestions. Thank you.

Sub edge()

On Error Resume Next

Dim lastRowIndex As Integer
Dim firstRowIndex As Integer
Dim MyString As String


lastRowIndex = ActiveSheet.UsedRange.Rows.Count

MyString = Application.ThisCell.Address
firstRowIndex = Range(MyString).Select

For i = firstRowIndex To lastRowIndex
If i Mod 2 <> 0 Then
    If Range("A" & i).Value = Range("A" & i + 1).Value Then
            If Range("G" & i).Value > Range("G" & i + 1).Value Then
                    If Range("H" & i).Value = "0" Then Range("N" & i).Value = "TE"
                    ElseIf 0.01898 < Val(Range("G" & i).Value) < 0.02149 Then Range("N" & i).Value = "LE"
                    **ElseIf Val(Range("G" & i).Value) > 0.02149 Then Range("N" & i).Value = "TE"**
                    End If

            ElseIf Range("G" & i).Value < Range("G" & i + 1).Value Then
                    If Range("H" & i).Value = "0" Then Range("N" & i).Value = "LE"
                    ElseIf 0.01898 < Val(Range("G" & i).Value) < 0.02149 Then Range("N" & i).Value = "LE"
                    ElseIf Val(Range("G" & i).Value) > 0.02149 Then Range("N" & i).Value = "TE"
                    End If



ElseIf i Mod 2 = 0 Then
    If Range("A" & i).Value = Range("A" & i - 1).Value Then
            If Range("G" & i).Value > Range("G" & i - 1).Value Then
                    If Range("H" & i).Value = "0" Then Range("N" & i).Value = "TE"
                    ElseIf 0.01898 < Val(Range("G" & i).Value) < 0.02149 Then Range("N" & i).Value = "LE"
                    ElseIf Val(Range("G" & i).Value) > 0.02149 Then Range("N" & i).Value = "TE"

            ElseIf Range("G" & i).Value < Range("G" & i - 1).Value Then
                    If Range("H" & i).Value = "0" Then Range("N" & i).Value = "LE"
                    ElseIf 0.01898 < Val(Range("G" & i).Value) < 0.02149 Then Range("N" & i).Value = "LE"
                    ElseIf Val(Range("G" & i).Value) > 0.02149 Then Range("N" & i).Value = "TE"
            End If
    End If
End If


Next

End Sub

Aucun commentaire:

Enregistrer un commentaire