Application-defined or object-defined error (1004) in With

[michaeltsmith93]

Hi, in addition to the If statement causing error 13, I'm getting 1004 in the With statement. Thoughts?

Private Sub OK_Click()
Dim ERow As Long
Dim ws As Worksheet
Set ws = Worksheets("Tracker")


'find first empty row
ERow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'check for input
If (Trim(Me.ptmember.Value) Or Trim(Me.StudyRole.Value) Or Trim(Me.MatrixRole.Value) Or Trim(Me.Department.Value)) = "" Then
   MsgBox "Please fill all fields."
   Exit Sub
End If


With ws


[COLOR=#ff0000]    .Cells(ERow, 1).Value = Me.ptmember.Value[/COLOR]
    .Cells(ERow, 2).Value = Me.StudyRole.Value
    .Cells(ERow, 4).Value = Me.MatrixRole.Value
    .Cells(ERow, 5).Value = Me.Department.Value
    
End With


Me.ptmember.Value = ""
Me.StudyRole.Value = ""
Me.MatrixRole.Value = ""
Me.Department.Value = ""


End Sub

 

[Momentman]

More like

If Trim(Me.ptmember.Value)="" or Trim(Me.StudyRole.Value)=""...............

The With statement looks good, when it errors out, confirm what the Value of Erow is

 

[michaeltsmith93]

Yeah, I figured out the I statement, thank you.

It tells me that the value of ERow is 1048577. Thoughts?

 

[Fluff]

That means you have data in row 1048576, ie the vary last row of the sheet.

 

[Norie]

Try this to find the next empty row.

ERow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[michaeltsmith93]

...I'm so upset. I inherited this workbook from someone else. I have no idea why there was something down there.




That fixed it. Thank you, Fluff. You're a Godsend.

 

[Fluff]

You're welcome & thanks for the feedback