If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Excel > End If without block if

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,295
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
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
Reply With Quote
Reply

Tags
end if without block if, excel 2003, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On