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 <b.bomg...@gmail.com> 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" <(E-Mail Removed)> wrote in message
news:33b2708a-bcaf-480b-8b2e-(E-Mail Removed)...
> 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

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

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

--
Rick (MVP - Excel)


"Bongard" <(E-Mail Removed)> wrote in message
news:58b525a0-ac64-4eb4-9e04-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:58b525a0-ac64-4eb4-9e04-(E-Mail Removed)...
> > 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("CA2D2").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