For Each ... Increment Problem

rnmikes

New Member
Joined
Jun 28, 2012
Messages
3
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:
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
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.
 
Upvote 0

Forum statistics

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

Which adblocker are you using?

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
Back
Top