Storing data in an array

[jbrouse]

Greetings,

I'm trying to pass information from one worksheet to another. When my sub-routine is called, it checks for which form the active sheet contains, and calls the appropriate sub-routine. In this sub-routine, all of my data to be copied is set.

Is there any way I can use an array or something similar to store all data in Cells C6:C30, D6:D30, and F6:F30, to be called on at a later time?

 

[pgc01]

Hi jbrouse

I did not fully understand your problem so I'll just answer to:

Is there any way I can use an array or something similar to store all data in Cells C6:C30, D6:D30, and F6:F30, to be called on at a later time?

Example C6:D30 is a range with 25 rows and 2 columns. You can write an array in that range. Ex:

Option Explicit
Option Base 1

Sub ArrRngTest()
Dim arr(25, 2), i As Integer

For i = 1 To 25
    arr(i, 1) = i
    arr(i, 2) = 100 + i
Next

Range("C6:D30") = arr

End Sub

Is this what you want?
PGC

 

[erik.van.geit]

Hi,

the other way around perhaps ?

Option Explicit

Sub use_array()
Dim rng As Range
Dim arr As Variant
Dim I As Long, J As Long

Set rng = Range("C6:D30")

arr = rng

'other code if you want
'next lines would do some operations with the array
For I = 1 To rng.Rows.Count
  For J = 1 To rng.Columns.Count
  arr(I, J) = arr(I, J) & "hello"
  Next J
Next I
'array is written back to sheet
'in this case to same range, but can be another range (needs: same dimensions)
rng = arr
End Sub

NOTE: you cannot pass a range with different areas to one array, unless you use more code ...

kind regards,
Erik

 

[jbrouse]

That's exactly what I needed...Thanks!

I apologize for the confusing post It has been a long day.