Problem Assigning a Range to an Array.

[HelloLloyd]

I am trying to set a range as an array, and the UBound keeps returning a value of 1. Below is the code...

Sub TestArray()
Dim testarray As Variant
testarray = Range("g128:dv128&quot
Box = UBound(testarray)

counter = 1
While counter <= UBound(testarray)
MsgBox testarray(counter, 1)
counter = counter + 1
Wend
End Sub

This code works fine when the range is horizontal, but not vertical like it's coded now. It keeps telling me that the upper bound of the array is 1. I'm sure this is something I'm missing. I'm new to this and learning as I go. Thanks for any help.

 

[ADoozer]

this may be missing the mark a little but have you tried

MsgBox testarray(counter,2)


from MSDN:-

UBound Function

Returns aLong containing the largest available subscript for the indicated dimension of anarray.

Syntax

UBound(arrayname[, dimension])

The UBound function syntax has these parts:

Part Description
arrayname Required. Name of the arrayvariable; follows standard variable naming conventions.
dimension Optional; Variant (Long). Whole number indicating which dimension's upper bound is returned. Use 1 for the first dimension, 2 for the second, and so on. If dimension is omitted, 1 is assumed.


Remarks

The UBound function is used with the LBound function to determine the size of an array. Use the LBound function to find the lower limit of an array dimension.

Dim A(1 To 100, 0 To 3, -3 To 4)

Statement Return Value
UBound(A, 1) 100
UBound(A, 2) 3
UBound(A, 3) 4



good luck

If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

erm... what i meant was that range(g128:dv128) is essentially an
Array(1,[however many cells between g and dv])

wheras range(g128:g200) is essentially an
Array(72[or whatever],1)

apologise if thats the wrong way round but im confusing myself thinking about it!!!

hope you understand my point?!?!?

again good luck If somethings hard to do, its not worth doing - Homer Simpson

 

[HelloLloyd]

What I got out of your postings is that the UBound function only finds the highest row that is filled. What I am actually trying to accomplish is to take some data that is entered into noncontiguous rows, assemble it into an array, determine the frequencty of any given ranges of numbers (i.e. 1 to 25, 26 to 50), and generate another chart from that. However, each of the rows, in addition to not being right next to each other, they are also all of differening lengths. For instance, one row goes through column H, and the other through F. I need the loop that places their values in the array to accomodate for this. I hope this makes any sense to you and you can lend a little help. It seems that I get going in a good direction and the object model just throws me off and laughs as I flounder. Thanks again for the help.

 

[ADoozer]

so, you will have an array(in the respect it will be x by x) rather than a column(1 by x) or row(x by 1)!

or did i misread again?!?!

as an example of ubound works

Dim myarray(10, 2) As Integer

Private Sub Form_Load()

    For i = 0 To 1
        For j = 0 To 9
            myarray(j, i) = i + j
        Next j
    Next i
    
    ub1 = UBound(myarray, 1)
    ub2 = UBound(myarray, 2)
    
End Sub

what i was trying to express was (using your code)

Sub TestArray()
Dim testarray As Variant
testarray = Range(&quot;g128:dv128&quot
Box = UBound(testarray)

counter = 1
While counter <= UBound(testarray)
MsgBox testarray(counter, 1)
counter = counter + 1
Wend
End Sub

ubound(testarray) where testarray(g->dv)

so ubound(testarray,1) will return 1
and ubound(testarray,2) will return val(g-dv)

so to cap it off....

Sub TestArray()
Dim testarray As Variant
testarray = Range(&quot;g128:dv128&quot
myrows = UBound(testarray,1)
mycols = UBound(testarray,2)

counter = 1
While counter <= UBound(testarray,2)
MsgBox testarray(counter, 2)
counter = counter + 1
Wend
End Sub

i hope i made more sense this time(and i hope the code works... ive not tested it yet!!)! If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

ok here is some code i have tested:

you will need a reference to microsoft excel object library (project->references)

Dim myxl As Excel.Application

Dim myarray()

Private Sub Form_Load()
    
    Set myxl = New Excel.Application
    
    myxl.Workbooks.Open "C:\yourexcelfile.xls" 'assumes your file has values in g128 to dv128)

    myxl.Visible = True
    
   myarray = myxl.Range("G128", "DV128")
    ub1 = UBound(myarray, 1)     'this will return 1
    ub2 = UBound(myarray, 2)     'this will return 120

    Set myxl = Nothing
End Sub
[code]

good luck If somethings hard to do, its not worth doing - Homer Simpson