Range's Address: How To Change via VBA?
 

This works, no problem:
-------------------------------------------
?Range("Fund0_MarketTotSEI_Owned").Address
$G$31
-------------------------------------------


But this fails with "Assignment To constant
not permitted" (A compile-time error....)
-------------------------------------------
Range("Fund0_MarketTotSEI_Owned").Address="$G$32"
-------------------------------------------


My agenda is that I'm creating a spreadsheet via VBA in an MS
Access app and one of the columns is a percent computed by
dividing a current row value by a grand total value - and, of
course, we don't know the grand total's value (or row number)
until we've created all the line items.

I can't just run a query to compute said total because part of
the spreadsheet is a "WhatIf" section and the total will change
each time the user does something in that section.

My first thought was to create a range name that points to
somewhere arbitrary, include said name in the .Formulas as I
populate rows, and then alter the range's .Address to point to
the actual total once I know what row it is on.

Is my basic strategy sound?

If so, how to alter that range's .Address? Tried a couple of
Googles, but got overwhelmed by the volume of non-applicable
information returned.

If the strategy is not sound, can anybody suggest something
better?
--
PeteCresswell

Range's Address: How To Change via VBA?
 

This can be a bit confusing - despite the terminology a
"Named Range" is (in the Excel Object Model) really a
Name object that refers to a Range, rather than a
Range object that happens to have a Name...
(And I doubt that that last sentence removes much
of the confusion..)

Anyway, you need to change the Range that Name
object refers to:

Names("Fund0_MarketTotSEI_Owned").RefersTo = "=$G$32"






On Jul 25, 3:28 pm, "(PeteCresswell)" wrote:
This works, no problem:
-------------------------------------------
?Range("Fund0_MarketTotSEI_Owned").Address
$G$31
-------------------------------------------

But this fails with "Assignment To constant
not permitted" (A compile-time error....)
-------------------------------------------
Range("Fund0_MarketTotSEI_Owned").Address="$G$32"
-------------------------------------------

My agenda is that I'm creating a spreadsheet via VBA in an MS
Access app and one of the columns is a percent computed by
dividing a current row value by a grand total value - and, of
course, we don't know the grand total's value (or row number)
until we've created all the line items.

I can't just run a query to compute said total because part of
the spreadsheet is a "WhatIf" section and the total will change
each time the user does something in that section.

My first thought was to create a range name that points to
somewhere arbitrary, include said name in the .Formulas as I
populate rows, and then alter the range's .Address to point to
the actual total once I know what row it is on.

Is my basic strategy sound?

If so, how to alter that range's .Address? Tried a couple of
Googles, but got overwhelmed by the volume of non-applicable
information returned.

If the strategy is not sound, can anybody suggest something
better?
--
PeteCresswell

Range's Address: How To Change via VBA?
 

If you want to name a range

range("g32").name="Pete"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"(PeteCresswell)" wrote in message
...
This works, no problem:
-------------------------------------------
?Range("Fund0_MarketTotSEI_Owned").Address
$G$31
-------------------------------------------


But this fails with "Assignment To constant
not permitted" (A compile-time error....)
-------------------------------------------
Range("Fund0_MarketTotSEI_Owned").Address="$G$32"
-------------------------------------------


My agenda is that I'm creating a spreadsheet via VBA in an MS
Access app and one of the columns is a percent computed by
dividing a current row value by a grand total value - and, of
course, we don't know the grand total's value (or row number)
until we've created all the line items.

I can't just run a query to compute said total because part of
the spreadsheet is a "WhatIf" section and the total will change
each time the user does something in that section.

My first thought was to create a range name that points to
somewhere arbitrary, include said name in the .Formulas as I
populate rows, and then alter the range's .Address to point to
the actual total once I know what row it is on.

Is my basic strategy sound?

If so, how to alter that range's .Address? Tried a couple of
Googles, but got overwhelmed by the volume of non-applicable
information returned.

If the strategy is not sound, can anybody suggest something
better?
--
PeteCresswell

Range's Address: How To Change via VBA?
 

Per Don Guillett:


Thanks Andrew.

Thanks Don.

I'm on it!!!! -)
--
PeteCresswell