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? |
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. |
All times are GMT +1. The time now is 01:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com