none
Object variable or with block variable ot set RRS feed

  • Question

  • Dear Sir:

    I made the following program:

    Private Sub GetSelectedRange(rng1 As Range)
        Dim numRow As Long, numRow1 As Long, i As Long, j As Long, k As Long
        Dim RangeSelect() As Range
        Dim myArray()
        ReDim RangeSelect(1 To Selection.Areas.Count)
        For i = 1 To Selection.Areas.Count
            Set RangeSelect(i) = Selection.Areas(i)
        Next i
        numRow = 0
        For i = 1 To Selection.Areas.Count
            numRow = numRow + RangeSelect(i).Rows.Count
        Next i
        ReDim myArray(1 To numRow, 1 To RangeSelect(1).Columns.Count)
        numRow1 = 0
        For i = 1 To Selection.Areas.Count
            For j = 1 To RangeSelect(i).Rows.Count
                numRow1 = numRow1 + 1
                For k = 1 To RangeSelect(i).Columns.Count
                    myArray(numRow1, k) = RangeSelect(i).Cells(j, k)
                Next k
            Next j
        Next i
            Set rng1.Value = myArray
    End Sub

    It showed "Object variable or with block variable not set". When I debugged it, it highlighted line "Set rng1.vaue=myArray". Would you please help see what is the problem and how to fix it? Your support is highly appreciated!

    Regards,

    Shell Hu

    Monday, November 21, 2016 9:17 AM

Answers

  • An array is not an object yet alone a Range object which is the only type of object you can assign to a range variable. Here what you are doing is to write the contents of the array to the range's value property, the range can be multiple cells. Remove the Set

    rng1.Value = myArray

    If you want to write the entire contents of the array you might first want to ensure the range is large enough

    Set rng1 = rng1.Resize(UBound(myArray), UBound(myArray, 2))

     

    • Marked as answer by Shell Hu Monday, November 21, 2016 11:44 PM
    Monday, November 21, 2016 9:48 AM
    Moderator
  • Dear Sir:

    I revised my code as following:

    Private Sub GetSelectedRange(rng As Range)
        Dim numRow As Long, numRow1 As Long, i As Long, j As Long, k As Long
        Dim RangeSelect() As Range
        Dim myarray()
        Dim msg As String
        Dim rng1 As Range
        ReDim RangeSelect(1 To Selection.Areas.Count)
        For i = 1 To Selection.Areas.Count
            Set RangeSelect(i) = Selection.Areas(i)
        Next i
        numRow = 0
        For i = 1 To Selection.Areas.Count
            numRow = numRow + RangeSelect(i).Rows.Count
        Next i
        ReDim myarray(1 To numRow, 1 To RangeSelect(1).Columns.Count)
        numRow1 = 0
        For i = 1 To Selection.Areas.Count
            For j = 1 To RangeSelect(i).Rows.Count
                numRow1 = numRow1 + 1
                For k = 1 To RangeSelect(i).Columns.Count
                    myarray(numRow1, k) = RangeSelect(i).Cells(j, k)
                Next k
            Next j
        Next i
        Set rng1 = Range(Cells(1, 1), Cells(numRow, RangeSelect(1).Columns.Count))
        rng1.Value = myarray
        Set rng = rng1
               
           
    End Sub

    Now it is working now. Thanks a lot for your help!

    • Marked as answer by Shell Hu Monday, November 21, 2016 11:43 PM
    Monday, November 21, 2016 11:43 PM

All replies

  • An array is not an object yet alone a Range object which is the only type of object you can assign to a range variable. Here what you are doing is to write the contents of the array to the range's value property, the range can be multiple cells. Remove the Set

    rng1.Value = myArray

    If you want to write the entire contents of the array you might first want to ensure the range is large enough

    Set rng1 = rng1.Resize(UBound(myArray), UBound(myArray, 2))

     

    • Marked as answer by Shell Hu Monday, November 21, 2016 11:44 PM
    Monday, November 21, 2016 9:48 AM
    Moderator
  • Dear Sir:

    I've done as you instructed. But the result is the same.

    Monday, November 21, 2016 10:26 AM
  • Are you sure the passed object variable rng1 refers to a range? Show us the code you used to call GetSelectedRng and in particular how you assigned rng1

    What are you actually trying to do with this routine?

    Monday, November 21, 2016 2:08 PM
    Moderator
  • Dear Sir:

    I revised my code as following:

    Private Sub GetSelectedRange(rng As Range)
        Dim numRow As Long, numRow1 As Long, i As Long, j As Long, k As Long
        Dim RangeSelect() As Range
        Dim myarray()
        Dim msg As String
        Dim rng1 As Range
        ReDim RangeSelect(1 To Selection.Areas.Count)
        For i = 1 To Selection.Areas.Count
            Set RangeSelect(i) = Selection.Areas(i)
        Next i
        numRow = 0
        For i = 1 To Selection.Areas.Count
            numRow = numRow + RangeSelect(i).Rows.Count
        Next i
        ReDim myarray(1 To numRow, 1 To RangeSelect(1).Columns.Count)
        numRow1 = 0
        For i = 1 To Selection.Areas.Count
            For j = 1 To RangeSelect(i).Rows.Count
                numRow1 = numRow1 + 1
                For k = 1 To RangeSelect(i).Columns.Count
                    myarray(numRow1, k) = RangeSelect(i).Cells(j, k)
                Next k
            Next j
        Next i
        Set rng1 = Range(Cells(1, 1), Cells(numRow, RangeSelect(1).Columns.Count))
        rng1.Value = myarray
        Set rng = rng1
               
           
    End Sub

    Now it is working now. Thanks a lot for your help!

    • Marked as answer by Shell Hu Monday, November 21, 2016 11:43 PM
    Monday, November 21, 2016 11:43 PM