Addressing cells in a NamedRange

M

Mike

To make a long story short, you can't please all the people all the time. I
have written a report that analyzes weekly data and puts the results onto a
report-like sheet. I have written my code directly to the display cells in
the usual manner. For the 4th time now, they have decided to re-arrange the
look and format of my report sheet. Naturally, they want me to re-write the
code again to place the results where they want them. I also know that this
will never end, like the wife moving around the furniture just to see how it
would look and fit.
And now for the question. I could take each section of the report and make
them Named Ranges and have the code write to the range names, wherever they
may be relocated on the sheet. How would I refer code to a specific cell
location within that range?
For example I now put data into "H9" by writing Range("H9").Value = dataA +
dataB. If "H9" is within Named Range "ProfitMargin" and is 4 cells down and
3 cells right of the upper left corner of the range, how would I code to
that range position.

Any help would be appreciated while I still have some hair and sense of
humor.
Mike
 
B

Bernie Deitrick

Mike,

You'll need to have the rest of your sense of humor removed prior to
continuing said:
For example I now put data into "H9" by writing Range("H9").Value = dataA +
dataB. If "H9" is within Named Range "ProfitMargin" and is 4 cells down and
3 cells right of the upper left corner of the range, how would I code to
that range position.
Click to expand...


Range("ProfitMargin").Cells(4,3)
Range("ProfitMargin")(4,3)
Range("ProfitMargin").Offset(3,2)
Range("ProfitMargin").Range("C4")

would all reference the cell in the 4th row and 3rd column of the range
"ProfitMargin"

Note: I wasn't sure if by 4 cells down you mean count down 4 cells to get to
the fifth row.... but you should get the picture.

HTH,
Bernie
MS Excel MVP
 
N

Norman Jones

Hi Mike,
If "H9" is within Named Range "ProfitMargin" and is 4 cells down and 3
Click to expand...
cells right of the upper left corner of the range, how would I code to that
range position.<

Try:

Range("ProfitMargin")(1).Offset(4, 3).Value = "yourvalue"
 
B

Bernie Deitrick

Oops, got a little sloppy. That third one should be

Range("ProfitMargin").Offset(3, 2)(1)
or
Range("ProfitMargin").Offset(3, 2).Cells(1,1)

HTH,
Bernie
MS Excel MVP
 
M

Mike Fogleman

Does the (1) or Cells(1,1) set the reference point within the range to
Offset from?
 
A

Alan Beban

Mike said:
Does the (1) or Cells(1,1) set the reference point within the range to
Offset from?
Click to expand...

No; it limits the returned range to only the first cell, rather than a
range of the same size as ProfitMargin.

Alan Beban
 
B

Bernie Deitrick

Mike,

Though these are equivalent, and return the same cell, they are subtly
different:

'Select the first cell of the offset range
Range("ProfitMargin").Offset(3, 2).Cells(1,1)

'Select the first cell of the range to offset from
Range("ProfitMargin").Cells(1,1).Offset(3, 2)

The main difference is that if ProfitMargin were defined to include entire
rows, say, using the Offset method first would fail, since you can't offset
an entire row (columns 1 to 256) by two columns (you can't have columns 3 to
258, since 257 and 258 don't exist). These types of issues are probably
infrequently seen, since the typical usage is more likely to be

Range("ProfitMargin").Cells(4,3)
or
Range("ProfitMargin")(4,3)

HTH,
Bernie
MS Excel MVP
 
Top