Excel VBA If Else

Question

Sorry for the way i posted this question earlier and in a way i was confused with where to type the question so that it's separate from the code.

I have the code below in excel VBA. I'm trying to compare the string values in excel sheet with the String value in VBA.If a match is found,a 2 should be entered in cell ni where i is the cell position as per the looop else, x should be entered.The problem is in the if condition part,the truth condition is skipped and instead the else condition is executed which gives me wrong results.Despite checking in all forums on google since yesterday noon and tweaking it in all manner,i've not gotten a solution yet..i'm a newbie in VBA as well

Sub outcome2()

Dim LastRow As Long, i As Long

LastRow = Cells(Rows.Count, "H").End(xlUp).Row
For i = 1 To LastRow
Dim stor1  As Integer
Dim stor2  As String
Dim stor3  As String
Dim stor4  As String
Dim stor5  As String
Dim stor6  As String
Dim stor7  As String
Dim stor8  As String
Dim stor9  As String
Dim stor10 As String
Dim stor11 As String
Dim stor12 As String
Dim stor13 As String
Dim stor14 As String
Dim stor15 As String
Dim stor16 As String
Dim stor17 As String
Dim stor18 As String
Dim stor19 As String
Dim stor20 As String
Dim stor21 As String
Dim stor22 As String
Dim stor23 As String
Dim stor24 As String
Dim stor25 As String
Dim stor26 As String
Dim stor27 As String
Dim stor28 As String
stor1 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "fair") And InStr(Range("l" & i).Value2, "null") And InStr(Range("m" & i).Value2, "poor")
stor2 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "fair") And InStr(Range("l" & i).Value2, "null") And InStr(Range("m" & i).Value2, "fair")
stor3 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "fair") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "good")
stor4 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "fair") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "null")
stor5 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "poor")
stor6 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "fair")
stor7 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "good")
stor8 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "null")
stor9 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "poor")
stor10 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "fair")
stor11 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "good")
stor12 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "null")
stor13 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "poor")
stor14 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "fair")
stor15 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "good")
stor16 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "good") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "null")
stor17 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "poor")
stor18 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "fair")
stor19 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "good")
stor20 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "no ") And InStr(Range("m" & i).Value2, "null")
stor21 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "poor")
stor22 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "fair")
stor23 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "good")
stor24 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "yes") And InStr(Range("m" & i).Value2, "null")
stor25 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "poor")
stor26 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "fair")
stor27 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "good")
stor28 = InStr(Range("H" & i).Value2, "classified paved") And InStr(Range("i" & i).Value2, "null") And InStr(Range("j" & i).Value2, "no-side") And InStr(Range("k" & i).Value2, "null") And InStr(Range("l" & i).Value2, "nul") And InStr(Range("m" & i).Value2, "null")

        If stor1 > 0 Then
           Range("n" & i).Value2 = "2"
        Else
          Range("P" & i).Value2 = "x"
           End If


        Next i



    End Sub

Possible duplicate of [Odd behavior with boolean if statement in VBA](https://stackoverflow.com/q/24684955/11683) — GSerg, Oct 20 '18 at 08:54
Possible duplicate of [Odd behavior with boolean if statement in VBA](https://stackoverflow.com/questions/24684955/odd-behavior-with-boolean-if-statement-in-vba) — legoscia, Oct 20 '18 at 09:29
*Welcome to [so]!* This is a site where programmers *write their own code* and share a ***specific* problem** after trying to solve it on their own. Be sure to check out the [tour] (you'll earn your 1st badge!) and see "[ask]", and also the [help/on-topic] for more information about what's on topic on this site. If you have a *specific* problem with a certain section of your code, you can [edit] your post to share an **[mcve]** as well as sample data and some background info. Here are some [tips](//codeblog.jonskeet.uk/stack-overflow-question-checklist/) from the site's top user. Good Luck! — ashleedawg, Oct 20 '18 at 09:34

Vasily · Answer 1 · 2018-10-22T06:48:43.290

The issue is in wrong usage of variable types, you've defined stor1 as integer, but further, to stor1 the Boolean value was assigned, and of course it converted into integer (false into 0, true into -1). That is why you can't achieve ... If stor1 > 0 Then ... condition, stor1 will be always less or equal to 0.

Test below:

change you variable types to Boolean, and use ... If stor1 Then ...

Also, pay attention on defining of the variables inside loop, it is a bad practice.

Below is the code from previous version of post (example), I think it will be more readable and more flexible to achieve what you need with less rows of code:

Sub outcome123()

Dim vStr$, cl As Range
Dim rng As Range: Set rng = Range([H1], Cells(Rows.Count, "H").End(xlUp))

For Each cl In rng

    vStr = LCase(cl.Value2 & "|" & _
                 Range("i" & cl.Row).Value2 & "|" & _
                 Range("j" & cl.Row).Value2 & "|" & _
                 Range("k" & cl.Row).Value2 & "|" & _
                 Range("l" & cl.Row).Value2 & "|" & _
                 Range("m" & cl.Row).Value2)

    If vStr Like "*earth*|*none*|*both sides*|*poor*|*no*|*poor*" Then

        Range("n" & cl.Row).Value2 = "5"
    Else
        Range("n" & cl.Row).Value2 = "X"
    End If

Next cl

End Sub

Although your effort in cleaning up questions is appreciated, it's equally important to ensure that new users are aware of the standards for posting questions, and in this case the question should have been left unedited, the OP given some links like [ask] and [mcve] and [help/on-topic], downvoted, and if not fixed then VTC. The OP would have been unable to format that as code because the sites enforces a text-to-code ratio for new users... (2k rep min?) Furthermore, **answering** low quality questions hurts us all and the site in general, in a number of ways. — ashleedawg, Oct 20 '18 at 09:33
Actually the platform is a bit confusing to new users on where to add the question and the code but thanks you answered.My problem is that am using instr to compare the data in excel to that in vba and if it matches ,a 2 is printed else x is printed for no match found.The problem is that in the if else,the truth condition is not executed but instead the else condition is executed..What might be the probem — mohadennis, Oct 20 '18 at 12:02

Excel VBA If Else

1 Answers1