General discussion


Nested For Each

By nulubez ·
I am trying to use a nested For Each loop in VBA to find a value from one worksheet on another, compare dates and format a cell if one is greater than the other --

Set rptRng = range("A9:A1009")
Set intRng = range("intList!A1:intList!A1009")
For Each Cell In rptRng
rptNum = Cell.Row
rptID = range("A" + CStr(rptNum)).Value
For Each Cell In intRng
intNum = Cell.Row
intID = range("intList!A" + CStr(intNum)).Value
If rptID = intID Then
rptDate = range("G" + CStr(rptNum)).Value
intDate = range("intList!C" + CStr(intNum)).Value
If intDate > rptDate Then
With range("F" + CStr(rptNum)).Interior
.ColorIndex = 44
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If

I keep getting a "For control variable already in use" error. I understand what that means but how do I reference the "cell" in the second range? Or - what can I use instead of "cell" in the second For Each loop?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Make two objects of type Cell

by Slayer_ In reply to Nested For Each

dim oCell1 as cell
dim oCell2 as cell

I do not remember the object hierarchy, I am guessing

Collapse -

Thank you for the response.....

by nulubez In reply to Make two objects of type ...

That is what I thought to do also but there is no object 'cell' in XP. So I am a different approach with a nested For Each and For loop. What I'm finding tricky is I need to cycle through each row of range1 and check each one of those rows with every row in range2 each time. In other words take the value of "A" in each row of range1 and see if it exists anywhere in range2.

Related Discussions

Related Forums