Name of "Named Range" as CountIf Argument?

[chicohernandez]

Hello!

Background: (read or just go to the actual question below ) I have a macro that will add a new row below another row, and fill the first cell of that row with a value that equals the value of the cell above + 1. Example:
Payment No. 1 in the amount of ... on ...
Payment No. 2 in the amount of ... on ...
I do this successfully via command button calling following macro

Please Login or Register  to view this content.

Unfortunately, the same worksheet needs to have another payment schedule, where new - consecutively numbered payments - shall be inserted exactly as above. So the CountIf function is now useless because the word "Payment No." will appear on different locations, namely in different payment schedules. The Worksheet is very dynamic (rows will be inserted/deleted depending on various user-choices), so I cannot further specify the range in which CountIf shall count.

Question: Can I use the name of the NAMED RANGE as the argument in the CountIf function?
Like this

Please Login or Register  to view this content.

Because while the readable text shall always be "Payment No. " I can of course name the range differently depending on which payment schedule the payment is in.

Thanks in advance for looking into this!

[Fettertiger]

Hi,

sure, you can use a named range for CountIf.
If your Named Range would be called "Current_Fiscal" as an example, the command line would need to look like this:
HiNum = WorksheetFunction.CountIf("Current_Fiscal", "Payment " & "*")

Just let me (or the other members of the forum know, if you would need to have the range calculated dynamically.

Regards

Theo
__________________
Please consider:

Thanking those who helped you. Click the scales icon in the upper right corner of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

[chicohernandez]

Hello!

Thank you for the quick reply! But probably I did not explain my question properly, its my first post. Sorry for that.

What I want the CountIf function to do is to search in the entire ActiveSheet and count the number of all the named ranges whose "names" (not values) begin with "Payment". So the named range is not the range I want to search IN but instead the named range is what I want to search FOR. In other words, the name of the named range shall be on the right side inside the CountIf( ), not on the left.

If the above is not possible, I will have to go a different route and probably try to figure out how to define a dynamic named range for each payment schedule to search in for. I've never done that before so could be a new challenge .

Kind regards!

[royUK]

Countif doesn't work like that.

Why would you want a payment to include text? VBA isn't there to overcome poorly designed sheets/workbooks. You shouldn't mix text & numbers if you want use functions on the cells. Use a Custom Format instead

In the custom format dialog

"Payment in the amount of " 000.00

in the cell just the number

[chicohernandez]

Hello royUK!

Of yourse I did not mix numbers and text. The code I've posted shows that text and numbers will be in separate cells (note the different column-parameters in the offset). I wonder how my question could be misunderstood like that, because your answer is nowhere near what I was asking for. And the worksheet is not poorly designed !

I was just asking whether CountIf can in some way count the number (number here means quantity) of named ranges in a Worksheet whose name begins with "Payment".
E.g. the name (what you can insert on the top left of excel; not value!) of range A1 is "Payment1" and of range A2 is "Payment2"
-> CountIf result shall be 2.

I suppose the answer to that is no. No problem I will search for a different solution.

Kind regards!

[royUK]

Please Login or Register  to view this content.

g looks like a number to me, code adds text & g

But then I'm sure you know best

[chicohernandez]

Hello royUK!

Ok you are right on that .

But:
1. Contrary to what I believe you're saying, CountIf works with a mixture of text and numbers. It works in my worksheet already.
2. You posted a line of my code that sets the cell value. I think it should be clear what I want to achieve is not releated to cell value, but to range name.

Anyway, I think this post can be closed because obviously my question cannot be solved with CountIf.

Kind regards!

[royUK]

I didn't say Contif will not work with text & number - I said it's not a good idea to mix text with numbers in one cell