skrev i en meddelelse
oups.com...
I have arrays with preexisting data called EDD, WSPT, and SPT
calculated earlier in the program. I would like the user to enter one
of these array names into the worksheet. I then want my program to
read in the array name but then somehow make that correspond to the
matching array with the existing data.
for example
ArrayName=Worksheets("Master").Range("B6").Offset( MasterOffset, 0)
Gives me "EDD"
I then want to output the EDD array:
Worksheets("Hidden").Range("A1").Resize(N_Jobs, 8).Value = ArrayName

How do i accomplish this?



jabraden

I believe it's not possible to do it directly, but here is a workaround.
It will work with Option Base 0 as well as Option Base 1.



Sub GetArray()
'Leo Heuser, 11 Oct. 2006
Dim ArrayName As String
Dim ArrayNames As Variant
Dim DestRange As Range
Dim LbArrayNames As Long
Dim PreArray() As Variant
Dim WhichArray As Long

ArrayNames = Array("EDD", "WSPT", "SPT")

LbArrayNames = LBound(ArrayNames)

ReDim PreArray(LbArrayNames To UBound(ArrayNames))

PreArray(LbArrayNames) = Sheets("Sheet1").Range("A1:A10") ' EDD
PreArray(LbArrayNames + 1) = Sheets("Sheet1").Range("b1:b12") ' WSPT
PreArray(LbArrayNames + 2) = Sheets("Sheet1").Range("c1:c14") ' SPT

ArrayName = Sheets("Sheet1").Range("F1").Value

If Not IsError(Application.Match(ArrayName, ArrayNames, 0)) Then
WhichArray = Application.Match(ArrayName, ArrayNames, 0) + _
(LbArrayNames = 0)

Set DestRange = Sheets("Sheet2").Range("D4"). _
Resize(UBound(PreArray(WhichArray), 1), _
UBound(PreArray(WhichArray), 2))

DestRange.Value = PreArray(WhichArray)
End If
End Sub




--
Best regards
Leo Heuser

Followup to newsgroup only please.