Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |