Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamics array (2002/SP3)

    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.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

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

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

    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

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

    =-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?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamics array (2002/SP3)

    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

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

    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.

    <pre>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
    </pre>


  8. #8
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamics array (2002/SP3)

    Great help Mike and Hans. Much appriciated.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dynamics array (2002/SP3)

    If you declare XVal as a Variant, you can just assign the range to it directly:
    <pre>Dim XVal
    XVal = Worksheets("Sheet1").Range(C3:C20).Value
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •