VBA loop through range of cells fails

For some reason, the code below fails on the second line with run-time error '1004' stating "Method 'Intersect' of object '_Application' failed" and on some occasions where I have tried to amend the code it produces a '_Global' failure. The stranger thing is that I have played with various versions of this code and sometimes after many changes during debug mode I retried this form and then it runs. If I then try to rerun the code it fails again.

rng1 is a set of 开发者_如何学运维cells from the same column, rng2 is a set of cells over multiple columns with the same rows as rng1

e.g. rng1 = {A2:A10}, rng2 = {D2:H10}

The instructions saves the values of cells from within a single row of rng2 with respect to an individual entry in rng1 into an array. I have checked that the ranges are on the same sheet, valid and (where named) are referencing the correct cells.

For Each c In Range("rng1").Cells

        For Each d In Application.Intersect(Rows(c.Row), Range("rng2")).Cells

            *some instructions here*

        Next d

Next c

Try this instead, the only time your code caused errors for me was when the intersection ended up being empty.

Dim c As Range, d As Range
Dim rng As Range

For Each c In Range("test1")
    Set rng = Application.Intersect(Rows(c.Row), Range("test2"))
    If rng Is Nothing Then
        '' Empty intersection ''
        Debug.Print "Empty"
    Else
        For Each d In rng
            '' some instructions here ''
            Debug.Print d.Address
        Next d
    End If
Next c

Of course you should probably also be prefixing your Range and Rows with the sheet, e.g:

Sheet1.Rows

and

Sheet1.Range("test1")

etcetera...