I have a problem with counta function in VBA

Question

Why the "counta" function isn't adding up rows is beyond me. Only the values in the same second row are replaced.

This is my Excel VBA code for counting. A function always replaces the value for the first row rather than counting and moving on to the next row.

Sub Reset()

Dim iRow As Long
iRow = [COUNTA(Purchases Tracker!A:A)]
With ENTRY

    .txtPRN.Value = ""
    .txtDC.Value = ""
    .txtDA.Value = ""
    .txtPRA.Value = ""
    .txtPON.Value = ""
    .txtPOA.Value = ""
    .txtDI.Value = ""
    .txtAMR.Value = ""
    
    .cmbBG.Clear
    .cmbST.Clear
    
    .cmbBG.AddItem "5431"
    .cmbBG.AddItem "5309"
    .cmbBG.AddItem "5308"
    .cmbBG.AddItem "5307"
    .cmbBG.AddItem "5306"
    .cmbBG.AddItem "5305"
    .cmbBG.AddItem "5304"
    .cmbBG.AddItem "5303"
    .cmbBG.AddItem "5302"
    .cmbBG.AddItem "5301"
    .cmbBG.AddItem "5300"
    .cmbBG.AddItem "5299"
    .cmbBG.AddItem "95112"
    .cmbBG.AddItem "95113"
    
     
    .cmbST.AddItem "Approved"
    .cmbST.AddItem "Rejected"
    .cmbST.AddItem "In Progress"
    .cmbST.AddItem "Returned"
    .cmbST.AddItem "Recieved"
    .cmbST.AddItem "Recieved Partially"
    
    .lstDatabase.ColumnCount = 11
    .lstDatabase.ColumnWidths = "30,60,60,70,70,70,60,70,70,70,70"
    
    
    
    
End With

End Sub

Sub Submit()

Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Purchases Tracker")
iRow = [COUNTA(Purchases Tracker!A:A)] + 1
With sh
    .Cells(iRow, 1) = iRow - 1
    .Cells(iRow, 2) = ENTRY.cmbBG.Value
    .Cells(iRow, 6) = ENTRY.txtPRN.Value
    .Cells(iRow, 7) = ENTRY.txtDC.Value
    .Cells(iRow, 8) = ENTRY.txtDA.Value
    .Cells(iRow, 10) = ENTRY.txtPRA.Value
    .Cells(iRow, 11) = ENTRY.txtPON.Value
    .Cells(iRow, 12) = ENTRY.txtPOA.Value
    .Cells(iRow, 13) = ENTRY.txtDI.Value
    .Cells(iRow, 15) = ENTRY.txtAMR.Value
    .Cells(iRow, 18) = ENTRY.cmbST.Value
End With

End Sub

Sub show_ENTRY() ENTRY.Show End Sub

narikkadan · Answer 1 · Oct 3, 2022

When the worksheet name has a space, you need single quotes: iRow = [COUNTA('Purchases Tracker'!A:A)]. I prefer iRow = Application.CountA(ThisWorkbook.Worksheets("Purchases Tracker").Columns("A")) though.