Using a Named Range in Different VBA statements

[lovecolorado]

I'm improving my code's readability by inserting Named ranges into an existing, working macro. The problem is that Named ranges seem to act differently in different macro statements and I can't figure out how to use them so they work.

My original code is:

Please Login or Register  to view this content.

and I want to replace

Please Login or Register  to view this content.

with a named range Chapter_Primary, where the range of Chapter_Primary that has just one cell, namely 'Instructions and Scratch Pad'!$B123, per the following screen-capture:
Named Range.JPG

I've searched online for ways to implement this and saw, for a one-cell Named Variable, that I can use

Please Login or Register  to view this content.

. Just to be cautious, I inserted a print statement immediately before my original code, so my code now reads

Please Login or Register  to view this content.

When I execute the code, MsgBox displays the proper chapter, but the calculation changes from 89% to 0%; i.e., the expression

Please Login or Register  to view this content.

is never true. Why would Range("Chapter_Primary") work with MsgBox but not COUNTIF?


As an alternative, I also tried replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

, so my code became

Please Login or Register  to view this content.

This would not execute, and the debugger would highlight this line of code.


How can I use the Named Range Chapter_Primary so the code works?
To help, I have attached the file.


I also want to replace the code

Please Login or Register  to view this content.

with the Named Range Chapters, so if your answer does not also apply to this situation, I would appreciate your help with this next step.

[rorya]

Evaluate is using a formula string, so you should simply replace this:
'Instructions and Scratch Pad'!$B123
with this:
Chapter_primary

[lovecolorado]

That was embarrassingly simple, but thank you all the same.

I have two follow-up questions:

1. As I wrote in my original post, I also want to replace the code

   Please Login or Register  to view this content.

   with the Named Range 'Chapters' (without the single quote marks). I tried just substituting 'Chapters' (without the single quote marks), but that didn't work. I would appreciate your help with this next step.
2. You wrote "Evaluate is using a formula string". Would you please explain when I should use 'Chapter_Primary' and when I should use 'Range("Chapter_Primary")'?

[rorya]

That should work:

Please Login or Register  to view this content.

You'd use Range("Chapter_Primary") anywhere you would have used Sheets("Input - Membership CSV File").Range("B123").

[lovecolorado]

Thank you for explaining when to use 'Chapter_Primary' and when to use 'Range("Chapter_Primary")'.

When I substitute 'Chapters' (without the single quote) for ''Input - Membership CSV File'!$E5:$E705' (without the single qutoes), the debugger hangs at the line

Please Login or Register  to view this content.

Any further suggestions?

Also, I just remembered one question that I forgot to ask in my original post:
I want to replace '$G$860' (without the single quotes) with the value passed to the Sub; i.e., Anchor. Can you tell me how to do this? (This is the last question in this thread - I promise.)

[rorya]

What does the debugger say?

I wouldn't bother with evaluate here, I'd use the WorksheetFunction object:

Please Login or Register  to view this content.

[lovecolorado]

First: Thank you for also addressing my question about replacing $G$860 with Anchor.

Second: When I substitute 'Chapters' for ''Input - Membership CSV File'!$E5:$E705' and run the macro, the error message is:
Error III.png
and the debugger highlights the line of code containing 'Chapters'.

Third: When I comment out my line of code, insert yours, and run the macro, I get the error message:
Error IV.png
and the debugger highlights your line of code.

Because my objective is to make the code more readable, I prefer the solution with 'evaluate', but I will gladly, and gratefully, take anything at this point.
Any further thoughts on how to get this to work?

[rorya]

Those attachments don't seem to work. Can you just type the error message?

[lovecolorado]

In my thread, if you click on the text 'Attachment 618513' or 'Attachment 618515', a new webpage appears that displays the attachments properly. I don't know why they don't appear 'inline' with the reply.

However, to your request:
The text of the first error message is: Run-Time error '13': Type mismatch
The text of the second error message is: Run-Time error '1004': Unable to get the Countifs property of the WorksheetFunction class.

[rorya]

Neither link is valid when I click on them.

I suspect there is a problem with your range definitions. Can you post your workbook?

[lovecolorado]

Re "Neither link is valid when I click on them.": I reposted the error messages and they display properly now.

Re "I suspect there is a problem with your range definitions.": The original post shows the range definition for 'Chapters'.

Re "Can you post your workbook?": The original post contains the workbook.

[rorya]

Yes, but that version still contains all the bugs from previous questions and doesn't appear to have all the names defined in it that you are using.

[lovecolorado]

This version is completely current.

[rorya]

Your Chapters range is not the same size as the other ranges in the COUNTIFS formula - it only goes to row 405

[lovecolorado]

Thank you. I had greatly extended the input ranges when I added the Named Ranges, and forgot about the obvious incompatibility with the hard-coded former lengths. That resolves all of my questions about Named Ranges.

This leaves only my final, third question:
If I use $G$860 in the equation, it evaluates to 89%.
If I replace $G$860 with Anchor.Value2, the equation wrongly evaluates to 0%.

What can I do to use the value of the variable Anchor so the equation evaluates properly?

The current file is attached.

[rorya]

You can't use anchor.value2 in a formula string. You need to concatenate the actual value into the formula string if you're going to use Evaluate:

Please Login or Register  to view this content.

[lovecolorado]

Thank you for a third time! That works perfectly.

Now for 'comprehension' vs. 'compliance', would you be so kind as to explain why TEXT($G$860-45,0) worked, but TEXT(Anchor.Value2 - 45,0) did not?

[rorya]

Evaluate calculates a formula string just as if you had entered it into a cell. In a cell, this:

TEXT(Anchor.Value2 - 45,0)

has no meaning since Anchor.Value2 is not a named range. Using the concatenation approach above, VBA calculates Anchor.Value2 - 45 and then puts that literal value into the formula string.

[lovecolorado]

Re "calculates a formula string just as if you had entered it into a cell": I hadn't thought of it in exactly that way, and this clears-up my question perfectly. Thank you.

I hate to say it, but I declared 'victory' too soon. When I said the worksheet calculated the results properly, the result was for a different 'Anchor' than G860 and, therefore, did not use the module we've been working on. When I ran the module with G860, and with the code

Please Login or Register  to view this content.

the equation, once again, returned a value of 0, instead of the 89% that it should return. Do you have any ideas on how to correct why this occurred?

Once again, I've attached the current version of the file to this post.

[rorya]

The code should be:

Please Login or Register  to view this content.

[lovecolorado]

Thank you. Works perfectly; and, thanks to your explanation, I understand why.

[rorya]

Thank you. Works perfectly;

Glad to help.

and, thanks to your explanation, I understand why.

Even better! My work here is done.