Error using Named Range value in Macro Formula

[shawkins13]

I have a named range in my workbook that is a constant value (Cratio=2.3755799). I want to refer to this value in my macro in a formula and then copy the answer of that formula back into the workbook. There are two ways that I have referred to this value and both are causing a 1004 error. They are:

Range("Cratio")

and

Activeworkbook.Names ("Cratio").value

I am trying to use this number value in a macro formula, but I see that the value is being returned as "=2.3755799"


Can someone tell me how to refer to this named range correctly so that it is returning a number I can use in my macro formula?

Thanks!

 

[Wookiee]

I think you got it half-right on both of your attempts. Try:

Range("Cratio").Value

I created a named range in a test workbook and was able to use that syntax to successfully use the constant value in a math problem.

 

[shawkins13]

Hmmm....I tried your method, but it is still not working. To double-check the problem I put "x" in my macro equal to the constant:

x=2.3755799

The formula I use works when I use the constant in the actual code and provides the correct answer when I do this:
Formula: Worksheets("Case Results").Cells(7, 27)= Cells(19, 27) / (Cells(24, 27) * x)

When I change variable x to:

x=Range ("CRatio").value

I am still getting Run-time error '1004': Method 'Range' of object '_Global' failed



Any ideas??

I think you got it half-right on both of your attempts. Try:

Range("Cratio").Value

I created a named range in a test workbook and was able to use that syntax to successfully use the constant value in a math problem.

 

[Wookiee]

I have a hypothesis. It might have to do with the scope of the named range. In the test workbook I used, my Cratio range is set to Workbook scope. Maybe the one you've got is specific to the worksheet in which it resides, rather than the entire workbook?

I tried creating a new named range in the same workbook, but it was restricted to the worksheet scope and when I used the same macro code to test it, I got the exact error you just described.

 

[Wookiee]

Okay, I did some further testing. Even when I had the worksheet with the named range open, the macro errored out. It also errored when I qualified the reference in the VBA code by adding the Sheets() name before the range.

I originally thought that I would be able to modify the scope of the named range using the Name Manager, but it wouldn't let me alter the scope of existing named ranges. What I had to do was delete the named range and re-create it. I just selected the cell in question and typed the name in the (not sure what the field is called, but it's to the immediate left of the formula bar). It seems like naming a range that way defaults to the Workbook scope. I hope that works for you.

 

[pgc01]

I have a named range in my workbook that is a constant value (Cratio=2.3755799).

Hi

Sorry, it makes no sense. If you have defined a name with a constant value then it's not a range, it's a constant. You have defined a named constant and so

Range("Cratio")

makes no sense.

To evaluate the named constant, 2 alternatives:

Evaluate("Cratio")

or

[Cratio]

The Evaluate() method is more flexible as it allows you to build the string parameter.