Need help counting cells that contain any number but not exact string TR is in the cell

[RaffPost]

Hello all,

Thanks in advance for looking and hopefully you'll be able to help me.

I've created a restaurant work schedule and want to count how many staff members are scheduled. I only want to count cells that contain ANY number in them BUT NOT cells that contain any number plus the exact letters TR.

Example:

9
10
11
1130
12
1230
3
330
4
430
5
530
6
630
7

Basically it's schedule start times without the : between the hours and minutes. I may be adding other start times in the future so I would like to keep the formula as open as possible to not have any errors once I add another time slot.

How can I count the below range of cells to tell me that I have ONLY 5 people working and not count the 3 people who are Training?

9
9 TR
10
10 TR
1030
11
11 TR
12

Thanks!

 

[skywriter]

Well if you have a drop box account you can post the file there, put a link here and I'll take a look at it.
Critical things here are file is saved as a .xlsm format, macros are enabled.
You chose insert module, NOT insert Class Module.
Put the test data above on a blank sheet in A1 to A25 then you can just copy the formula =CountCustom(A1:A25) and paste it into a cell and hit enter.

Another thing is click the fx next to the formula bar on your spreadsheet, this will bring up the function menus, in the drop down box labeled:
Or select a category: scroll down to user defined and you should see CountCustom in there.

 

[RaffPost]

Well if you have a drop box account you can post the file there, put a link here and I'll take a look at it.
Critical things here are file is saved as a .xlsm format, macros are enabled.
You chose insert module, NOT insert Class Module.
Put the test data above on a blank sheet in A1 to A25 then you can just copy the formula =CountCustom(A1:A25) and paste it into a cell and hit enter.

Another thing is click the fx next to the formula bar on your spreadsheet, this will bring up the function menus, in the drop down box labeled:
Or select a category: scroll down to user defined and you should see CountCustom in there.

Here's the Dropbox link - https://www.dropbox.com/s/71jkqqwvbjz8gt2/Terroni Adelaide FOH Schedule (Template).xlsm?dl=0

Thanks!

 

[skywriter]

I don't see where you inserted a module and I don't find the code in any of the other places where it could have ended up. I would suggest following the directions above again and we'll see what happens.

When you hit Alt and then F11 you should see a screen that has some drop down menu choices.
One of these is Insert, choose that one and then choose Module, NOT Class Modules. Then paste the code into the large white box with the blinking cursor in it. That's it, you can hit Alt + Q to get back to the Excel screen.
If you paste the sample data in a blank sheet like I did above, in the same cells as I did (A1:A25) then you can copy the formula into a blank cell and hit enter and it should work. If not post the workbook with the code installed so I can look at it and see what's happening.

 

[RaffPost]

I actually removed them before I sent the link so that you could add it yourself with the certainty it was done correctly. I'll add it again and resend the link. Thanks.

 

[RaffPost]

Here is the link again with the code added - https://www.dropbox.com/s/71jkqqwvbjz8gt2/Terroni Adelaide FOH Schedule (Template).xlsm?dl=0

 

[skywriter]

There's no module added and no code.

 

[RaffPost]

There's no module added and no code.

Bad news - I hadn't synced my Drobbox account to upload the changes

Good news - It works! - not sure what happened but after several tries the code works

Frustrating news - I need the code tweaked slightly

Is it possible to have more than one code?

I need to have counts for the following sections on the spread sheet.

Servers
Count number of Servers that have numbers and letters in the cells. DO NOT count cells that contain numbers with TR. DO NOT count blank cells or cells with letters only.
9
10
11
11 L
12
4
4 CAN
5
6 TR

Hosts
Count number of Hosts that have numbers and letters in the cells. DO NOT count cells that contain numbers with TR. DO NOT count blank cells or cells with letters only.

9
10
10 TR
11
11 TR
12 H OFC
12
1230 FR
4
4 TR
5
6

Support
Count number of Bussers that have numbers and BS in the cells. DO NOT count cells with numbers with BB, BR, FR, TR BB, TR BR or TR FR. DO NOT count blank cells or cells with letters only.
Count number of Food Runners that have numbers and FR in the cells. DO NOT count cells with numbers with BB, BR, BS, TR BB, TR BR or TR BS. DO NOT count blank cells or cells with letters only.
Count number of Back Runners that have numbers and BR in the cells. DO NOT count cells with numbers with BB, BS, FR, TR BB, TR BS, TR BS or TR FR. DO NOT count blank cells or cells with letters only.

9 BS
10 BS
10 BS
1130 FR
12 BS
1230 FR
4 BS
5 BS
6 BR
6 FR
6 TR BB
6 TR BR
6 TR BS
6 TR FR

Bar
Count number of Bartenders that only have numbers in the cells. DO NOT count cells with numbers and with TR B or TR BB. DO NOT count blank cells or cells with letters only.
Count number of Bar Backs that have numbers and BB in the cells. DO NOT count cells with numbers and with TR B or TR BB. DO NOT count blank cells or cells with letters only.

9
11
11 TR
12
4
5
5 TR B
6 BB



***I added numbers in the above example to show the spacing between the the numbers and letters. Different numbers could be used.***


Thanks again for helping me with this issue. It's greatly appreciated.

 

[skywriter]

Time to start a new thread.
Good Luck.

 

[RaffPost]

Time to start a new thread.
Good Luck.

Thanks for your help thus far.

What do you suggest the best course of action regarding starting a new thread? Should I remove the current code from it and post the new thread? Should I list the new thread with the code listed as being active?

How do I close this thread without marking it as solved?