refer to cell
 

Hi! I want to name a range in VBA. I know in what cell I want it to start,
lets say A1. I also have a variable that controls both the height and length
of the range (they are of equal size). This variable is called x.

I have:

ThisWorkbook.Names.Add Name:="CorrMatrix", RefersTo:="=$A$1:$C$10",
Visible:=True

but the Refers to bit is wrong. How shall I write it? please help!

refer to cell
 

Hi Anne,

Your question is not clear but, perhaps, try
something like:

'=============
Public Sub Tester()
Dim x As Long, y As Long
x = 6
y = 4
ThisWorkbook.Names.Add Name:="CorrMatrix", _
RefersTo:=Range("A1").Resize(x, y).Address
End Sub
'<<=============



---
Regards,
Norman



"Arne Hegefors" wrote in message
...
Hi! I want to name a range in VBA. I know in what cell I want it to start,
lets say A1. I also have a variable that controls both the height and
length
of the range (they are of equal size). This variable is called x.

I have:

ThisWorkbook.Names.Add Name:="CorrMatrix", RefersTo:="=$A$1:$C$10",
Visible:=True

but the Refers to bit is wrong. How shall I write it? please help!

refer to cell
 

Your RefersTo doesn't reference the sheet you are using. It should be
something like

"=Sheet1!$A$1:$C$10"

HTH,
Barb Reinhardt

"Arne Hegefors" wrote:

Hi! I want to name a range in VBA. I know in what cell I want it to start,
lets say A1. I also have a variable that controls both the height and length
of the range (they are of equal size). This variable is called x.

I have:

ThisWorkbook.Names.Add Name:="CorrMatrix", RefersTo:="=$A$1:$C$10",
Visible:=True

but the Refers to bit is wrong. How shall I write it? please help!