VBA not finding next empty row due to formula

[Kusaywa]

I've created a simple checkbook register where there is a formula in column G that totals E, F & G.
I also created a form.
Problem is, after hitting submit, it enters the entry at row 101 due to the formula in rows 2-100 (I think).
Is there a way to have it select next empty row and exclude the formula in G? Columns A - F have no formula.
Thanks in advance.

Here's the Form info:

Private Sub Submit_Click()

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

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
'If Trim(Me.txtPart.Value) = "" Then
  'Me.txtPart.SetFocus
  'MsgBox "Please enter a part number"
  'Exit Sub
'End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 2).Value = [Text(Now(), "MM-DD-YYYY")]
  .Cells(iRow, 3).Value = Me.TextBox1.Value
  .Cells(iRow, 4).Value = Me.TextBox2.Value
  .Cells(iRow, 5).Value = Me.TextBox3.Value
  .Cells(iRow, 6).Value = Me.TextBox4.Value
'  .Protect Password:="password"
End With

Unload Me

'clear the data
'Me.TextBox1.Value = ""
'Me.TextBox2.Value = ""
'Me.TextBox3.Value = ""
'Me.TextBox4.Value = ""
'Me.TextBox1.SetFocus

End Sub

 

[Micron]

maybe Range("A" & Rows.count).End(xlUp).Row + 1

Re your code comments: You're not really finding the first empty row, you're finding the last row that's not empty. Insert a row before you edit the code and see that the result will increase by one as expected, but the first row is empty, so the code doesn't really find the first empty row.

 

[Kusaywa]

This is the part of the code I believe doesn't do what I want...

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 

[Micron]

What I posted should work since you say there are no formulas in column A.

 

[Kusaywa]

I'm getting the following error with "Row" in your code highlighted?

 

[Alex Blakenburg]

To use @Micron's suggestion the full line should look like this:
(note you don't have the assignment part "iRow =" and you have also missed the "+")

iRow = Range("A" & Rows.count).End(xlUp).Row + 1

If you want to stick with your original, you could limit the search range excluding column G

iRow = ws.Columns("A:F").Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 

[Micron]

My apologies. When I posted about finding "first empty row" I imagined you'd make the connection with the line that needed to be replaced. Sometimes I assume too much. Alex is spot on re my intentions.

 

[Kusaywa]

Thank You Micron & Alex, both solutions worked.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.