end if without block if error

  • Thread starter Thread starter stewart
  • Start date Start date
S

stewart

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
 
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
 
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
Click to expand...

That did the trick. Thanks, especially for explaining it. It really
helps.
 
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
==============
 
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
==============
Click to expand...

thank you. I was just working on condensing the code and this really
helped.
 
Back
Top