define worksheet content in VBA

[Grey]

I need to define a variable name for a worksheet. I want to define A12000
as "ABC" variable.
how to do it in VBA??

MIllions thanks

 

[Don Guillett]

I just recorded this
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/30/2004 by Don Guillett
'

'
Range("C28").Select
ActiveWorkbook.Names.Add Name:="abc", RefersToR1C1:="=Sheet1!R2C3:R8C4"
End Sub

Or, another way
Sheets("sheet1").Range("a1:a6").Name = "xyz"

 

[Nick Hodge]

Don could well have understood your post correct by I read it slightly
differently. That is you want to assign a range to a variable not a range
name. If this is the case you can 'pick the bones' from the code below.
(The trick to assigning an object variable is that it must be 'Set'. (The
mywks variable is not necessary but if you are going to move sheets during
the code it is best to assign that too)

Sub assignVariable()
Dim myWks As Worksheet
Dim ABC As Range
Set myWks = Worksheets("Sheet1")
Set ABC = myWks.Range("A12000")
ABC.Select
End Sub

Hopefully between Don and myself, you have an answer


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)