Can't reference a named cell in VBA

[Kim]

I've combined two cells to make a named cell. ie. A2 = left three chars of
this month, B2 = wheat. "Apr Wheat" is a named cell. I can't seem to figure
out how to assign the value of named cell "Price" to the named cell "Apr
Wheat" in VB. The code [Apr Wheat].cells.value = [price] works fine, just
can't get a variable that's combined A2 & B2 to work. I'm in Excel 2003.

What can't I see? Thanks for any help.

Kim

 

[Art]

Kim,

You may just be missing a space between your variables.

I named the following ranges:
Apr = A1:A5
Wheat = B3:E3
Rice = B4:E4

The following code will print out the contents of A3:

Sub test()
Dim a As String
Dim b As String
Dim c As String
a = "Apr"
b = "Wheat"
c = "Rice"
MsgBox (Range(a & " " & b))
End Sub

It prints out A4 if you use c instead of b.

 

[Kim]

Art,

My posting error. I'm looking to put the values of the one named cell into
another named cell. The cell names appear to be OK and if I put the named
cell in the code it seems to work OK. It's just when I try to reference the
newly created cell I fail. Maybe this will help:

a1 = April
a2 = Wheat
a3 = left(a1) & " _" & a2

Apr_Wheat is a named cell, located at b1

Price is a named cell, located at c2, it has a value of 3.50

My problem is putting 3.50 in cell b1 using the cell names.

I hope I explained this better. It's more difficult to explain than I
thought.

Thanks for sticking with me.

Kim

Kim,

You may just be missing a space between your variables.

I named the following ranges:
Apr = A1:A5
Wheat = B3:E3
Rice = B4:E4

The following code will print out the contents of A3:

Sub test()
Dim a As String
Dim b As String
Dim c As String
a = "Apr"
b = "Wheat"
c = "Rice"
MsgBox (Range(a & " " & b))
End Sub

It prints out A4 if you use c instead of b.

I've combined two cells to make a named cell. ie. A2 = left three chars of
this month, B2 = wheat. "Apr Wheat" is a named cell. I can't seem to figure
out how to assign the value of named cell "Price" to the named cell "Apr
Wheat" in VB. The code [Apr Wheat].cells.value = [price] works fine, just
can't get a variable that's combined A2 & B2 to work. I'm in Excel 2003.

What can't I see? Thanks for any help.

Kim

 

[Art]

Kim,

Sorry I misunderstood. Is this what you're looking for?

Sub test()
Range(Range("A3")) = Range("Price")
End Sub

Art,

My posting error. I'm looking to put the values of the one named cell into
another named cell. The cell names appear to be OK and if I put the named
cell in the code it seems to work OK. It's just when I try to reference the
newly created cell I fail. Maybe this will help:

a1 = April
a2 = Wheat
a3 = left(a1) & " _" & a2

Apr_Wheat is a named cell, located at b1

Price is a named cell, located at c2, it has a value of 3.50

My problem is putting 3.50 in cell b1 using the cell names.

I hope I explained this better. It's more difficult to explain than I
thought.

Thanks for sticking with me.

Kim

Kim,

You may just be missing a space between your variables.

I named the following ranges:
Apr = A1:A5
Wheat = B3:E3
Rice = B4:E4

The following code will print out the contents of A3:

Sub test()
Dim a As String
Dim b As String
Dim c As String
a = "Apr"
b = "Wheat"
c = "Rice"
MsgBox (Range(a & " " & b))
End Sub

It prints out A4 if you use c instead of b.

I've combined two cells to make a named cell. ie. A2 = left three chars of
this month, B2 = wheat. "Apr Wheat" is a named cell. I can't seem to figure
out how to assign the value of named cell "Price" to the named cell "Apr
Wheat" in VB. The code [Apr Wheat].cells.value = [price] works fine, just
can't get a variable that's combined A2 & B2 to work. I'm in Excel 2003.

What can't I see? Thanks for any help.

Kim

 

[Kim]

Art,

Works like a champ! Thanks!

Kim

Kim,

Sorry I misunderstood. Is this what you're looking for?

Sub test()
Range(Range("A3")) = Range("Price")
End Sub

Art,

My posting error. I'm looking to put the values of the one named cell into
another named cell. The cell names appear to be OK and if I put the named
cell in the code it seems to work OK. It's just when I try to reference the
newly created cell I fail. Maybe this will help:

a1 = April
a2 = Wheat
a3 = left(a1) & " _" & a2

Apr_Wheat is a named cell, located at b1

Price is a named cell, located at c2, it has a value of 3.50

My problem is putting 3.50 in cell b1 using the cell names.

I hope I explained this better. It's more difficult to explain than I
thought.

Thanks for sticking with me.

Kim

Kim,

You may just be missing a space between your variables.

I named the following ranges:
Apr = A1:A5
Wheat = B3:E3
Rice = B4:E4

The following code will print out the contents of A3:

Sub test()
Dim a As String
Dim b As String
Dim c As String
a = "Apr"
b = "Wheat"
c = "Rice"
MsgBox (Range(a & " " & b))
End Sub

It prints out A4 if you use c instead of b.

:

I've combined two cells to make a named cell. ie. A2 = left three chars of
this month, B2 = wheat. "Apr Wheat" is a named cell. I can't seem to figure
out how to assign the value of named cell "Price" to the named cell "Apr
Wheat" in VB. The code [Apr Wheat].cells.value = [price] works fine, just
can't get a variable that's combined A2 & B2 to work. I'm in Excel 2003.

What can't I see? Thanks for any help.

Kim