locked
End if without Block if error Ms Excel 2013 RRS feed

  • Question

  • Hi

    I am new to VBA and i am not very good with it.

    i am having an error with the following code: Block If without end if

    here is my code:

    Sub Recalculateplan()
    
    Var = Sheets("Information").Cells(4, 2).Value
    
    If (Var = "$H$3") Then:
    Output = "The Cheapest Plan Is Strawberry at " & Range("H3").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    
    If (Var = "$H$4") Then:
    Output = "The Cheapest Plan Is Banana at " & Range("H4").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    
    If (Var = "$H$5") Then:
    Output = "The Cheapest Plan Is Apple at " & Range("H5").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    
    If (Var = "$H$6") Then:
    Output = "The Cheapest Plan Is Kiwi at " & Range("H6").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    End Sub

    Thank you in anticipation for your support.

    Emersont1

    Tuesday, March 4, 2014 6:05 PM

Answers

  • Re:  End If error

    1.  Remove the colon after each instance of the word "Then"

    2.  Also, "Var" is used by Excel as the name of a function.
         Change Var to something else.

    3.  Add the line "Option Explicit"  as the first line in the code module and declare all variables...
         Dim vVar as Variant
         Dim Output as String

    4.  You don't need "( )" when using a Msgbox unless you are returning a value from it...
         Msgbox Output  works just fine.

    '---
    Jim Cone
    Portland, Oregon USAhttps://goo.gl/IUQUN2 (Dropbox)

    (free & commercial excel add-ins & workbooks)



    • Marked as answer by Emersont1 Wednesday, March 5, 2014 7:24 AM
    • Edited by James Cone Thursday, October 20, 2016 2:47 PM
    Tuesday, March 4, 2014 7:59 PM

All replies

  • Re:  End If error

    1.  Remove the colon after each instance of the word "Then"

    2.  Also, "Var" is used by Excel as the name of a function.
         Change Var to something else.

    3.  Add the line "Option Explicit"  as the first line in the code module and declare all variables...
         Dim vVar as Variant
         Dim Output as String

    4.  You don't need "( )" when using a Msgbox unless you are returning a value from it...
         Msgbox Output  works just fine.

    '---
    Jim Cone
    Portland, Oregon USAhttps://goo.gl/IUQUN2 (Dropbox)

    (free & commercial excel add-ins & workbooks)



    • Marked as answer by Emersont1 Wednesday, March 5, 2014 7:24 AM
    • Edited by James Cone Thursday, October 20, 2016 2:47 PM
    Tuesday, March 4, 2014 7:59 PM
  • The reason you are getting that error is because of the ":" after "Then:". Try this:

    Sub Recalculateplan()
    Var = Sheets("Information").Cells(4, 2).Value
    If (Var = "$H$3") Then
    Output = "The Cheapest Plan Is Strawberry at " & Range("H3").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    If (Var = "$H$4") Then
    Output = "The Cheapest Plan Is Banana at " & Range("H4").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    If (Var = "$H$5") Then
    Output = "The Cheapest Plan Is Apple at " & Range("H5").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    If (Var = "$H$6") Then
    Output = "The Cheapest Plan Is Kiwi at " & Range("H6").Value
    MsgBox (Output)
    'B11
    Range("B11").Value = Output
    End If
    End Sub


    VBA Programming Service
    Tuesday, March 4, 2014 8:11 PM