VBA to count instances in a row, 2 values

[CR41GK19]

Hi

I'm trying to count instances of absences, not individual days.

I have a register set up for the most recent 6 months (1 column per day) that for each person, their none working days are marked as X and sick days are marked as S. All other days are blank.

So I'm looking for something to count the S's in a row and stop if a cell is blank and then start counting again on the next S, all the while ignoring the X's

I've attached an example file showing what the result should be if its needed.

Any help would be fantastic.

[jindon]

For the data uploaded.

Please Login or Register  to view this content.

[CR41GK19]

Epic, Thank you for this, it works with a much larger data set as well (after i changed "al" to "c" and set i to 3)

Is there a way to specifically set a range for the code to work in, as I also have a date specific check where by S's before a specific date don't need to be counted?

[jindon]

If you upload a workbook with the layouts similar to what you are testing, it will help.

[CR41GK19]

I've added a sheet with the entire data set I'm using, just not all the rows, on the far right side is the result of the code you wrote that counts the instances in the whole range, and next to that in some rows is a date that is pulled from somewhere else in the workbook and then beyond that is the new instance count from that date. Its a large sheet, about 190 columns fyi.

[jindon]

Are you sure about your result?

If so, I don't think I understand what you are trying to do...

[CR41GK19]

I can't deny I dont understand how the code you provided works, but i do get that it counts every row in the sheet and the entire row.

I want to be able to confine that to only work in a specific range. Say ("B2:GC56") for example.
And i wan't to be able to change that so I can set the range using other variables.

For example if the range is set up

Please Login or Register  to view this content.

and then goes through all the rows in the range using something akin to:

Please Login or Register  to view this content.

That would allow it to only count the rows that are relevant to me.

And if the range is set using cells like above then I can set the range using other variables eg:

Please Login or Register  to view this content.

I hope that helps you understand what I'm after.

I did try editing the

Please Login or Register  to view this content.

part of the code you provided but that seems to break the code and it doesn't do anything.

I'd also like to point out that I don't mind if the code operation is slow, but i do need it to run row by row with the beginning and end of the range being able to be subject to change.

Every row is an individual record that feeds a master records sheet, and I need this code to find instances of absences in a date range. And depending on the need only 1 specific row may need updating, or a a series of rows my need to be queried one after the other with new date ranges.

[jindon]

Are you sure about your result?

Again, is your result all correct?

[CR41GK19]

Yes, the result i wanted is correct.

However I changed the code that plots the S's on the grid to account for the date check so your code is fine.

Thank you for the help

[jindon]

GD3 in your result is 3.
Need your clear explanation why it is 3.

If the count starts from 2019/9/23, that is col.CI onwards, my result is just 1.

[CR41GK19]

I put the expected results in column GH which is 2 for that specific row. As for that row theres an S in column CJ and DL.

[jindon]

Read my previous post carefully.

GD3 in your result is 3. that is indeed in GH3...

[CR41GK19]

oh sorry, i have been staring a too many columns for too long today,


That is the result of the current code you provided, i ran it to check the output and add the results to column GD.

[jindon]

So what is the correct result for row 3?

If it is still 3, I should give up this.

[CR41GK19]

the correct result is in column GH

[jindon]

That means totally ignore the dates in col.GF saying, "Only count S's after this date"???

[jindon]

When Col.GF is blank, count from the begging?

If so,

Please Login or Register  to view this content.

I need to go out soon, so next reply will be late...

[CR41GK19]

Sorry for the very late reply.

That code works perfectly, thank you.

Is it possible to edit it so that it functions with this sheet. Its very similar but the data starts one row down and 3 columns across.

I also need to be able to run it from another sheet in the workbook for this specific sheet. the name of the sheet doesn't matter.

I can't figure out how to edit your code to do it myself.

[jindon]

Please Login or Register  to view this content.

[CR41GK19]

This is brilliant thank you!

I run this code at 3 times in my project as I use it to check instances of various types of absence. 'S' is one type, the other 2 I need to check are 'P' and 'U'.

I thought changing this line:

RegX.Global = True: RegX.Pattern = "\b\S*S\S*\b"

to

RegX.Global = True: RegX.Pattern = "\b\U*U\U*\b"

Would be enough to make the code work but I obviously don't understand how the code works as this isn't the case and it doesn't count correctly.

I've attached 2 workbooks with the same layout at before just with the 2 different absence codes in each.

Is it possible to get the code to work with these sheets as well? Sorry for the multiple requests, my project is advancing faster now that things are coming together.

[jindon]

Is this a last change?, better upload the final format, not one by one.

[CR41GK19]

The sheet this code runs on is generated by another sub and then reset after all the require code is done.

Unfortunately there is no final sheet with all the absences on it. The records present change each time change, so each type of absence has to be run individually.

And yes this will be the last change.

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

For all P or S or U.
If you want only a particular letter like P then change to

Please Login or Register  to view this content.

[CR41GK19]

That worked thank you so much for all your help.

[jindon]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.