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 in Others by Kithuzzz
• 20,660 points
47 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 by narikkadan
• 37,660 points

Related Questions In Others

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 in Others by Sohail
• 2,960 points
52 views
0 votes
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 in Others by nisha
• 2,190 points
54 views
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
31 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 in Database by gaurav
• 22,040 points
3,780 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 in Database by gaurav
• 22,040 points
186 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 in Others by narikkadan
• 37,660 points
64 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 in Others by narikkadan
• 37,660 points
98 views
0 votes
1 answer
0 votes
1 answer

Creating a chart in VBA with 2 different Types

Can you carry this out by hand ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
43 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