Adding an additional parameter to a VBA existing code

**gregpaj** · 09-13-2018, 05:08 PM

I have used the following code and it works perfectly:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function

But, now I want to do a little more. For the range of cells, I want to have 4 separate user defined functions.
1) Count cells IF they are a particular color, as well as the value inside ending in "*o"
2) Count cells IF they are a particular color, as well as the value inside ending in "*s"
3) Count cells IF they are a particular color, as well as the value in the cell having 2 text characters in the cell, "??"
4) Count cells IF they are a particular color, as well as the value in the cell being a number greater than zero, ">0".
How can I modify the base "GetColorCount" code to incorporate this additional parameter for each instance?

**AlphaFrog** · 09-13-2018, 07:37 PM

Originally Posted by gregpaj

3) Count cells IF they are a particular color, as well as the value in the cell having 2 text characters in the cell, "??"

When you say two text characters, do you mean text is only alpha-characters A-Z or can it include numeric characters? Give examples.

**AlphaFrog** · 09-13-2018, 07:39 PM

Here are Functions 1, 2 , and 4

Please Login or Register  to view this content.

**gregpaj** · 09-13-2018, 07:58 PM

Thanks! The question you had Is that I have some cells with two digit numerical values, and some cells with 2 digit alpha character values. Maybe there is a way to count both together in one statement? That would be great actually. So total of three functions. the *s plus color count, the *o plus color count, and the containing "AA or 88" plus color count function. BTW I tried your code for *o and I got an error.

**gregpaj** · 09-13-2018, 08:12 PM

Im too new of a user so I am not allowed to post images, but I wish I can show you what I mean

**AlphaFrog** · 09-13-2018, 08:16 PM

Originally Posted by gregpaj

and the containing "AA or 88" plus color count function.

Do you just want to count cells with any two characters? Or do you have something like 8A that you want to exclude?

BTW I tried your code for *o and I got an error.

What is the error description?
What line is highlighted when you click the Debug button on the error dialog?

**gregpaj** · 09-13-2018, 08:23 PM

Its a poker hand matrix. 13x13 cells, 169 total cells. bottom left side has all offsuit hands , example A2o. Top right section has all suited variants, example A2s, and cutting down the diagonal cells from top left to bottom right are all the pairs, AA through to 22. So I'm trying to build a VBA function to count all the cells i highlight by color I select, as well as if they are offsuit (*o), suited (*s) or paired (AA-22). Maybe you have a more elegant solution? so ultimately if I had one function where I can select the color range, the color reference, and a third (or fourth) argument to allow me to filer for only suited, offsuit or paired hands would be perfect.

**gregpaj** · 09-13-2018, 08:27 PM

Error is "compile error: argument not optional" and top line is highlighted in the module

**AlphaFrog** · 09-13-2018, 08:46 PM

Originally Posted by gregpaj

Error is "compile error: argument not optional" and top line is highlighted in the module

Sound like you missed an argument; the count range or the color range.

The function GetColorCount2 below has all three in one function. There are now three arguments. The third argument is 1, 2, or 3.
1 counts "*o"
2 counts "*s"
3 counts two paired characters.

Please Login or Register  to view this content.

**gregpaj** · 09-13-2018, 08:55 PM

Awesome!! that is perfect!! Can I add some text to explain what 1,2 or 3 means in the function window so I don't forget?

**gregpaj** · 09-13-2018, 08:56 PM

I'm a beginner at this stuff but that makes my life soo much easier. so appreciative

**AlphaFrog** · 09-13-2018, 09:11 PM

To be more descriptive, the crit are now "o", "s", or "xx" instead of 1 ,2, 3. Include the surrounding quotes e.g.
=GetColorCount2(A1:A100, B1, "xx")

Please Login or Register  to view this content.

Adding an additional parameter to a VBA existing code

LinkBack

Thread Tools

Rate This Thread

Display

Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Re: Adding an additional parameter to a VBA existing code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Additional look up is required to an existing VBA code !

[SOLVED] Additional look up is required to an existing VBA code

[SOLVED] Modfication of Existing Code for additional additional cells

Adding an additional IF function to my existing formula to return a blank

[SOLVED] Add new (additional range) to existing code

[SOLVED] Adding additional selection criteria to existing Macro

[SOLVED] adding additional parameter to median formula

Bookmarks

Bookmarks

Posting Permissions