Dynamics array (2002/SP3)

Topic

New Reply

How can I save a range, for example C3:C20, to a dynamic array?

Dim XVal as Double
Redim XV(1 To 100)
XVal = Worksheets(“Sheet1”).Range(C3:C20)

Note that the range always starts at row 3, and can have an unknown number of rows.

Reply | Quote

Replies

Change this
XVal = Worksheets(“Sheet1”).Range(C3:C20)
to
XVal = Worksheets(“Sheet1”).Range(“c3:C” & Cells(1, 3).End(xlDown).Row)

Reply | Quote

Thanks Mike. However, I get a “Type Mismatch” error. Any ideas why?

This is part of my code:

Dim XVal() As Double, strSheetName as String
strSheetName = “Sheet1”
With Worksheets(strSheetName)
ReDim XValues(1 To 100)
XVal = .Range(“c3:C” & Cells(1, 3).End(xlDown).Row) ‘ << error occurs at this line

Reply | Quote

=-One thing I noticed is that you’ve declared XVal as an array with “Double” numbers, but you are trying to assign a range to the entire array.

What are you trying to accomplish?

Reply | Quote

I’m trying to assign values in Column C (or any other column for that matter) to an array so that I can do some kind of function with it, like sort or search for value, and at the end put all values in the array in another worksheet.

Reply | Quote

You can search and sort directly on worksheets – look up the Find and Sort methods of the Range object in the Excel VBA help.

You could do the following:

Dim m As Long
Dim r As Long
m = Range(“C3”).End(xlDown).Row
ReDim XVal(1 To m – 2) As Double
For r = 3 To m
XVal(r – 2) = Range(“C” & r).Value
Next r

Reply | Quote

You may be able to adapt the methodology of the following. It assigns the data in C3 through the end of the continuous range and then moves those numbers to the J column. It then displays the sum of the numbers in the C3:C? range in a message box.

Sub addToArray()
    Dim i As Long, xVal() As Double, cellCount As Long, xRange As Range
    Set xRange = Worksheets("Sheet1").Range("c3:C" & Cells(1, 3).End(xlDown).Row)
    cellCount = xRange.Count
    ReDim xVal(1 To cellCount - 1)
    For i = 1 To cellCount - 1
        xVal(i) = xRange(i)
    Next
For i = 1 To UBound(xVal)
    Cells(i, 10) = xVal(i)
Next

MsgBox Application.WorksheetFunction.Sum(xVal)

End Sub

Reply | Quote

Great help Mike and Hans. Much appriciated.

Reply | Quote

If you declare XVal as a Variant, you can just assign the range to it directly:

Dim XVal 
     XVal = Worksheets("Sheet1").Range(C3:C20).Value

Reply | Quote