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

Mike,

You'll need to have the rest of your sense of humor removed prior to
continuing <g

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.


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

"Mike" wrote in message
...
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

Hi Mike,

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.<

Try:

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

---
Regards,
Noran

"Mike" wrote in message
...
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

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
Range("ProfitMargin").Cells(4,3)
Range("ProfitMargin")(4,3)
Range("ProfitMargin").Offset(3,2)
Range("ProfitMargin").Range("C4")

Does the (1) or Cells(1,1) set the reference point within the range to
Offset from?
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
Range("ProfitMargin").Cells(4,3)
Range("ProfitMargin")(4,3)
Range("ProfitMargin").Offset(3,2)
Range("ProfitMargin").Range("C4")

Mike Fogleman wrote:

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

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

Alan Beban
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in

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

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

"Mike Fogleman" wrote in message
news:F6spc.53743$536.9188666@attbi_s03...
Does the (1) or Cells(1,1) set the reference point within the range to
Offset from?
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
Range("ProfitMargin").Cells(4,3)
Range("ProfitMargin")(4,3)
Range("ProfitMargin").Offset(3,2)
Range("ProfitMargin").Range("C4")