Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array Lbound not working when only one value in array
Hi-The code below is to use a dyanmic array to loop through the
values. It works with more than one value in the array but does not work with only one value in the array. I end up with a type mismatch. How do I fix? Array Value =23000-7000 CODE: Sub SetupCostCentres() Dim MyArray As Variant Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim Sh As String Application.EnableEvents = False Set rng = Worksheets("CC Info").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) If ArrayCount = 0 Then MsgBox "No Cost Centres Entered" Exit Sub Else ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("Cc Info").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) 'MISMATCHES HERE RANGEVALUES TO "23000-7000" EXPECTING NUMBER For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array Lbound not working when only one value in array
Dean,
Firstly, I do not understand your logic of using COUNTA. That counts the non-blank cells. How do know the non-blanks are at the top of given range ? May you should Sort the column first, or use .End(xlDown) instead. Also, you can assign the range values to an array directly: Dim MyArray As Variant With Worksheets("CC Info") MyArray = .Range(.Range("A2"),.Range("A2").End(xlDown)) End With NickHK wrote in message ups.com... Hi-The code below is to use a dyanmic array to loop through the values. It works with more than one value in the array but does not work with only one value in the array. I end up with a type mismatch. How do I fix? Array Value =23000-7000 CODE: Sub SetupCostCentres() Dim MyArray As Variant Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim Sh As String Application.EnableEvents = False Set rng = Worksheets("CC Info").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) If ArrayCount = 0 Then MsgBox "No Cost Centres Entered" Exit Sub Else ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("Cc Info").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) 'MISMATCHES HERE RANGEVALUES TO "23000-7000" EXPECTING NUMBER For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array Lbound not working when only one value in array
On May 23, 2:43 pm, "NickHK" wrote:
Dean, Firstly, I do not understand your logic of using COUNTA. That counts the non-blank cells. How do know the non-blanks are at the top of given range ? May you should Sort the column first, or use .End(xlDown) instead. Also, you can assign the range values to an array directly: Dim MyArray As Variant With Worksheets("CC Info") MyArray = .Range(.Range("A2"),.Range("A2").End(xlDown)) End With NickHK wrote in message ups.com... Hi-The code below is to use a dyanmic array to loop through the values. It works with more than one value in the array but does not work with only one value in the array. I end up with a type mismatch. How do I fix? Array Value =23000-7000 CODE: Sub SetupCostCentres() Dim MyArray As Variant Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim Sh As String Application.EnableEvents = False Set rng = Worksheets("CC Info").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) If ArrayCount = 0 Then MsgBox "No Cost Centres Entered" Exit Sub Else ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("Cc Info").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) 'MISMATCHES HERE RANGEVALUES TO "23000-7000" EXPECTING NUMBER For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j)- Hide quoted text - - Show quoted text - Nick, By using the code you have shown do I not have to ReDim for the variant size of the array or is that implicit in the statement? How do I structure the LBound and Ubound? Thanks, Dean |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Array Lbound not working when only one value in array
You can just use the same LBound/UBound construct on each dimension as you
did before. NickHK wrote in message ups.com... On May 23, 2:43 pm, "NickHK" wrote: Dean, Firstly, I do not understand your logic of using COUNTA. That counts the non-blank cells. How do know the non-blanks are at the top of given range ? May you should Sort the column first, or use .End(xlDown) instead. Also, you can assign the range values to an array directly: Dim MyArray As Variant With Worksheets("CC Info") MyArray = .Range(.Range("A2"),.Range("A2").End(xlDown)) End With NickHK wrote in message ups.com... Hi-The code below is to use a dyanmic array to loop through the values. It works with more than one value in the array but does not work with only one value in the array. I end up with a type mismatch. How do I fix? Array Value =23000-7000 CODE: Sub SetupCostCentres() Dim MyArray As Variant Dim rng As Range Dim RangeValues As Variant Dim i As Long Dim j As Long Dim Sh As String Application.EnableEvents = False Set rng = Worksheets("CC Info").Range("A2:A100") ArrayCount = Excel.WorksheetFunction.CountA(rng) If ArrayCount = 0 Then MsgBox "No Cost Centres Entered" Exit Sub Else ReDim MyArray(1 To ArrayCount, 1) RangeValues = Worksheets("Cc Info").Range("A2:A" & ArrayCount + 1) For i = LBound(RangeValues, 1) To UBound(RangeValues, 1) 'MISMATCHES HERE RANGEVALUES TO "23000-7000" EXPECTING NUMBER For j = LBound(RangeValues, 2) To UBound(RangeValues, 2) MyArray(i, j) = RangeValues(i, j)- Hide quoted text - - Show quoted text - Nick, By using the code you have shown do I not have to ReDim for the variant size of the array or is that implicit in the statement? How do I structure the LBound and Ubound? Thanks, Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic array | Excel Programming | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Change LBound of array | Excel Programming | |||
Dynamic Array | Excel Programming | |||
Dynamic 2D Array | Excel Programming |