Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    End If without block if

    So, I am at a loss. I am new to VB and can’t seem to figure out why I keep getting this End If error. I keep counting up the Ifs and End Ifs, but I still seem to get this error. Any help would be much appreciated.


    Here is the code that keeps giving me problems:

    Sub Macro2()


    '
    '*************************************
    'This Macro sorts all the stocks into Sheets by Price
    '*************************************

    '*************************************
    'Sort Colums
    '*************************************
    Sheets("Sheet1").Select
    Columns("A:K").Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    '*************************************
    'Add 10 New Sheets and rename them
    '*************************************
    Sheets("Sheet1").Select
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add

    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Master"
    Sheets("Sheet2").Name = "0-5"
    Sheets("Sheet3").Name = "5-10"
    Sheets("Sheet4").Name = "10-15"
    Sheets("Sheet5").Name = "15-20"
    Sheets("Sheet6").Name = "20-25"
    Sheets("Sheet7").Name = "25-30"
    Sheets("Sheet8").Name = "30-35"
    Sheets("Sheet9").Name = "35-40"
    Sheets("Sheet10").Name = "40-45"
    Sheets("Sheet11").Name = "45-50"

    '*************************************
    'Loop Through all the cells evaluating them by price
    '*************************************
    Sheets("Master").Select

    For Counter = 1 To 7000
    Set curCell = Worksheets("Master").Cells(Counter, 9)
    If Abs(curCell.Value) < 5 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("0-5").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 5 And Abs(curCell.Value) < 10 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("5-10").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 10 And Abs(curCell.Value) < 15 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("10-15").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 15 And Abs(curCell.Value) < 20 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("15-20").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 20 And Abs(curCell.Value) < 25 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("20-25").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 25 And Abs(curCell.Value) < 30 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("25-30").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 30 And Abs(curCell.Value) < 35 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("30-35").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 35 And Abs(curCell.Value) < 40 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("35-40").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 40 And Abs(curCell.Value) < 45 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("40-45").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    If Abs(curCell.Value) >= 45 And Abs(curCell.Value) < 50 Then Sheets("Master").Select
    Rows("Counter: Counter").Select
    Selection.Copy
    Sheets("45-50").Select
    Rows("Counter: Counter").Select
    ActiveSheet.Paste
    End If

    Next Counter

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    In VBA the if statement can take several forms
    If boolean expression then
    code block
    Else
    alternative code block
    Endif

    OR

    if boolean expression then sentence
    .. Following code is deemed not part of the if expression so using an Endif throws an error

    The solution is have no code on the same liberalization as the then. Its good practice to always ose the first style of if statement even if there is only one lied if code that is conditional
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •