vba cell reference using named column

[Ralph Gregory]

hi I am trying to get a value from a cell in a named column can anyone help with the correct format?

a=8
range("D2") = Range("Myname" & a) * Range("X1")

this returns an error
what am I doing wrong?

 

[VoG]

This is unclear. Possibly

Range("D2").Value = Range("Myname" & a * Range("X1")).Value

 

[Ralph Gregory]

This is unclear. Possibly

Range("D2").Value = Range("Myname" & a * Range("X1")).Value

no still gives an error
to clarify range myname refers to $B:$B which is a complete column
the reason I want to name the column is so that if I add another column later
I dont have to redo all the cell references in my formulas.
so in vba
a = 2
Range("D2") = Range("B" & a) * Range("X1") works fine
but if I add a column before B my data has moved and the formula is useless
so how do I include the named column information in the formula?

 

[Ralph Gregory]

no still gives an error
to clarify range myname refers to $B:$B which is a complete column
the reason I want to name the column is so that if I add another column later
I dont have to redo all the cell references in my formulas.
so in vba
a = 2
Range("D2") = Range("B" & a) * Range("X1") works fine
but if I add a column before B my data has moved and the formula is useless
so how do I include the named column information in the formula?

HeHe answer to myself...worked it out after loads of trial and error
C = Range("MyColumn").Column
a = 2
Range("D2") = Cells(a, C) * Range("X1")

 

[mikerickson]

The .Cells property of a range is relative to the top left cell of the range. So an alternative could avoid using the unqualified Cells (relative to the ActiveSheet by default), like this:

a = 2

Range("D2") = Range("myColumn").Cells(a, 1) * Range("X1")

 

[Ralph Gregory]

The .Cells property of a range is relative to the top left cell of the range. So an alternative could avoid using the unqualified Cells (relative to the ActiveSheet by default), like this:

a = 2

Range("D2") = Range("myColumn").Cells(a, 1) * Range("X1")

Thanks Mikerson that's very elegant and gives me a bit more understanding as to how it works.