PDA

View Full Version : reference named range



no_more_fun
02-01-2012, 02:08 PM
using ADDRESS() on a sheet to generate a range, then defining that as a named variable in the workbook.

I want to assign this range to a variable in a macro and havent had any luck... ie:

Sheet1 A1 = ADDRESS(ROUNDUP(B27,0),ROUNDUP(B20,0),4)
- resulting value: O59
Sheet1 A2 = ADDRESS(ROUNDUP(B28,0),ROUNDUP(B21,0),4)
- resulting value: Q72

Defined names:
L_50 refers to A1
H_50 refers to A2


in macro:



Dim rng50 As Range

Set rng50 = Range(L_50, H_50)


is a no go. I've tried some other ways of doing this but none work. I cant find an example of this online anywhere.

any ideas?

the goal is a simple macro that cycles through each sheet, removes all previous cell outlines, then outlines the range specified, rinse and repeat for all sheets in workbook.

Bob Phillips
02-01-2012, 05:00 PM
Maybe



Dim rng50 As Range

Set rng50 = Range(Range(Range("L_50").Text), Range(Range("H_50").Text))

Bob Phillips
02-01-2012, 05:06 PM
But why not just



Dim rng50 As Range

Set rng50 = Range(Cells(Range("B28").Value, Range("B21").Value), Cells(Range("B27").Value, Range("B20").Value))