using For each loop to populate a range
 

i'm trying to use a For Each loop but I can't get it to work. I don't think
that the counter is necessary but i'm not sure how this should be written.
Any help streamlining this would be appreciated.

Static counter As Integer
For Each Row In Range("TotalData")
ImplementPlace = Worksheets("Implementation
Summary").Range("ImplementPlace").Cells(2, 1)
counter = counter + 1
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
15) = "True" Then
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
10) = "True" Then
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "S"
Else
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "IR"
End If
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 3) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 2)
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 4) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 7)
End If

Next Row

using For each loop to populate a range
 

Try something like this:

lngTotalRows = Range("TotalData").Rows.Count

For counter = 1 To lngTotalRows

' Your code here

Next

No need to include the line "counter = counter + 1"

"Sean" wrote:

i'm trying to use a For Each loop but I can't get it to work. I don't think
that the counter is necessary but i'm not sure how this should be written.
Any help streamlining this would be appreciated.

Static counter As Integer
For Each Row In Range("TotalData")
ImplementPlace = Worksheets("Implementation
Summary").Range("ImplementPlace").Cells(2, 1)
counter = counter + 1
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
15) = "True" Then
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
10) = "True" Then
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "S"
Else
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "IR"
End If
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 3) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 2)
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 4) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 7)
End If

Next Row

using For each loop to populate a range
 

Hi,

I don't think the main problem is your use of the "For Each" loop, I think
there are other elements that will not allow your code to work.
1. Why do you declare the counter variable as a class variable?
2. I don't understand your use of the Range object. Usually you would have
an expression like "Range("e1")" to refer to the cell E1, or "Range("e1,
f2")" to refer to the group of cells that spans from E1 to F2, or even
"Range(rngName)" if you have written the coordinates into a string variable
(i.e. rngName="e2"), but not a string that is not the reference to a cell or
cell range.
3. And of course if you're using "For Each" you do not need any counter that
specifies the next cell, as that is done automatically. The idea behind the
"For Each" statement is to do away with using a counter, increasing it and
checking whether the last item has been reached. In your code you are using
both forms at the same time.

Regards,

Alex


"Sean" wrote:

i'm trying to use a For Each loop but I can't get it to work. I don't think
that the counter is necessary but i'm not sure how this should be written.
Any help streamlining this would be appreciated.

Static counter As Integer
For Each Row In Range("TotalData")
ImplementPlace = Worksheets("Implementation
Summary").Range("ImplementPlace").Cells(2, 1)
counter = counter + 1
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
15) = "True" Then
If Worksheets("Post-Assessment").Range("TotalData").Cells(counter,
10) = "True" Then
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "S"
Else
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 2) = "IR"
End If
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 3) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 2)
Worksheets("Implementation
Summary").Range("ImplementMeth").Cells(ImplementPl ace, 4) =
Worksheets("Post-Assessment").Range("TotalData").Cells(counter, 7)
End If

Next Row