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)

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)

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

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