refer to cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
'<<=============
 
Your RefersTo doesn't reference the sheet you are using. It should be
something like

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

HTH,
Barb Reinhardt
 
Back
Top