| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
end if without block if error
When this code is run i get the end if without block if error, but i
don't see what i am doing wrong. It highlights the very first "if" statement Private Sub btnSubmit_Click() Sheets("rewards tracker").Activate If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Mon" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Tue" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Wed" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(18) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Thu" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Fri" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Sat" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
end if without block if error
VBA has two different forms of the If-Then statement...
First Form =============== If <Logical Then <Code Second Form =============== If <Logical Then <Code End If and you cannot mix them. You have mixed them. Your opening If statement (you also do this in your ElseIf statement)... If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 contains <Code after the Then statement... there can be no linked ElseIf nor End If statements with it. VBA assumes everything after your opening If statement is independent lines of code... it is therefore choking on the Next statements below it. Just move your For-Next statement out of the If-Then single line statement and put it on its own line immediately below the If-Then statement... If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step <<rest of your code and do the same for the ElseIf statement also. Rick "stewart" wrote in message ups.com... When this code is run i get the end if without block if error, but i don't see what i am doing wrong. It highlights the very first "if" statement Private Sub btnSubmit_Click() Sheets("rewards tracker").Activate If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Mon" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Tue" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Wed" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(18) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Thu" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Fri" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Sat" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
end if without block if error
On Sep 16, 11:46 am, "Rick Rothstein \(MVP - VB\)"
wrote: VBA has two different forms of the If-Then statement... First Form =============== If <Logical Then <Code Second Form =============== If <Logical Then <Code End If and you cannot mix them. You have mixed them. Your opening If statement (you also do this in your ElseIf statement)... If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 contains <Code after the Then statement... there can be no linked ElseIf nor End If statements with it. VBA assumes everything after your opening If statement is independent lines of code... it is therefore choking on the Next statements below it. Just move your For-Next statement out of the If-Then single line statement and put it on its own line immediately below the If-Then statement... If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step <<rest of your code and do the same for the ElseIf statement also. Rick "stewart" wrote in message ups.com... When this code is run i get the end if without block if error, but i don't see what i am doing wrong. It highlights the very first "if" statement Private Sub btnSubmit_Click() Sheets("rewards tracker").Activate If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Mon" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Tue" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Wed" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(18) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Thu" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Fri" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Sat" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i End If End Sub That did the trick. Thanks, especially for explaining it. It really helps. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
end if without block if error
On Sep 16, 12:21 pm, "Don Guillett" wrote:
It appears that there is only one variable so try this Sub chooseday() Select Case UCase(Left(Range("k3"), 3)) Case "THU": x = 3 Case "MON", "THU": x = 8 Case "TUE", "SAT": x = 13 Case "WED": x = 18 Case Else End Select 'MsgBox x For i = 1 To txtQty.Value txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select 'variable on below line Selection.Cells(x) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i End Sub This can and probably should be further refined to REMOVE the selections if NOT needed. Maybe?? Sub chooseday1() Select Case UCase(Left(Range("k3"), 3)) Case "THU": x = 3 Case "MON", "THU": x = 8 Case "TUE", "SAT": x = 13 Case "WED": x = 18 Case Else End Select 'MsgBox x For i = 1 To txtQty.Value txtholder.Value = Me.Controls("txtNum" & i) With Rows(txtholder.Value + 4) .Cells(x) = Me.Controls("txtRew" & i) .Offset(0, 1) = Me.Controls("txtTot" & i) .Offset(0, 3) = Me.Controls("txtNew" & i).Value End With Next i End Sub ============== -- Don Guillett Microsoft MVP Excel SalesAid Software "stewart" wrote in message ups.com... When this code is run i get the end if without block if error, but i don't see what i am doing wrong. It highlights the very first "if" statement Private Sub btnSubmit_Click() Sheets("rewards tracker").Activate If Left(cmbDate.Value, 3) = "Sun" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Mon" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Tue" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Wed" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 4).Select Selection.Cells(18) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Thu" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(3) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Fri" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(8) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i ElseIf Left(cmbDate.Value, 3) = "Sat" Then For i = 1 To txtQty.Value Step 1 txtholder.Value = Me.Controls("txtNum" & i).Value Rows(txtholder.Value + 108).Select Selection.Cells(13) = Me.Controls("txtRew" & i).Value ActiveCell.Offset(0, 1) = Me.Controls("txtTot" & i).Value ActiveCell.Offset(0, 3) = Me.Controls("txtNew" & i).Value Next i End If End Sub thank you. I was just working on condensing the code and this really helped. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| End If without Block If Error Help | Excel Programming | |||
| Compile error: End If without block IF | Excel Programming | |||
| VBA error - End If without Block If | Excel Worksheet Functions | |||
| Error - End If Without Block | Excel Programming | |||
| Error 91, Object var or With block not set | Excel Programming | |||
Linear Mode
