Method 'Range' of object '_Worksheet' failed

Question

I'm trying to use the following code to generate data on one sheet by taking data from another. I've beed doing similar things with VBA for a while and haven't encountered this problem until now. I've looked through quite a few other questions with the same error but haven't found what I need.

My code started crashing with only the message "400", so I added an error hander and now get the message:

Method 'Range' of object '_Worksheet' failed.

My code (on sheet "Buyers"):

Public Sub MakeSheets(Optional ByVal SortSource As Boolean = False, Optional ByVal ExportSheets As Boolean = False)

    On Error GoTo ErrorMsg

    Dim r, n As Integer 'for looping through [r]ows and iterating [n]ew lines added to sheets

    If SortSource Then 'This code never executes because I'm only using the default boolean values so far
        Sheets("Buyers").UsedRange.Sort key1:=FullRange("NAME"), Header:=xlYes
        Sheets("Properties").UsedRange.Sort key1:=Sheets("Properties").FullRange("STREET NAME"), key2:=Sheets("Properties").FullRange("NUMBER"), Header:=xlYes
    End If

    Sheets("Ownership").Rows("3:" & Sheets("Ownership").UsedRange.Rows.Count).Delete
    n = 2 'last title row of target worksheet
    With Sheets("Properties")
    MsgBox .Cells(3, .FullRange("STREET NAME").Column).Value 'Successfully displays the appropriate cell value
        For r = 2 To .PropertyRows()
            If Not IsEmpty(.Cells(r, .FullRange("OWNER").Column).Value) Then
                n = n + 1
                MsgBox n 'displays "3", confirming this point is successfully reached once.
                'Problem appears to be next line
                Sheets("Ownership").Range("A" & n & ":L" & n).Value = Array( _
                    .Cells(r, .FullRange("MLS#").Column).Value, _
                    .Cells(r, .FullRange("LIST PRICE").Column).Value, _
                    .Cells(r, .FullRange("NUMBER").Column).Value & " " & .Cells(r, .FullRange("STREET NAME").Column).Value, _
                    .Cells(r, .FullRange("OWNER").Column).Value & " " & .Cells(r, .FullRange("OWNED").Column).Value, _
                    .Cells(r, .FullRange("SUBDIVISION").Column).Value, _
                    .Cells(r, .FullRange("COUNTY").Column).Value, _
                    .Cells(r, .FullRange("BED").Column).Value, _
                    .Cells(r, .FullRange("BATH").Column).Value, _
                    .Cells(r, .FullRange("YEAR BUILT").Column).Value, _
                    .Cells(r, .FullRange("TAXES").Column).Value, _
                    .Cells(r, .FullRange("TAX YEAR").Column).Value, _
                    .Cells(r, .FullRange("ACQUISITION DATE").Column).Value)
            End If
        Next r
    End With
    Exit Sub

ErrorMsg:
    MsgBox Err.Description
End Sub

I can post the code for FullRange if requested, but it calls a couple of other custom functions, and I think the MsgBox I threw in before the loop starts ably demonstrates that it isn't the problem. (There is a public FullRange function on both "Buyers" and "Properties", each referring to their own sheets. Both are used extensively elsewhere by code in both sheets without issue.)

I've also tried completely removing the With block in case that was creating some strange conflict, but the results were identical.

What happens if you try to set some fixed array to the range at that point? What happens if you replace the .FullRange call with some constant? i.e. is the problem with Sheets("Ownership").Range or within the array? You could also set a breakpoint at that line and test it yourself in the immediate window. — arcadeprecinct
@arcadeprecinct, my generic test Sheets("Ownership").Range("A3:L3").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) worked perfectly. When I set a breakpoint within FullRange, it's reached the first two times but not when the array is built. I think the problem is with Range. — Josh
Try putting the array into an array variable Dim t() as variant Then t = Array(... Then stop your code at the next line and see if t is filled properly. — Scott Craner
Could someone please enlighten me? I have never seen / heard of .PropertyRows() (which seems to be a property of the Sheets collection). I tried to find it on MSDN but there doesn't seem to be anything like this. In the VBE library (pressing F2) I couldn't find it either. — Ralph
Also instead of continually calling the function to get the proper columns over and over, put the values into their own variables. Then use those variables in your array. That way you avoid the code continually calling the sheet for the same number. It will speed up the process. — Scott Craner

Josh Josh · Accepted Answer · 2016-07-18T01:57:25

It turns out something fishy was going on under the hood of Excel or my computer. I had already Quit and reopened Excel before posting, but then I started getting some quirky behavior (such as Macro's disappearing from the run macro menu) so I rebooted the whole machine. My original code is working fine now. Thanks for looking with me guys.

Method 'Range' of object '_Worksheet' failed

1 Answers