I will have an application in which there will be four columns where the end
user can fill out 100 rows of dollar amounts or percentages. I need to be
able to save those columns as part of saving a "case" which can be recalled
for editing, etc.

The thought is is to take the 100 rows of the column and store it in a cell
as an array. Can this be done?

use the registry to save your scenarios as a delimited string like:


Sub StoreData()
Dim s$, v, r As Range
Set r = Range("a1:a100")
If Not (r.Rows.Count = 1 Or r.Columns.Count = 1) Then
MsgBox "r must be a single row or column range"
ElseIf r.Rows.Count = 1 Then
v = r.value2
Else
v = Application.Transpose(r.value2)
End If
s = Join(v, vbTab)
SaveSetting "myApp", "scenarios", "1", s
End Sub

you'll need xl2000 or newer to use join..

Use GetSetting you retrieve again..
I've used value2 which will save dates as numbers
avoiding changes in regional settings when retrieving
I've used vbTab as delimiter since it's unlikely the cells will
contain strings with tabs...



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Don Kline wrote :

I will have an application in which there will be four columns where
the end user can fill out 100 rows of dollar amounts or percentages.
I need to be able to save those columns as part of saving a "case"
which can be recalled for editing, etc.

The thought is is to take the 100 rows of the column and store it in
a cell as an array. Can this be done?

Array
Name
You can store 32K characters in a cell. So you could save these as text
strings which an be parsed into an array or parsed back into separate cells.
This can be done by using the list separator character. I think trying
to make it anything else would be restricted by the formula length
restriction of 1024 characters.

another approach would be to store it in a defined name:

This stores the array in a defined name, then repopulates the array.

Sub BB()
Dim v(1 To 100, 1 To 2) As Double
For i = 1 To 100
For j = 1 To 2
v(i, j) = Application.Round(1000 * Rnd() + 1, 2)
Next
Next
ThisWorkbook.Names.Add Name:="vArr", RefersTo:=v
Erase v
For i = 1 To 100
For j = 1 To 2
v(i, j) = Evaluate("Index(varr," & i & _
"," & j & ")")
Debug.Print i, j, v(i, j)
Next
Next

End Sub

--
Regards,
Tom Ogilvy


"Don Kline" wrote in message
...
I will have an application in which there will be four columns where the
end
user can fill out 100 rows of dollar amounts or percentages. I need to be
able to save those columns as part of saving a "case" which can be
recalled
for editing, etc.

The thought is is to take the 100 rows of the column and store it in a
cell
as an array. Can this be done?