end if without block if error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stewart - 16 Sep 2007 16:19 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 16 Sep 2007 16:46 GMT

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

> 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"
[quoted text clipped - 70 lines]
>
> End Sub

Reply to this Message

stewart - 16 Sep 2007 16:57 GMT

On Sep 16, 11:46 am, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMn...@NOSPAMcomcast.net> wrote:

> VBA has two different forms of the If-Then statement...
>
[quoted text clipped - 102 lines]
>
> > End Sub

That did the trick. Thanks, especially for explaining it. It really
helps.

Reply to this Message

Don Guillett - 16 Sep 2007 17:21 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.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"
[quoted text clipped - 70 lines]
>
> End Sub

Reply to this Message

stewart - 17 Sep 2007 00:01 GMT

> It appears that there is only one variable so try this
>
[quoted text clipped - 120 lines]
>
> > End Sub

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

Reply to this Message