Check if any values are already in the established FirstRow

[julhs]

Bear in mind that this is operating via a UserForm
A section of code within my "Add Enrty" code (below) on my UserForm establishes the First and last rows of a dynamic range.
It’s a dynamic range because every time a new entry is made, a new BLANK row is added.
What I’m trying to do is when that dynamic range is COMPLETELY BLANK and the first time data is added via the Userform that the 1st entry is made 3 rows BELOW the “FirstRow”. Then when subsequent entries are made that they are entered into the next available Empty/Blank row, so on & so on.

The section I can’t get right is the line; " ** This takes the values from the ComboBoxes/Text boxes on UserForm…."

Private Sub cmdAddEntry_Click()
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim LR1 As Long
    Dim ws As Worksheet
       Set ws = ThisWorkbook.ActiveSheet
             Application.ScreenUpdating = False
                '** Next section establishes; FirstRow, LastRow & LR1
      With ws
            FirstRow = .Columns("B:B").Find("Date", , xlFormulas, xlWhole, xlByRows, xlNext, False).Row + 3
            LastRow = .Columns("D:D").Find("Total received", , xlFormulas, xlWhole, xlByRows, xlNext, False).Row
            LR1 = .Range(.Cells(8, "A"), .Cells(LastRow - 1, "E")).Find("*", , xlValues, , xlByRows, xlPrevious).Row '*** Finds last USED row (col "A:E")
               '** Next section adds new blank row every time new data is added
         If LR1 = LastRow = 0 Then '(this needs to be 0 otherwise this doesn't work)
             .Range("A" & LR1 + 1 & ":E" & LR1 + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' *** Inserts new blank row into columns "A:E"
             .Range("A" & LR1 + 1 & ":E" & LR1 + 1).FillUp '*** Fills the existing formatting into the newly added row
           LastRow = LR1 + 1
      End If
          Debug.Print LastRow
          Debug.Print FirstRow
          Debug.Print LR1
              '** This takes the values from the ComboBoxes/Text boxes on UserForm and places them in the respective columns
          '?? With ws
         If FirstRow.Cells = "" Then ???????????????????
            .Cells(FirstRow, "B") = Format(Me.Calendar1.Value, "d/mmm/yy")
            .Cells(FirstRow, "C") = Me.txtInvNo.Value
            .Cells(FirstRow, "D") = Me.cmbPaymentFrom
            .Cells(FirstRow, "E") = Me.txtItemValue.Value
          '?? End If
        Else
              '**This works on its own,(but without "f FirstRow.Cells......" section above, BUT puts first value in row BELOW “FirstRow” (ei FirstRow +1)
            .Cells(LastRow, "B") = Format(Me.Calendar1.Value, "d/mmm/yy")
            .Cells(LastRow, "C") = Me.txtInvNo.Value
            .Cells(LastRow, "D") = Me.cmbPaymentFrom
            .Cells(LastRow, "E") = Me.txtItemValue.Value
        End If
         '?? End With
      End With
              '** This just resets everything on UserForm
           Calendar1 = ""
           txtDate = ""
           txtItemValue = ""
           txtInvNo = ""
           cmbPaymentFrom = ""
         Application.ScreenUpdating = True
  End Sub

 

[NoSparks]

Perhaps this will work for you

Private Sub cmdAddEntry_Click()
    Dim ws As Worksheet
    Dim FirstRow As Long, LastRow As Long, LR1 As Long
    Dim rng As Range
  
Set ws = ThisWorkbook.ActiveSheet

Application.ScreenUpdating = False

'** Next section establishes; FirstRow, LastRow & LR1
    With ws
        ' FirstRow
        Set rng = .Columns("B:B").Find("Date", , xlFormulas, xlWhole, xlByRows, xlNext, False)
        If Not rng Is Nothing Then
            FirstRow = rng.Row + 3
        Else
            MsgBox "Date not found"
            Exit Sub
        End If
      
        ' LastRow
        Set rng = .Columns("D:D").Find("Total received", , xlFormulas, xlWhole, xlByRows, xlNext, False)
        If Not rng Is Nothing Then
            LastRow = rng.Row
        Else
            MsgBox "Total received not found"
            Exit Sub
        End If
      
        ' LR1
        Set rng = .Cells(FirstRow, "A").Resize(LastRow - FirstRow, 5).Find("*", , xlValues, , xlByRows, xlPrevious)
        If Not rng Is Nothing Then
            LR1 = rng.Row + 1
        Else
            MsgBox "The dynamic range is COMPLETELY BLANK"
            LR1 = FirstRow
        End If
      
'** This takes the values from the ComboBoxes/Text boxes on UserForm and places them in the respective columns
        ' Write data to the sheet
        .Cells(LR1, "B") = Me.Calendar1.Value   '= Format(Me.Calendar1.Value, "d/mmm/yy")
        .Cells(LR1, "C") = Me.txtInvNo.Value
        .Cells(LR1, "D") = Me.cmbPaymentFrom
        .Cells(LR1, "E") = Me.txtItemValue.Value
      
        ' Insert new blank row into columns "A:E" right below newly written data IF NEEDED
        If LR1 = LastRow - 1 Then
            .Cells(LR1 + 1, "A").Resize(, 5).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    End With

Application.ScreenUpdating = True

'** This just resets everything on UserForm
    Calendar1 = ""
    txtDate = ""
    txtItemValue = ""
    txtInvNo = ""
    cmbPaymentFrom = ""
                   
End Sub

you may need to alter that Calendar line

 

[julhs]

For anybody reading this later; I EVENTUALLY sorted this query.
It turned out to be as simple as simple could be.
Replaced;
If FirstRow.Cells = "" Then
with
If FirstRow > LastRow Then
Tip is needed to use "Debug.Print, First Row/ LastRow ….... to figure out if FirstRow# was greater than LastRow #
Final code

Private Sub cmdAddEntry_Click()
 Dim FirstRow As Long
 Dim LastRow As Long
 Dim LR1 As Long
 Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
     Application.ScreenUpdating = False
'-------------------------------
         '** Next section establishes; FirstRow, LastRow & LR1
With ws
    FirstRow = .Columns("B:B").Find("Date", , xlFormulas, xlWhole, xlByRows, xlNext, False).Row ' + 3 Note, this has been removed from original code!!
    LastRow = .Columns("D:D").Find("Total received", , xlFormulas, xlWhole, xlByRows, xlNext, False).Row
    LR1 = .Range(.Cells(8, "A"), .Cells(LastRow - 1, "E")).Find("*", , xlValues, , xlByRows, xlPrevious).Row '*** Finds last USED row (col "A:E")
      ' Next section adds new blank row every time new data is added
   If LR1 = LastRow = 0 Then '(this needs to be 0 otherwise this doesn't work)
     .Range("A" & LR1 + 1 & ":E" & LR1 + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' *** Inserts new blank row into columns "A:E"
     .Range("A" & LR1 + 1 & ":E" & LR1 + 1).FillUp '*** Fills the existing formatting into the newly added row
   LastRow = LR1 + 1
   FirstRow = FirstRow + 3 'Note this is new addition
 End If
'---------------------------------------
'  To get row #
     Debug.Print LastRow
     Debug.Print FirstRow
     Debug.Print LR1
'---------------------------------------
      '** This takes the values from the ComboBoxes/Text boxes on UserForm and places them in the respective columns
   With ws
         'If FirstRow.Cells = "" Then   '**** Wasn’t working in original
   If FirstRow > LastRow Then ' This is the required correction to line above
    .Cells(FirstRow, "B") = Format(Me.Calendar1.Value, "d/mmm/yy")
    .Cells(FirstRow, "C") = Me.txtInvNo.Value
   .Cells(FirstRow, "D") = Me.cmbPaymentFrom
   .Cells(FirstRow, "E") = Me.txtItemValue.Value
 Else
      ' Places subsequent values into next EMPTY row
   .Cells(LastRow, "B") = Format(Me.Calendar1.Value, "d/mmm/yy")
   .Cells(LastRow, "C") = Me.txtInvNo.Value
   .Cells(LastRow, "D") = Me.cmbPaymentFrom
   .Cells(LastRow, "E") = Me.txtItemValue.Value
  End If
 End With
End With
             '** This just resets everything on UserForm
      Calendar1 = ""
      txtDate = ""
     txtItemValue = ""
     txtInvNo = ""
     cmbPaymentFrom = ""
Application.ScreenUpdating = True
End Sub

 

[julhs]

Thanks Nosparks for your response, only noticed it AFTER I posted my solution.
Took me from initial post to now to figure it out, gratifying that I did it.
Not run your suggestion; but will, and let you know the outcome.
julhs

 

[NoSparks]

Please do

 

[julhs]

Yours is certainly more compact than mine.
There seems to be a slight glitch for me.
Columns A & F have formulas in and LastRow Col D has "Total received" and in LastRow Col E I have =SUM($E$9:$E11)
When entry is made the formulas in Col A & F are not copied and “Sum” formula is not adjusting to take into account the newly added row.
So i.e. it becomes SUM($E$9:$E12)

 

[NoSparks]

OK, thanks.
FWIW.... I'd use an Excel table

Good luck with your project

 

[NoSparks]

Hello julhs
Don't know if this will be of interest to you or not, but I'd do something along the lines of this

Table setup at very beginning

Test_File.xlsm
	A	B	C	D	E	F
1
2
3	Header1	Date	Header3	Header4	Header5
4
5				Total received	0
6
7
Sheet1

UserForm command button code

Private Sub cmdAddEntry_Click()
    Dim ws As Worksheet
    Dim oLo As ListObject, oNewRow As ListRow
    
Set ws = ActiveWorkbook.ActiveSheet
Set oLo = ws.ListObjects("Table1")

With oLo
    If WorksheetFunction.CountA(.ListRows(.ListRows.Count).Range) > 1 Then
        ' it does so add a new row to use
        Set oNewRow = .ListRows.Add(AlwaysInsert:=True)
    End If
    ' write to last listrow from form
    With .ListRows(oLo.ListRows.Count).Range
        .Cells(1, 2).Value = Format(Me.Calendar1.Value, "d/mmm/yy")
        .Cells(1, 3).Value = Me.txtInvNo.Value
        .Cells(1, 4).Value = Me.cmbPaymentFrom
        .Cells(1, 5).Value = Me.txtItemValue.Value
    End With
End With

' Reset the UserForm
Calendar1 = ""
txtDate = ""
txtItemValue = ""
txtInvNo = ""
cmbPaymentFrom = ""

End Sub

and in a standard module a macro to reset the table

Sub ResetTable()

Dim oLo As ListObject
Dim response

response = MsgBox("Are you sure you want to reset the table?", vbQuestion + vbOKCancel, "RESET TABLE")
If response = vbOK Then

    Set oLo = ActiveSheet.ListObjects("Table1")
    
    'Delete all table rows except first row
      With oLo.DataBodyRange
        If .Rows.Count > 1 Then
          .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
      End With
    
    'Clear out data from first table row (retaining formulas)
      On Error Resume Next      'in case no formulas
      oLo.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
      On Error GoTo 0           're-enable error notification

End If

End Sub

 

[julhs]

Really appreciate your input into alternative solution (Untried).
I will look into testing it.
But I have say that as the “cmdAddEntry_Click” is ONLY a small part of my UserForm code for a large/complicated accounting spreadsheet.
I’m not sure I want to go down the line of creating tables for the existing data and then install the subsequent code required for what it is I’m trying to achieve.
I appreciate your help.
Julhs

 

[NoSparks]

If you decide to look into tables here's a couple sites for dealing with tables

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

jkp-ads.com