Software

Ask something
Discussion Locked

Nested For Each

0 helpful
2 replies

by nulubez

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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
Next
Next

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?

2 replies

by slayer_

dim oCell1 as cell
dim oCell2 as cell

I do not remember the object hierarchy, I am guessing

by nulubez

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.

This discussion is closed to new replies.