VBA nested for loop won't Run

Question

Hi I'm working on a macro in VBA for excel. I have a nested for loop in my code as seen below. The second loop does not run; even a MsgBox() command within the second loop doesn't result in any action and the program just seems to skip over the nested loop with no reported errors.

In plain English this nested loop should:

1) Take the string from the i entry in the array categories_string (first for loop).

2) iterate through the 300+ rows in column "AE" in the excel file (second for loop, "length" is the length of the column of data in "AE")

3) look for string matches and add 1 to the corresponding entry in the categories_value array which is populated with variables set to 0 (the if statement).

For i = LBound(categories_string) To UBound(categories_string)
   For p = 1 To p = length
      If Worksheets("sheet1").Cells(p + 2, "AE").Value = categories_string(i) Then
        categories_value(i) = categories_value(i) + 1
      End If
   Next
Next

Since you already know how to work with arrays, why not put the values in Column AE into an array and cycle through that instead? It would be quicker then looping the cells themselves. AEArray = Worksheets("sheet1").Range("AE3:AE" & length).Value then do another for loop on this array instead. — Scott Craner
– Scott Craner, Commented Jul 12, 2016 at 19:35
Your answer fixed it. And great suggestion! this is only my 2nd day of using VBA so any advice is appreciated. — PSwalker
– PSwalker, Commented Jul 12, 2016 at 19:48
Sorry, @ScottCraner - your comment didn't even register, or I'd have told you to make it an answer so you could get your magical internet points... — FreeMan
– FreeMan, Commented Jul 12, 2016 at 20:08

FreeMan · Accepted Answer · 2016-07-12 19:46:02Z

4

Change

   For p = 1 To p = length

to

   For p = 1 To length

Should fix you right up.

You may want to consider Scott Cranner's comment as well, especially if your categories_string is large and length is large. Or, just do it for the practice

answered Jul 12, 2016 at 19:46

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA nested for loop won't Run

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related