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

[swindel]

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.

ID	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Count Y	Count X
1​	X	Y	X	X	X		1​	4​
2​	X	X		X			0​	3​
3​		Y	Y	Y			3​	0​
4​	Y	Y	Y	X		X	2​	2​
5​							0​	0​
6​	X		X	Y	Y		1​	2​
7​		Y	Y	Y	X	X	3​	2​

 

[Micron]

I didn't really think that, was just trying to be diplomatic.

 

[Zot]

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]

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]

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]

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

 

[swindel]

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]

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

 

[swindel]

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]

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]

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?