Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Array from list that may change in size
On Apr 11, 6:02 pm, "NickHK" wrote:
Dean, If you are getting the values from a range, you can do it in a single call. Note that you will always get a 2 dimensional array though, even if you only have a single row or column. Private Sub CommandButton2_Click() Dim RangeValues As Variant Dim i As Long Dim j As Long RangeValues = Range("A1:A3") For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) Debug.Print i & "," & j & " : " & RangeValues(i, j) Next Next End Sub NickHK wrote in message oups.com... On Apr 11, 3:47 pm, "NickHK" wrote: Dean, Adjust for your required data type: Public Function SomeFunction(ArrayCount As Long) Dim MyArray() As Long ReDim MyArray(1 To ArrayCount) 'etc... End Function You will probably need to use LBound/Ubound also. May looking "Option Base" also. NickHK wrote in message roups.com... I want to allow the user to type in a list of cost centres and have the code create an array from that list. The list may 1 record or it may be 100. How do I create a dynamic array?- Hide quoted text - - Show quoted text - Hi Nick Thanks for the help! How do I fill it after I have redim'd the array? I have created the code below that correctly returns the correct redim count of 5. How do I fill only the 5 values from the range? Function ArrayDimension() Dim rng As Range Dim MyArray() As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount) MyArray = rng.Value Debug.Print MyArray I keep getting a type mismatch. Please advise. Thanks- Hide quoted text - - Show quoted text - Ahh, that's great. Thanks Nick! I was having difficulty understanding the UBound and LBound in this case. Now it is clear! Cheers! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Array from list that may change in size
On Apr 12, 9:06 am, wrote:
On Apr 11, 6:02 pm, "NickHK" wrote: Dean, If you are getting the values from a range, you can do it in a single call. Note that you will always get a 2 dimensional array though, even if you only have a single row or column. Private Sub CommandButton2_Click() Dim RangeValues As Variant Dim i As Long Dim j As Long RangeValues = Range("A1:A3") For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) Debug.Print i & "," & j & " : " & RangeValues(i, j) Next Next End Sub NickHK wrote in message roups.com... On Apr 11, 3:47 pm, "NickHK" wrote: Dean, Adjust for your required data type: Public Function SomeFunction(ArrayCount As Long) Dim MyArray() As Long ReDim MyArray(1 To ArrayCount) 'etc... End Function You will probably need to use LBound/Ubound also. May looking "Option Base" also. NickHK wrote in message roups.com... I want to allow the user to type in a list of cost centres and have the code create an array from that list. The list may 1 record or it may be 100. How do I create a dynamic array?- Hide quoted text - - Show quoted text - Hi Nick Thanks for the help! How do I fill it after I have redim'd the array? I have created the code below that correctly returns the correct redim count of 5. How do I fill only the 5 values from the range? Function ArrayDimension() Dim rng As Range Dim MyArray() As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount) MyArray = rng.Value Debug.Print MyArray I keep getting a type mismatch. Please advise. Thanks- Hide quoted text - - Show quoted text - Ahh, that's great. Thanks Nick! I was having difficulty understanding the UBound and LBound in this case. Now it is clear! Cheers!- Hide quoted text - - Show quoted text - Hi Nick, I created the following on your advice and it works on the debugprint. I wanted to use this array in a multiple of different subs later. How do I call it in other subs? Example below. Always gives me subscript out of range. Function ArrayDimension() Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim a As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) Debug.Print MyArray(i, j) Next Next ' I assume here that MyArray is full of the new values. Want to call the whole array later. In the following sub(small extract of). End Function Set DestSh = ThisWorkbook.Worksheets("CSV") For Each sh In Sheets(MyArray()) "HERE IS WHERE I WANT TO CALL IT. IT STORES THE SHEET NAMES REQ"D Last = LastRow(DestSh) With sh.Range("A6:Q213") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next Thanks again. Sorry but am new to this. Cheers, Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Array from list that may change in size
Dean,
You need to check of Scope of Variables. As the array is defined in the sub, once the sub finishes, it falls out of scope and hence cannot be referenced. If you need to access its values across many subs, Dim the array in the Declaration section the module or make it Public in a standard module. NickHK wrote in message ups.com... On Apr 12, 9:06 am, wrote: On Apr 11, 6:02 pm, "NickHK" wrote: Dean, If you are getting the values from a range, you can do it in a single call. Note that you will always get a 2 dimensional array though, even if you only have a single row or column. Private Sub CommandButton2_Click() Dim RangeValues As Variant Dim i As Long Dim j As Long RangeValues = Range("A1:A3") For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) Debug.Print i & "," & j & " : " & RangeValues(i, j) Next Next End Sub NickHK wrote in message roups.com... On Apr 11, 3:47 pm, "NickHK" wrote: Dean, Adjust for your required data type: Public Function SomeFunction(ArrayCount As Long) Dim MyArray() As Long ReDim MyArray(1 To ArrayCount) 'etc... End Function You will probably need to use LBound/Ubound also. May looking "Option Base" also. NickHK wrote in message roups.com... I want to allow the user to type in a list of cost centres and have the code create an array from that list. The list may 1 record or it may be 100. How do I create a dynamic array?- Hide quoted text - - Show quoted text - Hi Nick Thanks for the help! How do I fill it after I have redim'd the array? I have created the code below that correctly returns the correct redim count of 5. How do I fill only the 5 values from the range? Function ArrayDimension() Dim rng As Range Dim MyArray() As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount) MyArray = rng.Value Debug.Print MyArray I keep getting a type mismatch. Please advise. Thanks- Hide quoted text - - Show quoted text - Ahh, that's great. Thanks Nick! I was having difficulty understanding the UBound and LBound in this case. Now it is clear! Cheers!- Hide quoted text - - Show quoted text - Hi Nick, I created the following on your advice and it works on the debugprint. I wanted to use this array in a multiple of different subs later. How do I call it in other subs? Example below. Always gives me subscript out of range. Function ArrayDimension() Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim a As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) Debug.Print MyArray(i, j) Next Next ' I assume here that MyArray is full of the new values. Want to call the whole array later. In the following sub(small extract of). End Function Set DestSh = ThisWorkbook.Worksheets("CSV") For Each sh In Sheets(MyArray()) "HERE IS WHERE I WANT TO CALL IT. IT STORES THE SHEET NAMES REQ"D Last = LastRow(DestSh) With sh.Range("A6:Q213") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next Thanks again. Sorry but am new to this. Cheers, Dean |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Array from list that may change in size
On Apr 12, 11:45 am, "NickHK" wrote:
Dean, You need to check of Scope of Variables. As the array is defined in the sub, once the sub finishes, it falls out of scope and hence cannot be referenced. If you need to access its values across many subs, Dim the array in the Declaration section the module or make it Public in a standard module. NickHK wrote in message ups.com... On Apr 12, 9:06 am, wrote: On Apr 11, 6:02 pm, "NickHK" wrote: Dean, If you are getting the values from a range, you can do it in a single call. Note that you will always get a 2 dimensional array though, even if you only have a single row or column. Private Sub CommandButton2_Click() Dim RangeValues As Variant Dim i As Long Dim j As Long RangeValues = Range("A1:A3") For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) Debug.Print i & "," & j & " : " & RangeValues(i, j) Next Next End Sub NickHK wrote in message roups.com... On Apr 11, 3:47 pm, "NickHK" wrote: Dean, Adjust for your required data type: Public Function SomeFunction(ArrayCount As Long) Dim MyArray() As Long ReDim MyArray(1 To ArrayCount) 'etc... End Function You will probably need to use LBound/Ubound also. May looking "Option Base" also. NickHK wrote in message roups.com... I want to allow the user to type in a list of cost centres and have the code create an array from that list. The list may 1 record or it may be 100. How do I create a dynamic array?- Hide quoted text - - Show quoted text - Hi Nick Thanks for the help! How do I fill it after I have redim'd the array? I have created the code below that correctly returns the correct redim count of 5. How do I fill only the 5 values from the range? Function ArrayDimension() Dim rng As Range Dim MyArray() As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount) MyArray = rng.Value Debug.Print MyArray I keep getting a type mismatch. Please advise. Thanks- Hide quoted text - - Show quoted text - Ahh, that's great. Thanks Nick! I was having difficulty understanding the UBound and LBound in this case. Now it is clear! Cheers!- Hide quoted text - - Show quoted text - Hi Nick, I created the following on your advice and it works on the debugprint. I wanted to use this array in a multiple of different subs later. How do I call it in other subs? Example below. Always gives me subscript out of range. Function ArrayDimension() Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim a As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) Debug.Print MyArray(i, j) Next Next ' I assume here that MyArray is full of the new values. Want to call the whole array later. In the following sub(small extract of). End Function Set DestSh = ThisWorkbook.Worksheets("CSV") For Each sh In Sheets(MyArray()) "HERE IS WHERE I WANT TO CALL IT. IT STORES THE SHEET NAMES REQ"D Last = LastRow(DestSh) With sh.Range("A6:Q213") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next Thanks again. Sorry but am new to this. Cheers, Dean- Hide quoted text - - Show quoted text - Hi Nick, Sorry to get to this so late but was seconded off for a while. I have declared the array as a public variable in a module and still cannot get the array called in a sub later. I have both the codes below. Please advise as I think that it is probably a rookie error on the syntax somewhe Public MyArray As Variant Public Function CSV() ArrayDimension CreateCSV End Function Public Function ArrayDimension() Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim a As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) Debug.Print MyArray(i, j) Next Next End Function Public Sub CreateCSV() Dim Sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim MyArray As Variant On Error Resume Next If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then On Error GoTo 0 Application.ScreenUpdating = False Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "CSV" For Each Sh In Sheets(MyArray) Last = LastRow(DestSh) With Sh.Range("A6:Q281") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next DestSh.Cells(1).Select Application.ScreenUpdating = True Else Set DestSh = ThisWorkbook.Worksheets("CSV") For Each Sh In Sheets(MyArray(i, j)) Last = LastRow(DestSh) With Sh.Range("A6:Q213") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next DestSh.Cells(1).Select Application.ScreenUpdating = True End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Array from list that may change in size
Dean,
- Most of your "ArrayDimension" function does nothing but copy values from one array to another. You can achieve the whole this with a single line. - Again your problem with MyArray is one of scope. Whilst you have declared it at the module level, you have also declared (but not populated in any way) it with the sub, at local level. So this local, empty variable is used. Remove this local variable. - Not sure what your code in "CreateCSV" is trying to do. What should be the values of MyArray at this stage ? It will be a 2-D array. NickHK Option Explicit Public MyArray As Variant Public Function CSV() ArrayDimension CreateCSV End Function 'You don't really need this in a function now, as it is only a single line Public Function ArrayDimension() MyArray = Worksheets(1).Range(Range("A2"), Range("A100").End(xlUp)) End Function Public Sub CreateCSV() Dim DestSh As Worksheet 'Dim MyArray As Variant 'Try to reference the desired sheet On Error Resume Next Set DestSh = ThisWorkbook.Worksheets("CSV") 'Return to default error handling On Error GoTo 0 If DestSh Is Nothing Then 'No such sheet.. Application.ScreenUpdating = False 'So create it Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "CSV" '..Not sure what you are doing here '.. Application.ScreenUpdating = True Else '..Again not sure what you are doing here 'but something a bit different to the If block above ? '.. End If End Sub NickHK ----------------------- CUT ---------------------------------- Hi Nick, Sorry to get to this so late but was seconded off for a while. I have declared the array as a public variable in a module and still cannot get the array called in a sub later. I have both the codes below. Please advise as I think that it is probably a rookie error on the syntax somewhe Public MyArray As Variant Public Function CSV() ArrayDimension CreateCSV End Function Public Function ArrayDimension() Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim a As Long Set rng = Worksheets("TEST").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) Debug.Print MyArray(i, j) Next Next End Function Public Sub CreateCSV() Dim Sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim MyArray As Variant On Error Resume Next If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then On Error GoTo 0 Application.ScreenUpdating = False Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "CSV" For Each Sh In Sheets(MyArray) Last = LastRow(DestSh) With Sh.Range("A6:Q281") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next DestSh.Cells(1).Select Application.ScreenUpdating = True Else Set DestSh = ThisWorkbook.Worksheets("CSV") For Each Sh In Sheets(MyArray(i, j)) Last = LastRow(DestSh) With Sh.Range("A6:Q213") DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With Next DestSh.Cells(1).Select Application.ScreenUpdating = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WHY OH WHY! ... creating a dynamic array of outlookmail items, then display them. | Excel Programming | |||
Creating a dynamic list | Excel Worksheet Functions | |||
Creating a dynamic list | Excel Worksheet Functions | |||
Creating a dynamic list | Excel Programming | |||
size of multidimensional dynamic array | Excel Programming |