Why does my For Loop stop at 1???

[nickersonpower]

Hi all, I've written a code to extract values from a worksheet and copy to the clipboard so the user can do what they want with the resulting string. In the file I'm working with, there's 10 expected results. I have a For Loop at the end to create one long string with all the results listed. But when I run the macro, I only get the first result in the last MsgBox, and "x" is only =1 rather than 9, which is what I expected (as it starts at 0).

Here's my code:

Please Login or Register  to view this content.

The words "Declare! (>limit)" indicate that the text value in the cell 9 columns to the left (in column B) should be copied; otherwise it should be left alone.

All the elements of my code work properly individually (I've tried them). I can find each of the allergens in the dictionary I created. If I write:

Please Login or Register  to view this content.

I get item 5 from the dictionary. This works for all 10.

Why is my Loop stopping at 1 iteration? I don't get any errors.

I have all the references loaded that I need for this code to work, so it's probably not that.

Help?!

[6StringJazzer]

Your syntax is wrong for this line of code

Please Login or Register  to view this content.

You want this:

Please Login or Register  to view this content.

[6StringJazzer]

Here's a little more of an esoteric explanation for those who care.

The syntax for a For is

For variable = expression1 To expression2

It looks like you thought it was necessary to repeat the variable after the To.

The statement you wrote says:

Iterate the variable x starting at the value 1 and ending at the value

x = (allergenlist.Count - 1)

In VBA, the = sign can be an assignment statement and it is also a compare operator that returns a Boolean value. So the expression you wrote there is evaluated and returns FALSE, because x is not equal to that value. But the For statement is looking for an integer there, so it interprets the FALSE as 0. So your For statement is effectively

Please Login or Register  to view this content.

So it goes through once, then quits.

[nickersonpower]

Thanks so much both, it worked perfectly! I knew I could rely on you

[6StringJazzer]

One last thought: I suggest formatting your code using indentation to show logical structure. Makes it easier to read.

Please Login or Register  to view this content.