For UBound Loop

[Phatie17]

I have a for loop and it is stopping the loop early even though it shouldn't be.

For lnCFCustomCounter = 1 to UBound (vaWPTask)

Next lnCFCustomCounter


When UBound is set to 118 and lnCFCustomCounter gets to 97 it cuts out of the loop and doesnt finish to 118. Any ideas?

[mdmackillop]

I don't think we can help from that information. Can you post your workbook?

[Bob Phillips]

Sure it's not a multi-dimensional array?

[Phatie17]

I actually just found out the problem, now how would I solve it is the new question. So I am setting variant variables and when I start the macro there is only 96 lines. Once the macro runs through, lines are being added but I do not think the variants are being redimensioned. I am not sure if its the variant that is the problem or the UBound that isn't being redimensioned.

'Below I am setting variables. lnEnd is the last row with data. I would like this to change because lines are being added. I added this exact code after the rows have been added also and when the code is stepped through the UBound DOES say 111 instead of 96 but still stops at 96.

With wsWorkPlan

lnEnd = .Range("A65536").End(xlUp).Row
vaWPTask = .Range("A2:A" & lnEnd).Value
vaBuildPartDesc = .Range("D2" & lnEnd).Value
vaSharePointID = .Range("E2:E" & lnEnd).Value
vaCriteria1 = .Range("F2:F" & lnEnd).Value
vaCriteria2 = .Range("G2:G" & lnEnd).Value
vaCriteria3 = .Range("H2:H" & lnEnd).Value
vaCriteria4 = .Range("I2:I" & lnEnd).Value
vaCriteria5 = .Range("J2:J" & lnEnd).Value
vaPhase = .Range("K2:K" & lnEnd).Value
vaWBS = .Range("L2:L" & lnEnd).Value
vaFactorName = .Range("M2:M" & lnEnd).Value

End With

At the start of the macro UBound ends at 96 but once I add rows I would like the UBound to redimension to however many rows I added. When I step through the code the UBound DOES change to however many rows I have added but still stops at 96.


For lnCFCustomCounter = 1 To UBound(vaWPTask)

Next lnCFCustomCounter

[Bob Phillips]

How about changing the For ... Next loop to a Do ... Loop Until ...

The until I guess would be whether the internal counter is greater than the UBound value.

[Norie]

If you want to redimension an array your going to have to do it yourself, VBA won't do it automatically.

[Phatie17]

I tried to redimension the array by using this code

ReDim vaWPTask(UBound(vaWPTask))


and it seems to be redimensioning but the loop still cuts out early. Does VBA allow you to redimension the array in the loop which will actually make the loop run longer.

If it would help I could attach the workbook?

[Bob Phillips]

I assumed that you were already redim'ing it okay as you said that UBound showed 111. If so, then you could do it as I said.

[mdmackillop]

How about a For Each loop
[VBA]For each t in vaWPTask
i = i + 1
[/VBA]

[Norie]

That Redim isn't in the posted code.

I also don't quite get why you are redimming to the existing dimension. ie Ubound(UBound(vaWPTask))

[Phatie17]

Attached is the Orginal Code I had. This code did not have the Redimension code but the UBound still changes to reflect the added rows but kicks out at 96. Thanks for all your help. This is driving me crazy.

[Phatie17]

I actually got it working by using a Do Until loop lilke XLD said but not it gives me and error on the last row

Do Until vaWPTask(lnCFCustomCounter, 1) = ""


When it hits the last row that is empty is gives me a Subscript out of range error.

[Bob Phillips]

Maybe because the array is 0, 1,2,..., n-1, not 1,2,3,...,n

[Phatie17]

Yep that was it figured it out, thanks for all your help