Form fill always using last line of Data

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi,

Maybe this:

Code:
    lRow = Range("N" & Rows.Count).End(xlUp).Row
    For i = 3 To lRow
        If Cells(i, 1).Value = "" Then Exit For
        If Cells(i, 1).Value <> "" And Cells(i, 14).Value = "" Then
            UserForm1.txtdate.Value = Cells(lRow, 1)
            UserForm1.TextBox1.Value = Cells(lRow, 3)
            UserForm1.TextBox13.Value = Cells(lRow, 2)
            UserForm1.TextBox2.Value = Cells(lRow, 4)
            UserForm1.TextBox3.Value = Cells(lRow, 7)
            UserForm1.TextBox6.Value = Cells(lRow, 5)
            UserForm1.TextBox5.Value = Cells(lRow, 6)
            UserForm1.TextBox4.Value = Cells(lRow, 8)
            UserForm1.TextBox7.Value = Cells(lRow, 9)
            UserForm1.TextBox8.Value = Cells(lRow, 10)
            UserForm1.TextBox9.Value = Cells(lRow, 13)
            UserForm1.TextBox14.Value = Cells(lRow, 11)
            UserForm1.TextBox15.Value = Cells(lRow, 12)
            UserForm1.TextBox11.Value = Cells(lRow, 4)
        End If
    Next
 
Upvote 0
Sorry,

The vba is saying there is no data to update when there is.
I have included the full vba for the form to load to see if that would make any difference

I hope this helps

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
        
    lRow = Range("N" & Rows.Count).End(xlUp).Row
    For i = 3 To lRow
        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
 
End If
gotcha = False
'If Andy Mellor - callcentre enquiry
If UserForm2.ComboBox1 = "Andrew Mellor" Then
        Pword = InputBox("Please enter password")
        If Pword = "judy" Then
ActiveWorkbook.Sheets("Andrew Mellor").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
    lRow = Range("N" & Rows.Count).End(xlUp).Row
    For i = 3 To lRow
        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.TextBox15.Value = Cells(i, 12)
            UserForm1.TextBox14.Value = Cells(i, 11)
            UserForm1.TextBox11.Value = Cells(i, 4)
      
         With UserForm1.CBoxAdd
             .AddItem "Angela Rubbathan"
             .AddItem "Ash Young"
             .AddItem "Asif Gani"
             .AddItem "Beena Singh"
             .AddItem "Chris McKee"
             .AddItem "Hayley Moss"
             .AddItem "Matthew Andrew"
             .AddItem "Maryam Hessavi"
             .AddItem "Michelle Hilton"
             .AddItem "Sarah Jeffery"
             .AddItem "Sushil Bhakta"
         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
 
End If
gotcha = False
'If Christine Whitty - callcentre enquiry
If UserForm2.ComboBox1 = "Christine Whitty" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
ActiveWorkbook.Sheets("Christine Whitty").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
        lRow = Range("N" & Rows.Count).End(xlUp).Row
        For i = 3 To lRow
        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 "Gavin Mazza"
             .AddItem "Hannah Hill"
             .AddItem "Jeannette Yates"
             .AddItem "Joshim Khan"
             .AddItem "Louise McHugh-Gunson"
             .AddItem "Mark Haslam"
             .AddItem "Mark Taylor"
             .AddItem "Mark Weikert"
             .AddItem "Nicolas Sampson"
             .AddItem "Samantha Nixon"
        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
End If
End Sub
 
Upvote 0
Based on what I can see, if anything in Range("A3:A" & lRow) is blank then exit the For loop, meaning gotcha is never true (lRow in this case is the very bottom of column N).

So say the bottom of your data in col N is row 5,000 then check to see if anything between A3 and A5000 is blank.

Is this correct?
 
Upvote 0
Cheers

In essense the VB should be checking columnA for any data registered.
If there is anything in column A it should move accross to column 14 to see if there is any info registered. If colum A is populated and if column 14 is blank it should then populate the info between columns 1 - 13 as per the info to load USERFORM1. It seems to be filling the form from the last line instead of the first line from the top of the sheet

Hope this helps
Gavin
 
Upvote 0
Hmm, that's weird.

When I tested it it worked fine:

Cells A6 contained a blank so it skipped to A7 which didnt contain a blank AND N7 was blank so it popped in the info on that row then moved onto the next row.

I can't see how it is adding it to the bottom of the data rather than where there are blanks as the for loop runs from 3 to lRow stepping upwards.

Is the last cell in column A the same row as the last cell in column N?
 
Upvote 0
Hi
I have just re tried it and it still says "nothing to Update"

Generally the data is filled from the top of the sheet row 3 downwards

So ideally the VB should work downwards check A3 downwards for a cell that isnt blank, then move across and check column N. If N is blank it should upload the form.

Is there anyway i can upload the file for you to try?

Cheers
Gavin:confused:
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,270
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top