I have a problem with counta function in VBA

0 votes

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
Oct 2, 2022 in Others by Kithuzzz
• 38,010 points
770 views

1 answer to this question.

0 votes
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.
answered Oct 3, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
0 answers

I have a problem hosting a project made in Next.js

I have Next.js project. When I connect ...READ MORE

Jan 27, 2023 in Others by Tejashwini
• 3,820 points
215 views
0 votes
1 answer

Does Java have a IN operator or function like SQL

A Java-language was designed to be powerful ...READ MORE

answered May 30, 2022 in Others by Sohail
• 3,040 points

edited Jun 22, 2023 by Khan Sarfaraz 306 views
+1 vote
1 answer

Does Java have a "IN" operator or function like SQL?

You can use java.util.Collection.contains() for collections.  If a non-null ...READ MORE

answered May 31, 2022 in Others by nisha
• 2,210 points

edited Jul 6, 2023 by Khan Sarfaraz 434 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
18,498 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
731 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
564 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
754 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
757 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
3,270 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP