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 Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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
55,264
Office Version
  1. 365
Platform
  1. 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
55,264
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,329
Members
415,892
Latest member
ChloeLM

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
Top