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,000 points
1,107 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,600 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
349 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 502 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 642 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
19,980 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
980 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,600 points
806 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,600 points
913 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,600 points
1,060 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,600 points
3,757 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