For Each ... Increment Problem

[rnmikes]

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

 

[JMonty]

Instead of For Each, why not use For counter?

See below:

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.

 

[mikerickson]

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.

 

[rnmikes]

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

 

[rnmikes]

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

 

[mikerickson]

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

 

[Norie]

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:

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.