Unable to name certain ranges

[MichaelDavid]

Greetings! I opened a new blank workbook, and entered the following values in
Column A:

[A1] = 0
[A2] = 1
[A3] = 2
[A4] = 3

I then executed the following subroutine:

Sub Start()

[C1] = 0
Range("C2:C4").FormulaR1C1 = "=R[-1]C+RC[-2]"

MsgBox "C4 =" & [C4] ' As expected, MsgBox displays C4
= 6

' I then tried:

[E1] = 0
Range("E2:E4").Formula = "=E1+A2"

MsgBox "E4 =" & [E4] ' As expected, MsgBox displays E4
= 6

' I now give names to two ranges

Range("G2:G4").Name = "GValues"
Range("A2:A4").Name = "AValues"

[G1] = 0
Range("GValues").Formula = "=AValues+G1"

MsgBox "G4 =" & Range("GValues")(3) ' As expected, MsgBox displays
G4 = 6

' But if I try to assign a name to G1 as follows:

Range("G1").Name = "GInitVal"

' and use it in a formula as follows:

Range("GValues").Formula = "=AValues+GInitVal"

MsgBox "G4 =" & Range("G4")

' [G4] is only 3, not 6 as expected

' How can I name Range G1 so that I get the correct result?

End Sub
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[RadarEye]

Hi Michael,

The result is correct. When you look at the formula in G4 you will
see
=AValues+GInitVal

The value of GInitVal is allways 0.
When you use a namedrange and not a celladdress then name will be
constant.

HTH (sort of)

Wouter

 

[Bob Phillips]

Why do you expect it to be 6?

A4, which is within AValues, is assigned a value of 3. G1, which is
GInitVal, is assigned a value of 0. 3+0=3. QED.

 

[MichaelDavid]

Greetings! Is there any way to give GIntValue a name or define it in such a
way that
Range("GValues").Formula = "=AValues+GInitVal"
would yield a value of 6 for [G4] as was the case in each of the preceding
results?
Or is there any way I can name Range G1 such that it will work in the above
formula to yield a value of 6 for [G4]?
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick