Can you create a Range in VBA that is available to be referenced in
spreadsheet formulas? In other words, can you create a range in VBA
that shows up when you click Insert - Name - Define?

Thanks,
Brian

Hi
Range("A1:B3").Name = "myRange"

will give you myRange visible in the names dialog box.
regards
Paul

On Mar 24, 3:35*pm, Bongard wrote:
Can you create a Range in VBA that is available to be referenced in
spreadsheet formulas? In other words, can you create a range in VBA
that shows up when you click Insert - Name - Define?

Thanks,
Brian

You can add it to an individual sheet or to the workbook as a whole using
one of these constructions...

Worksheets(Sheet1).Names.Add "YourNameForTheRange", "=Sheet1!$C$3:$F$6"

ThisWorkbook.Names.Add "YourNameForTheRange", "=Sheet1!$C$3:$F$6"

The absolute cell references are important.

--
Rick (MVP - Excel)


"Bongard" wrote in message
...
Can you create a Range in VBA that is available to be referenced in
spreadsheet formulas? In other words, can you create a range in VBA
that shows up when you click Insert - Name - Define?

Thanks,
Brian

If you use relative references, the named range ends up being relative to
the active cell.

--
Rick (MVP - Excel)


"Bongard" wrote in message
...
Thank you both for your prompt responses. Can I ask why the absolutel
cell references are important for the named range?

Take a look at this when you get a chance:
http://www.cpearson.com/excel/named.htm

Also, this is an overall good tutorial:
http://www.anthony-vba.kefra.com/vba/vbabasic2.htm

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

If you use relative references, the named range ends up being relative to
the active cell.

--
Rick (MVP - Excel)


"Bongard" wrote in message
...
Thank you both for your prompt responses. Can I ask why the absolutel
cell references are important for the named range?

I suppose that makes sense and now I see when I enter relative
references that the named range changes as I move the selection around
the spreadsheet. The final code that I used (if it will help anyone in
the future) is the following

'PosSpread_Q - Found in the Cross Sectional Quintile data
Range("CA2:DD2").Select
PosSpread = Selection.Find(What:="PtoE SN Q",
After:=ActiveCell).Address(RowAbsolute:=False, ColumnAbsolute:=False)
If Range(PosSpread).Column 26 Then PosSpread1 = Left(PosSpread, 2)
Else PosSpread1 = Left(PosSpread, 1)
PosSpreadRange = "$" & PosSpread1 & "$4:$" & PosSpread1 & "$499"
ActiveWorkbook.Names.Add Name:="PosSpread_Q", RefersTo:="=OFFSET(Data!
$" & PosSpread1 & "$4,0,0,COUNTA(Data!" & PosSpreadRange & "),1)"

Thanks guys for your help,
Brian