# For Each ... Increment Problem

#### rnmikes

##### New Member
I am trying to increase the integer variable by 1 in a for each loop. I am either getting a value of 1 or a value of total count, but not an incremented value. Help please...

In this one, I always get a value of 1

Range("G2").Select
Dim counter As Integer
Dim N As String
Set myrange = Range(Selection, Selection.End(xlDown))
counter = 1
For Each MyCell In myrange.Cells
MyCell.FormulaR1C1 = "=IF(ISNA(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0)))," & counter & ",(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0))))"
counter = counter + 1
Next MyCell

In this one, I always get a value of 265 which is the total number of cells.

Dim rCell As Range
Dim rRng As Range
Dim counter As Integer
Dim nomatch As String
Dim Number As String

Set rRng = Range(Selection, Selection.End(xlDown))

counter = 1
nomatch = "No Match"
Number = "0"

For Each rCell In rRng.Cells

Number = Str(counter)
ActiveCell.FormulaR1C1 = "=IF(ISNA(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0)))," & counter & ",(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0))))"
counter = counter + 1

Next rCell

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Instead of For Each, why not use For counter?

See below:

Code:
``````Sub MyCounter()
Dim myrange As Range
Dim myrngct As Long
Dim counter as Long

Set myrange = Range("G2").End(xlDown)
myrngct  = myrange.Row

For counter = 2 to myrngct
(Your formula goes here, use counter)
Next i

End Sub``````

This is just a quick something off the top of my head. See if it works.

Last edited:
In the second code (the one that always returns 255), I note that you are putting the formula in ActiveCell in each iteration of the loop (without changing the selection). Thus, after the macro has run, you only see the formula from the last loop.

Have you tried putting a watch on counter and stepping through the code.

Instead of For Each, why not use For counter?

I Tried This:

Dim myrange As Range
Dim myrngct As Long
Dim counter As Long

Set myrange = Range("G2").End(xlDown)
myrngct = myrange.row

For counter = 2 To myrngct
ActiveCell.FormulaR1C1 = "=IF(ISNA(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0)))," & counter & ",(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0))))"
counter = counter + 1
Next counter

And I still get the max of 266 (which is because we started out at 2 instead of 1) for each cell where "counter" is output. Any ideas??

mikeerickson - I am not as smart as that I don't know how to put a watch on counter.

To add a watch, use the Add Watch option under the VB Editor's Debug menu.

Your code works fine for me and puts formulas like this in column G:

G2:=IF(ISNA(INDEX(ALLITEMNAME,MATCH(A2,ALLLABIN,0))),1,(INDEX(ALLITEMNAME,MATCH(A2,ALLLABIN,0))))
G3:=IF(ISNA(INDEX(ALLITEMNAME,MATCH(A3,ALLLABIN,0))),2,(INDEX(ALLITEMNAME,MATCH(A3,ALLLABIN,0))))
etc

One thing you could try is replacing the the hard coded number with a formula:
Rich (BB code):
``````MyRange.FormulaR1C1 = "=IF(ISNA(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0))),ROW()-1
,(INDEX(ALLITEMNAME,MATCH(RC[-6],ALLLABIN,0))))"``````

I've replaced MyCell with MyRange so the formula goes in the entire range in one go and you don't need to loop or have a counter.

Replies
35
Views
2K
Replies
2
Views
346
Replies
12
Views
639
Replies
5
Views
276
Legacy 143009
L
Replies
3
Views
360

Threads
1,211,965
Messages
6,105,131
Members
447,948
Latest member
rzitowsky

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back