end if without block if error

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

stewart

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.
 
D

Don Guillett

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
==============
 
S

stewart

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

leading zeros using ActiveCell.Offset().value to insert row and value 84
VBA Coding Help for Beginner 0
Type Mismatch Error 3
Save a userform that has been created programmatically at runtime? 2
Duplicate declaration in scope 2
Runtime error 91: Object variable or With block variable not set 8
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Variable Defined as Double is entered in cell with only 2 decimal places 2

Top