VBA to count the number of entry in a row and update in count column

swindel

New Member
Joined
Oct 11, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I am new in this forum and would like to understand the VBA macro, Just wanna know how can I count the number of entries in a row per criteria. I have the Unique ID, and have a couple of data and will insert the count on Count Y and Count X.

IDData 1Data 2Data 3Data 4Data 5Data 6Count YCount X
1​
XYXXX
1​
4​
2​
XXX
0​
3​
3​
YYY
3​
0​
4​
YYYXX
2​
2​
5​
0​
0​
6​
XXYY
1​
2​
7​
YYYXX
3​
2​
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,265
Office Version
  1. 365
Platform
  1. Windows
I didn't really think that, was just trying to be diplomatic.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows
No, it is in the correct forum.

Actually, Zot was the first one who replied earlier in the day, but it was an Excel solution and the OP alerted them to it. I deleted those posts so that the question would still appear as a new unanswered question (you can see the remnants of those deletions under the original question post).

Zot, if you are using the "Unanswered threads" link to locate new unanswered qustions, be sure to notice that these include questions across ALL questions forums.
Note that it lists which forum they are in right under the question (many people miss that), i.e.

View attachment 48955
I felt like dumb. When I saw the question (for 2nd time), it was like Déjà vu 😄. I was looking for a file that I did my work on but could not find it. So, I thought it was maybe something else, but when @Micron mentioned about Access, only then I recalled. It was same day but probably old age 😅

I think it is not eye catching to have gray label next to date there. Maybe I'm not used to it. Perhaps a small icon right before the beginning of the title? Well, now I know where to actually at first. Thanks @Joe4 for explanation.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,451
Office Version
  1. 365
Platform
  1. Windows
I think it is not eye catching to have gray label next to date there. Maybe I'm not used to it. Perhaps a small icon right before the beginning of the title? Well, now I know where to actually at first. Thanks @Joe4 for explanation.
You are welcome.

Yes, I think we have all done it once or twice, though that is the first time I have ever seen it done twice by the same person on the same thread!
I just try to remember what my father used to say "Assume nothing!", so I got of the habit of just assuming everything in that list is only from Excel Questions.
(Actually, he had a different saying about ASSUME, but that probably isn't repeatable here!;))
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows
You are welcome.

Yes, I think we have all done it once or twice, though that is the first time I have ever seen it done twice by the same person on the same thread!
I just try to remember what my father used to say "Assume nothing!", so I got of the habit of just assuming everything in that list is only from Excel Questions.
(Actually, he had a different saying about ASSUME, but that probably isn't repeatable here!;))
Need to add the same day as well. Now I'm the new world record holder 🤣
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,360
Office Version
  1. 365

ADVERTISEMENT

I wonder if *swindel has reviewed the thread or what latest status is.
 

swindel

New Member
Joined
Oct 11, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello, sorry i was in a holiday and just viewed the reply today.. Actually the data will be the result of a query. So the best option is VBA to count all fields in the row having criteria.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,265
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So, the closest you came to that was post 5?
 

swindel

New Member
Joined
Oct 11, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Micron, Yes.. i can understand basic MS Access VBA, but the problem for this is at the moment i still study the concept of organizing the code.. for this instance i supposed to used DAO to read the table/query and use for each to read a fields in a row. Maybe new table containing my unique key and a field containing the total count will be a best option.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,265
Office Version
  1. 365
Platform
  1. Windows
for this instance i supposed to used DAO to read the table/query and use for each to read a fields in a row.
Yes, but you would need 2 loops; the outer for moving through records and the inner to check each field for your criteria value - only if you stick with your current table/query that you first posted.
 

swindel

New Member
Joined
Oct 11, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yes, but you would need 2 loops; the outer for moving through records and the inner to check each field for your criteria value - only if you stick with your current table/query that you first posted.
You mean, i can use Do While to read all the record first and next the inner will be for loop or for each?
 

Forum statistics

Threads
1,144,098
Messages
5,722,471
Members
422,437
Latest member
BDodge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top