Why is Excel VBA giving me an error for a dynamic Range?

Question

I am trying to create a budget using a Match function, but I can't even get to that point because the dynamic Range I am trying to create is returning the error: "Application-defined or object-defined error".

The range in question is rng

Sub Material()

Dim wSheet As Worksheet
Dim x, dwIndex, offSet, count, upperLeft, bottomRight, r, wall As Integer
Dim rng As Range

x = 0

For Each wSheet In Worksheets
    If wSheet.Name = "Drywall Pricing" Then
        dwIndex = wSheet.Index - 1
    End If
Next wSheet

For i = 1 To dwIndex
    If Sheets(i).Range("K1").Value > 0 Then
        count = 9
        offSet = 41
        r = 27
        For wall = 1 To count
            offSet = (offSet * wall) - (offSet * 1)
            upperLeft = (r + 16) + offSet
            bottomRight = (r + 27) + offSet
            rng = Sheets(i).Range(Cells(upperLeft, 0), Cells(bottomRight, 1))
        Next wall
    End If
Next i

End Sub

Does anyone have any clue the variable rng is not valid? Any help would be just freaking great because this is getting ridiculous.

Thanks in advance!

Answer 1

Try: Set rng = ..

Any object assignments need the SET keyword :-)

Answer 2

What is the value of upperLeft and bottomRight when the error occurs?
Can that translate to an existing range?

Answer 3

You need to SET the range first.

Set rng = Sheets(i).Range(Cells(upperLeft, 0), Cells(bottomRight, 1))

Answer 4

In addition to needing SET, your Cells properties are unqualified so they will return a Range from the ActiveSheet. Based on your code, I think you want:

With Sheet(i)
     Set rng = .Range(.Cells(upperLeft, 0), .Cells(bottomRight, 1))
End With

Note the periods before the Cells calls.

Answer 5

You also have incorrectly declared most of the variables in the second statement.

Dim x, y as integer

only declares y as an integer, and x as a variant.

Answer 6

I'm not sure if the CELLS call is right or wrong, but I figured out a way to make it work.

Here's what I am using:

upperLeft = (ref + 16) + (offSet * wall)
bottomRight = (ref + 27) + (offSet * wall)
Set rng = Sheets(i).Range("A" & upperLeft & ":B" & bottomRight)

The error stopped occurring with the above formula.

Thanks everyone for your quick replies. First time I've used stackoverflow and I think it's my new favorite thing.

Answer 7

Also be aware that Match will return a range reference not a numeric value. Hence you will alo get an object error if there is no match.