Good Morning,
I am using the VB below to fill a form.
The Vb should be looking at column 14 to see if it is populated.
If it is it is meant to go down each line until it finds column 14 blank then populate the form with that lines data. Instead it goes down to the bottom and then uses the last line of data. how do i go about fixing this please???? I use this to loop through 3 different departments and am stuck.
All help is appreciated
I am using the VB below to fill a form.
The Vb should be looking at column 14 to see if it is populated.
If it is it is meant to go down each line until it finds column 14 blank then populate the form with that lines data. Instead it goes down to the bottom and then uses the last line of data. how do i go about fixing this please???? I use this to loop through 3 different departments and am stuck.
All help is appreciated
Rich (BB code):
'Open file
Dim gotcha As Boolean
'Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\Data.xls" 'Home
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("Ashley Longman")
If UserForm2.ComboBox1 = "Ashley Longman" Then
Pword = InputBox("Please enter password")
If Pword = "ticket" Then
ActiveWorkbook.Sheets("Ashley Longman").Activate
End If
Unload Me
For i = 3 To 65000
If Cells(i, 1) = "" Then Exit For
If Cells(i, 1) <> "" And Cells(i, 14) = "" Then
gotcha = True
UserForm1.Show False
UserForm1.txtdate.Value = Cells(i, 1)
UserForm1.TextBox1.Value = Cells(i, 3)
UserForm1.TextBox13.Value = Cells(i, 2)
UserForm1.TextBox2.Value = Cells(i, 4)
UserForm1.TextBox3.Value = Cells(i, 7)
UserForm1.TextBox6.Value = Cells(i, 5)
UserForm1.TextBox5.Value = Cells(i, 6)
UserForm1.TextBox4.Value = Cells(i, 8)
UserForm1.TextBox7.Value = Cells(i, 9)
UserForm1.TextBox8.Value = Cells(i, 10)
UserForm1.TextBox9.Value = Cells(i, 13)
UserForm1.TextBox14.Value = Cells(i, 11)
UserForm1.TextBox15.Value = Cells(i, 12)
UserForm1.TextBox11.Value = Cells(i, 4)
With UserForm1.CBoxAdd
.AddItem "Ash Young"
.AddItem "Chris Holcombe"
.AddItem "Chris Nash"
.AddItem "David Watson"
.AddItem "Gary Littler"
.AddItem "Kris Harwood"
.AddItem "Michelle Porter"
.AddItem "Tim Nickson"
.AddItem "Zenny Asghar"
End With
With UserForm1.txtdate
.Enabled = False
End With
End If
Next
If gotcha = False Then
MsgBox "Nothing to Update"
ActiveWindow.Close (True)
End If