VBA code stopped working, can't figure out what went wrong

[dlmoore99]

I have a userform that I've been developing for a few weeks now. My Private Sub cmdADD_Click() button will not operate anymore and debugger window appears when clicked. When I try to debug it goes to this line of code: .Cells(iRow, 1).Value = Me.txtDate.Value. Button was working great, but then stopped and I can't figure out why. Can someone please assist. Thanks in advance. Here is the code

Private Sub cmdADD_Click()
Dim iRow As Long
Dim LstRw As Long
Dim ws As Worksheet
Set ws = Worksheets("TGT")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.txtTime.Value) = "" Then
  Me.txtTime.SetFocus
  MsgBox "Please enter complete Target data"
  Exit Sub

End If

With ws
[COLOR=#ff0000]  .Cells(iRow, 1).Value = Me.txtDate.Value[/COLOR]
  .Cells(iRow, 2).Value = Me.txtTime.Value
  .Cells(iRow, 3).Value = Me.txtZulu.Value
  .Cells(iRow, 5).Value = Me.cboTGTBlock.Value & Me.txtTGTNo.Value
  .Cells(iRow, 6).Value = Me.cboPOO_GridZone.Value & Me.cboPOO_100K.Value & Me.txtPOO_East.Value & Me.txtPOO_North.Value
  .Cells(iRow, 7).Value = Me.txtPOO_Alt.Value
  .Cells(iRow, 8).Value = Me.cboPOI_GridZone.Value & Me.cboPOI_100K.Value & Me.txtPOI_East.Value & Me.txtPOI_North.Value
  .Cells(iRow, 9).Value = Me.txtPOI_Alt.Value
  .Cells(iRow, 10).Value = Me.txtRange.Value
  .Cells(iRow, 11).Value = Me.txtRCS.Value
  .Cells(iRow, 12).Value = Me.txtVel.Value
  .Cells(iRow, 13).Value = Me.cboType.Value
  .Cells(iRow, 14).Value = Me.txtComments.Value
  .Cells(iRow, 16).Value = Me.cboZulu.Value

End With
'clear the data
Me.txtTime.Value = ""
Me.txtZulu.Value = ""
Me.txtTGTNo.Value = Me.txtTGTNo.Value + 1
Me.txtPOO_East.Value = ""
Me.txtPOO_North.Value = ""
Me.txtPOO_Alt.Value = ""
Me.txtPOI_East.Value = ""
Me.txtPOI_North.Value = ""
Me.txtPOI_Alt.Value = ""
Me.txtRange.Value = ""
Me.txtRCS.Value = ""
Me.txtVel.Value = ""
Me.cboType.Value = ""
Me.txtComments.Value = ""
Me.txtTime.SetFocus
End Sub

Private Sub cmdCancel_Click()
Application.Visible = True
Me.Hide
End Sub
Private Sub cmdClear_Click()
Dim answer As VbMsgBoxResult
    answer = MsgBox("Are you sure you want to clear Target Data?", vbYesNo, "Clear Target Data")
    If answer = vbYes Then
        Me.txtTime.Value = ""
        Me.cboZulu.Value = ""
        Me.txtZulu.Value = ""
        Me.txtPOO_East.Value = ""
        Me.txtPOO_North.Value = ""
        Me.txtPOO_Alt.Value = ""
        Me.txtPOI_East.Value = ""
        Me.txtPOI_North.Value = ""
        Me.txtPOI_Alt.Value = ""
        Me.txtRange.Value = ""
        Me.txtRCS.Value = ""
        Me.txtVel.Value = ""
        Me.cboType.Value = ""
        Me.txtComments.Value = ""
    End If
    
End Sub
Private Sub cmdShowWb_Click()
Application.Visible = True
Me.Hide
End Sub

Private Sub Image1_Click()
End Sub

Private Sub Label2_Click()
End Sub

Private Sub Label5_Click()
End Sub

Private Sub ListBox1_Click()
End Sub

Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "mm/dd/yyyy")
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    'use a button to close user form and not the windows "X"
    MsgBox "Please use the Cancel Target button to close the form.", , "Close Form"
End Sub

Private Sub txtRCS_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim dblNum As Double
    If IsNumeric(Me.txtRCS.Value) Then
        dblNum = Me.txtRCS.Value
        Me.txtRCS.Value = Format(dblNum, "-00")
    End If
End Sub

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim dblNum As Double
    If IsNumeric(Me.txtTime.Value) Then
       dblNum = Me.txtTime.Value
       Me.txtTime.Value = Format(dblNum, "00:00")
    End If
      
End Sub

Private Sub cboZulu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(Me.cboZulu.Value) Then
        dblNum = Me.cboZulu.Value
        Me.cboZulu.Value = Format(dblNum, "00:00")
    End If
    
    If IsNumeric(Me.txtZulu.Value) Then
        dblNum = Me.txtZulu.Value
        Me.txtZulu.Value = Format(dblNum, "00:00")
    End If
    
    Me.txtZulu.Value = Val(Me.txtTime.Value) + Val(Me.cboZulu.Value)
    
End Sub

Private Sub cboZulu_updateform()
    
End Sub

 

[NeonRedSharpie]

If you put either debug.print(iRow), or msgbox(iRow), before the "with ws" block, what do you get?

Form looking at the first few lines I'd hazard a guess that it can't find a row and the msgbox will give you an error. But that's just a first debug.

 

[Fluff]

What error message did you get? & what was the value of iRow?

Also when posting code please use code tags, the # icon in the reply window.

 

[dlmoore99]

still get same debugger window with debug.print(iRow), and get msgbox window with 1048577 then to debugger window and still pointing to .Cells(iRow, 1).Value = Me.txtDate.Value

 

[Fluff]

and get msgbox window with 1048577

That's why you have a problem.
Your code is trying to add the data to a row that doesn't exist.
Somewhere in your sheet you have a value in row 1048576, which is the very last row.

 

[dlmoore99]

Found it, not sure how formula's got entered into those cells, but that did the trip, thank you again Fluff, you've helped me more than once. Thank you NeonRedSharpie, I'll try and remember that msgbox trick. I'm still trying to learn this VBA code.

 

[Fluff]

Glad we could help & thanks for the feedback