another array approach question

[Gary Keramidas]

let's see if i can explain clearly.

i'm using this to initialize the array
ReDim arr(0 To 1, 0 To 0)

then i use a redim preserve to add the elements

how can i tell how many elements there are to loop through?

ubound(arr) returns 1 and i know there are more elements than that.

 

[Dave Peterson]

To find the number of "rows"
msgbox ubound(arr,1) - lbound(arr,1) + 1

To find the number of "columns" in that array:
msgbox ubound(arr,2) - lbound(arr,2) + 1

If you want the total elements, just multiply these two.

 

[Norman Jones]

Hi Gary,

Try:

Debug.Print "UBound(arr, 1) = "; UBound(arr, 1), _
"UBound(arr, 2) = "; UBound(arr, 2)

 

[Gary Keramidas]

thanks dave

--


Gary

To find the number of "rows"
msgbox ubound(arr,1) - lbound(arr,1) + 1

To find the number of "columns" in that array:
msgbox ubound(arr,2) - lbound(arr,2) + 1

If you want the total elements, just multiply these two.

 

[Gary Keramidas]

thanks again norman

--


Gary

Hi Gary,

Try:

Debug.Print "UBound(arr, 1) = "; UBound(arr, 1), _
"UBound(arr, 2) = "; UBound(arr, 2)

 

[Gary Keramidas]

just curious, is something like this possible?

arr = Range("d2:d21,f2:f21")

 

[Dave Peterson]

Is it possible? Yes.
Does it work the way you want? I bet not.

It'll pick up the values in the first area and plop them into the array.

But you should try it and see.

just curious, is something like this possible?

arr = Range("d2:d21,f2:f21")

 

[Gary Keramidas]

you're right, it probably won't. i was wondering how to get the values from
column f from the array and couldn't.

 

[Dave Peterson]

You could loop through the range:

Option Explicit
Sub testme()

Dim myArr() As Variant
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

With ActiveSheet
Set myRng = .Range("d2:d21")
End With

ReDim myArr(1 To myRng.Rows.Count, 1 To 2)
iRow = 0
For Each myCell In myRng.Cells
iRow = iRow + 1
myArr(iRow, 1) = myCell.Value
myArr(iRow, 2) = myCell.Offset(0, 2).Value
Next myCell

End Sub

you're right, it probably won't. i was wondering how to get the values from
column f from the array and couldn't.

 

[Gary Keramidas]

thanks dave, i have code that works, i was just wondering about the technique i
posted.

i think i may have even adapted some of your code:

ReDim arr(0 To 1, 0 To 0)
For q = 12 To lRowIng
If .Cells(q, "B").Value <> "" Then
ReDim Preserve arr(0 To 1, 0 To arraySize)
arr(0, arraySize) = .Cells(q, "B").Value
arr(1, arraySize) = .Cells(q, "F").Value
arraySize = arraySize + 1
End If
Next

 

[Alan Beban]

If the functions in the file at http:/home.pacbell.net/beban are
available to your workbook, you might consider

lrow = 17
arr = ArrayReshape(MakeArray(Range("D12" & lrow), Range("F12:F" &
lrow), 1), 2, lrow - 12 + 1)

Alan Beban

 

[Dana DeLouis]

Here's something quick-n-dirty...

Sub Example()
Dim Arr
Arr = T2(Array(T1([D221]), T1([F2:F21])))
End Sub


'// Common Library Functions...

Function T1(v)
' Transpose 1 time.
With WorksheetFunction
T1 = .Transpose(v)
End With
End Function

Function T2(v)
' Double Transpose.
With WorksheetFunction
T2 = .Transpose(.Transpose(v))
End With
End Function

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007

thanks dave, i have code that works, i was just wondering about the
technique i posted.

i think i may have even adapted some of your code:

ReDim arr(0 To 1, 0 To 0)
For q = 12 To lRowIng
If .Cells(q, "B").Value <> "" Then
ReDim Preserve arr(0 To 1, 0 To arraySize)
arr(0, arraySize) = .Cells(q, "B").Value
arr(1, arraySize) = .Cells(q, "F").Value
arraySize = arraySize + 1
End If
Next