Compile Error

[gmazza76]

I am trying to write some VB to pull info from 3 different sheets within 1 work book depending on what "OFFICE" ( Only 2 offices ) has been selected to fill a form.
The agent selects the "office" that then loads the form already filled with the last open enquiry from a Data file. Each "OFFICE" is saved on a seperate sheet within another workbook

I am using the following VB but keep getting a "Compile Error - For control variable in use" Is there an easier way of writing the code ??

'Open file
Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\Data.xls"
Dim myBook As Workbook

On Error Resume Next
Set myBook = Application.Workbooks("Data.xls")
On Error GoTo 0

'If Ash Longman - Call Centre Enquiry
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Ash Longman")
If ActiveCell.ComboBox1 = "Ash Longman" Then
ActiveWorkbook.Sheets("Ash Longman").Activate
Unload Me
UserForm1.Show False
For i = 3 To 65000
If Cells(i, 1) = "" Then Exit For
If Cells(i, 11) = "" Then
UserForm1.txtdate.Value = Cells(i, 1)
UserForm1.TextBox1.Value = Cells(i, 2)
UserForm1.TextBox2.Value = Cells(i, 3)
UserForm1.TextBox3.Value = Cells(i, 6)
UserForm1.TextBox6.Value = Cells(i, 4)
UserForm1.TextBox5.Value = Cells(i, 5)
UserForm1.TextBox4.Value = Cells(i, 7)
UserForm1.TextBox7.Value = Cells(i, 8)
UserForm1.TextBox8.Value = Cells(i, 9)
UserForm1.TextBox9.Value = Cells(i, 10)

With JEN
.AddItem "No"
.AddItem "Yes"
End With

With CBoxAdd
'change per office
.AddItem "David Watson"
.AddItem "Jeannette Yates"
.AddItem "Joshim Khan"
.AddItem "Mark Haslam"
.AddItem "Mark Taylor"
.AddItem "Mark Weikert"
.AddItem "Nicholas Sampson"

End With
With txtdate
.Enabled = False
End With


Else
'If Andy Morrell - callcentre enquiry
If ActiveCell.ComboBox1 = "Andy Morrell" Then
ActiveWorkbook.Sheets("Andy Morrell").Activate
Unload Me
UserForm1.Show False
For i = 3 To 65000
If Cells(i, 1) = "" Then Exit For
If Cells(i, 11) = "" Then
UserForm1.txtdate.Value = Cells(i, 1)
UserForm1.TextBox1.Value = Cells(i, 2)
UserForm1.TextBox2.Value = Cells(i, 3)
UserForm1.TextBox3.Value = Cells(i, 6)
UserForm1.TextBox6.Value = Cells(i, 4)
UserForm1.TextBox5.Value = Cells(i, 5)
UserForm1.TextBox4.Value = Cells(i, 7)
UserForm1.TextBox7.Value = Cells(i, 8)
UserForm1.TextBox8.Value = Cells(i, 9)
UserForm1.TextBox9.Value = Cells(i, 10)

With JEN
.AddItem "No"
.AddItem "Yes"
End With

With CBoxAdd
'change per office
.AddItem "David Watson"
.AddItem "Jeannette Yates"
.AddItem "Joshim Khan"
.AddItem "Mark Haslam"
.AddItem "Mark Taylor"
.AddItem "Mark Weikert"
.AddItem "Nicholas Sampson"

End With
With txtdate
.Enabled = False
End With

Else
'If Christine Whitty - callcentre enquiry
If ActiveCell.ComboBox1 = "Christine Whitty" Then
ActiveWorkbook.Sheets("Christine Whitty").Activate
Unload Me
UserForm1.Show False
For i = 3 To 65000
If Cells(i, 1) = "" Then Exit For
If Cells(i, 11) = "" Then
UserForm1.txtdate.Value = Cells(i, 1)
UserForm1.TextBox1.Value = Cells(i, 2)
UserForm1.TextBox2.Value = Cells(i, 3)
UserForm1.TextBox3.Value = Cells(i, 6)
UserForm1.TextBox6.Value = Cells(i, 4)
UserForm1.TextBox5.Value = Cells(i, 5)
UserForm1.TextBox4.Value = Cells(i, 7)
UserForm1.TextBox7.Value = Cells(i, 8)
UserForm1.TextBox8.Value = Cells(i, 9)
UserForm1.TextBox9.Value = Cells(i, 10)

With JEN
.AddItem "No"
.AddItem "Yes"
End With

With CBoxAdd
'change per office
.AddItem "David Watson"
.AddItem "Jeannette Yates"
.AddItem "Joshim Khan"
.AddItem "Mark Haslam"
.AddItem "Mark Taylor"
.AddItem "Mark Weikert"
.AddItem "Nicholas Sampson"

End With
With txtdate
.Enabled = False
End With

Exit Sub
MyError:
MsgBox "This CSM has no outstanding escalations"
Workbooks("Data.xls").Close SaveChanges:=True
End If
End If
End If
End Sub

 

[Ruddles]

A FOR loop must have a matching NEXT statement to tell VBA where the loop ends so that it can go and get the next value of the control variable (in this case i).

You start three FOR loops but don't end any of them, so when you try to start the second one, you're still executing the first one.

 

[GTO]

Hi there,

Please use code tags like: [code]...your code between the tags[/code] and some indenting. It will make the code much easier to read, both for yourself and anyone wanting to help.

If I am counting correctly, I believe you are missing two 'Next' for the bottom of the loops.

 

[GTO]

Oops, okay, maybe three.

 

[Ruddles]

They would have been easier to count if the code had been correctly indented and presented between CODE tags!

In fact if the code had been correctly indented it would have been obvious where the errors were, as it would have started creeping towards the right-hand side of the window instead of finishing neatly back in column 1 where it started.

 

[Weaver]

The original post asked if there was an easier way, so as well as what the others had to say:

Probably not smart programming to jump out of for loops either, if you can help it

You could determine the last row of actual data first

lastRow = cells(rows.count,1).end(xlup).row

then

For i = 3 To lastRow

Also, you can loop here

UserForm1.TextBox1.Value = Cells(i, 2)
UserForm1.TextBox2.Value = Cells(i, 3)
UserForm1.TextBox3.Value = Cells(i, 6)
UserForm1.TextBox6.Value = Cells(i, 4)
UserForm1.TextBox5.Value = Cells(i, 5)
UserForm1.TextBox4.Value = Cells(i, 7)
UserForm1.TextBox7.Value = Cells(i, 8)
UserForm1.TextBox8.Value = Cells(i, 9)
UserForm1.TextBox9.Value = Cells(i, 10)

can become

for j = 1 to 9
  UserForm1.controls("TextBox" & j).Value = Cells(i, j+1)
next j