Why has my code stopped working?

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
All

The intent of this code is to find the last filled cell in column 16 and move onto the next column, gather all the information in that row from columns 1-15 and input them in a form. It was working fine yesterday, now all of the sudden it stopped working. It always goes back to the first row. The only thing I changed was coping over histortic data into the columns. Now even if I erase it, it still wont move onto the next row once the cell 16 is filled in.

Private Sub cmbsubmit_Click()


Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")

'Find the last non-blank cell in column P (Leadman Initials)
iRow = Cells(Rows.Count, 16).End(xlUp).Row + 1


'check for a needed information
If Trim(Me.txtleadmaninitials.Value) = "" Then
Me.txtleadmaninitials.SetFocus
MsgBox "Please enter Leadman Initials"
Exit Sub
ElseIf Trim(Me.txtdecptfailure.Value) = "" Then
Me.txtdecptfailure.SetFocus
MsgBox "Please enter Detailed Description of Failure"
Exit Sub
ElseIf Trim(Me.txtstage.Value) = "" Then
Me.txtstage.SetFocus
MsgBox "Please enter Which Stage issue originated at"
Exit Sub
ElseIf Trim(Me.txtroot.Value) = "" Then
Me.txtroot.SetFocus
MsgBox "Please enter root cause"
Exit Sub
ElseIf Trim(Me.txtsolution.Value) = "" Then
Me.txtsolution.SetFocus
MsgBox "Please enter solution initiated"
Exit Sub
Else
End If


With ws
.Cells(iRow, 16).Value = Me.txtleadmaninitials.Value
.Cells(iRow, 17).Value = Me.txtstage.Value
.Cells(iRow, 18).Value = Me.txtop.Value
.Cells(iRow, 19).Value = Me.txtdecptfailure.Value
.Cells(iRow, 20).Value = Me.txtroot.Value
.Cells(iRow, 21).Value = Me.txtsolution.Value
.Cells(iRow, 22).Value = Me.txtvndrname.Value
.Cells(iRow, 23).Value = Me.txtcmptpart.Value
End With

iRow = Cells(Rows.Count, 16).End(xlUp).Row + 1




Me.txtjo.Value = ""
Me.txtdte.Value = ""
Me.txtmdl.Value = ""
Me.txtsrl.Value = ""
Me.txttchn.Value = ""
Me.txtep.Value = ""
Me.txthtvlt.Value = ""
Me.txtwt.Value = ""
Me.txtfailedsystem.Value = ""
Me.textfailuredescription.Value = ""
Me.txttimetofix.Value = ""
Me.txtMECABBYPASS.Value = ""
Me.txtRprcmnt.Value = ""
Me.txtfix.Value = ""
Me.txtdynotechinitials.Value = ""
Me.txtleadmaninitials.Value = ""
Me.txtstage.Value = ""
Me.txtop.Value = ""
Me.txtdecptfailure.Value = ""
Me.txtroot.Value = ""
Me.txtvndrname.Value = ""
Me.txtcmptpart.Value = ""
Me.txtsolution.Value = ""
With ws
Me.txtjo.Value = .Cells(iRow, 1).Value
Me.txtdte.Value = .Cells(iRow, 2).Value
Me.txtmdl.Value = .Cells(iRow, 3).Value
Me.txtsrl.Value = .Cells(iRow, 4).Value
Me.txttchn.Value = .Cells(iRow, 5).Value
Me.txtep.Value = .Cells(iRow, 6).Value
Me.txthtvlt.Value = .Cells(iRow, 7).Value
Me.txtwt.Value = .Cells(iRow, 8).Value
Me.txtfailedsystem.Value = .Cells(iRow, 9).Value
Me.textfailuredescription.Value = .Cells(iRow, 10).Value
Me.txttimetofix.Value = .Cells(iRow, 11).Value
Me.txtMECABBYPASS.Value = .Cells(iRow, 12).Value
Me.txtRprcmnt.Value = .Cells(iRow, 13).Value
Me.txtfix.Value = .Cells(iRow, 14).Value
Me.txtdynotechinitials.Value = .Cells(iRow, 15).Value
End With
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Lucky you, you're done for the day"
Exit Sub
End If
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
Some extra information, when I have the tab DynoRepairData open on the screen it works fine, but if I have another tab open it doesn't.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,775
Office Version
365
Platform
Windows
Try
Code:
iRow = [COLOR=#ff0000]ws.[/COLOR]Cells(Rows.Count, 16).End(xlUp).Row + 1
Added
Without the ws reference irow will calculate on the active sheet, not the Dyno sheet
 
Last edited:

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
Try
Code:
iRow = [COLOR=#ff0000]ws.[/COLOR]Cells(Rows.Count, 16).End(xlUp).Row + 1
Added
Without the ws reference irow will calculate on the active sheet, not the Dyno sheet
Awesome, that works perfectly. Amazing something so simple can throw everything off.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,775
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,089,785
Messages
5,410,398
Members
403,316
Latest member
samhadian

This Week's Hot Topics

Top