Excel Range Math - anyone have a lookup?

[stanl]

I frequently persist Excel Ranges to XML (either as content only or with full formatting). Normally these are restored to the same Named Range via the RefersToRangeProperty. I was asked to restore a fully formatted range to a new worksheet in a different position. So assume I persisted

$B$3:$C$10 and it needs to be Restored to $A$10:$?$?? - I need to calculate the ??'s in order to set the Range Name in the new worksheet. I'm hoping someone has a look-up function that I can borrow? Hope this makes sense, and y'all have a happy holiday season. Stan

[Charlize]

Maybe use offset to add a column to the startadress (from A to B). Substract the rownumbers to have the number of lines (10-3). Use this number to define your range (B17).

Charlize

[stanl]

Maybe use offset to add a column to the startadress (from A to B). Substract the rownumbers to have the number of lines (10-3). Use this number to define your range (B17).

The problem is the xml is inserted as a Range.Value at runtime, so I need to calculate a new RefersTo property then reset or create the names range. I think that by using the original range rows.count and columns.count, I can convert the new address to r1c1 format, re-calcuilate the end address by row/col count then convert the formula back... sounds like a kludge... think I'll sleep on it. Stan

[SamT]

You normally restore to a Named Range.
IOW

Procedure Restore()
Put XMLRange in Named Range
End Procedure

Change that to

Procedure Restore()
Names.Add(Name:=TargetRange; Refers To:=$A$10:A10)
Put XMLRange in TargetRange
End Procedure

SamT

Everything I know about VBA, I learned last night at VBAX.com

[stanl]

You normally restore to a Named Range.
IOW

Perhaps my original question wasn't clear. I know how to archive and restore xml ranges; I was asking about calculating new address values were a range to be restored to a different location where you were only given the topleft address. Stan

[SamT]

stanl,

Well, see? I don't know anythng about your restore procedure, so I can't comment on how to interface with it.

But, if I:

NamedRange.Copy
Range(A10).Paste

I will put a duplicate of all the NamedRange cells into the range whose top left cell is A10.

IOW, if I copy three cells from a column it will paste into cells A10, A11, and A12.

Then again, I may be totally FUBARed about what you are really asking.

SamT

[XLGibbs]

Stanl , I would think you can calculate the rows columns and use math and R1C1 referencing to replace the refers to..adding the stored variables for rng.Rows and rng.Columns separate and simply adding those to the top left R1C1 address for the right half of the address..

[stanl]

Stanl , I would think you can calculate the rows columns and use math and R1C1 referencing to replace the refers to..adding the stored variables for rng.Rows and rng.Columns separate and simply adding those to the top left R1C1 address for the right half of the address..

That is exactly what I ended up doing. The fabricated recordset is something like:

oRS.Fields.Append("xlFile",200,100)       ;fullname and path of Excel Workbook
oRS.Fields.Append("rName",200,50)         ;Named range
oRS.Fields.Append("rSheet",200,30)        ;parent (worksheet range found in)
oRS.Fields.Append("rAddress",200,30)      ;range address, referstorange
oRS.Fields.Append("rRef",200,50)          ;refersto
oRS.Fields.Append("rRefRC",200,50)        ;Relative address, R1C1 format, based on
                                          ;Excel's ConvertFormula() function
oRS.Fields.Append("rCols",131,,2|32|64)   ;number of columns in range
oRS.Fields("rCols").Precision =3
oRS.Fields("rCols").NumericScale =0
oRS.Fields.Append("rRows",131,,2|32|64)   ;number of rows in range
oRS.Fields("rRows").Precision =6
oRS.Fields("rRows").NumericScale =0
 
oRS.Fields.Append("rData",203,100000)     ;memo field to hold range data
                                          ;a 2-dim array in csv format
oRS.Fields.Append("rXML",203,100000)      ;memo field range XML, so you end
                                          ;up with XML within XML - cool!

It is a bit overkill, but the speed of persisting and restoring is amazing even though the xml can be 10 times the size of the original workbook. If range data has to be re-positioned in an entirely different workbook, I have the necessary information to calculate.

To address questions concerning 'why not just keep it all in Excel?', you would be surprised how the ability to handle range data in this fashion can come in handy. Stan

[XLGibbs]

Perhaps you could write up an article for the Articles forum on the use of this method? Sounds like a decent alternative that should be explored sometimes.

[stanl]

Perhaps you could write up an article for the Articles forum on the use of this method? Sounds like a decent alternative that should be explored sometimes.

I did make a post in the Potential KB Articles forum on XML formats (which this is derived from) - didn't seem to be any interest Stan