Geek asks and answers

VBA loop through cell range not working

For some reason, the code below does not work on the second line with an error of "1004" at runtime, specifying the "Intersection" method of the "_Application" object failed and in some cases when I tried to change the code that it produces a '_Global'. The strangest thing is that I played with different versions of this code, and sometimes after many changes during debug mode, I repeated this form and then it starts. If I try to re-run the code, it will work again.

rng1 is a set of cells from the same column, rng2 is a set of cells across several columns with the same rows as rng1

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

The instructions store the values ​​of cells from a single row in rng2 relative to a single record in rng1 in an array. I checked that the ranges are on the same sheet, valid and (where named) refer to 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
+3
source share
1 answer

Try this instead, the only time your code caused errors for me when the intersection was 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 probably should also prefix yours Rangewith a Rowsleaf, for example:

Sheet1.Rows

and

Sheet1.Range("test1")

etc...

+6
source

More articles:


All Articles