On May 23, 2:43 pm, "NickHK" <TungChe...@Invalid.com> 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
>
> <dean.bru...@lion-nathan.com.au> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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
|
Similar Threads
