For Loop keep resetting itself after 58 iterations

[bluejelly]

Hi

I am having a problem i have never seen before and cannot work out why. I've searched for an answer but i've had no luck.

I have a table of data 57 columns wide. Columns 1 to 8 contain information such as name and date etc. Columns 9 to 57 show "production" numbers; each column represents a 30 minute period during the day. I'm trying to convert this table into a Tableau friendly format: i.e. Name, Date, Time and Production on all one row (one row per time per day per name)

I wrote the code below do this but when the For Loop gets to i=58, i seems to reset itself. If I have i in the watch window and a break point at "Next i", i get to 58 and when i resume the value of i in the watch window goes from 58 to " < Out of context> <out of="" context="">"<out of="" context="">. The code does not resume until i hit F5 again at which point i returns to 1.

Im pretty new to VBA so i'm hoping its an obvious newb mistake. Any help is appreciated!

Sub SmartestTableau()
Dim SourceArray As Variant
Dim OutputArray As Variant
SourceArray = Sheets("merged").UsedRange.Value

ReDim OutputArray(6, 1)

OutputArray(1, 1) = "Name"
OutputArray(2, 1) = ""
OutputArray(3, 1) = "Date"
OutputArray(4, 1) = "Time"
OutputArray(5, 1) = "Production"
OutputArray(6, 1) = "Time"

For i = 1 To UBound(SourceArray, 2)

    If SourceArray(i, 1) = "Yes" Then
    
        For j = 9 To 57

            ReDim Preserve OutputArray(6, UBound(OutputArray, 2) + 1)
            
            OutputArray(1, UBound(OutputArray, 2)) = SourceArray(i, 2)
            OutputArray(2, UBound(OutputArray, 2)) = ""
            OutputArray(3, UBound(OutputArray, 2)) = SourceArray(i, 8)
            OutputArray(4, UBound(OutputArray, 2)) = SourceArray(8, j)
            OutputArray(5, UBound(OutputArray, 2)) = SourceArray(i, j)
            OutputArray(6, UBound(OutputArray, 2)) = SourceArray(8, j)
            
        Next j

    End If


Next i

</out></out>

 

[Norie]

Where is i declared?

After you've run the code through are you getting the correct results?

 

[bluejelly]

Do I need to declare i? Usually my For Loops work without the declaration.

The UBound(SourceArray, 2) is over 10,000. As the code is only getting up to 56 I'm missing a huge amount of data. The code eventually continues (the loop resets to 1 several times) but I have tonnes worked out what causes the code to continue past the loop

 

[skywriter]

I don't understand how UBound(SourceArray, 2) is over 10,000 when you stated your table is 57 columns which would be what SourceArray, 2 is referring to.

Do you have 10,000 columns of data and not the 57 stated?

If you have 10,000 plus rows and want to loop based on that number use 1 instead of 2.

 

[Norie]

If you want to loop through all the rows in the data the first/outer loop should look like this.

For i = 1 To UBound(SourceArray)

Within that loop I would expect to see another loop for the columns which would add a new row to the output row for every iteration.

  For j = 9 To 57

Can you explain further the late out of the source data?

Perhaps post a few rows with dummy data.

 

[bluejelly]

Thanks! this is what is was! (i.e. me getting confused between array dimensions)

Thanks for spending time looking at this for me.

I don't understand how UBound(SourceArray, 2) is over 10,000 when you stated your table is 57 columns which would be what SourceArray, 2 is referring to.

Do you have 10,000 columns of data and not the 57 stated?

If you have 10,000 plus rows and want to loop based on that number use 1 instead of 2.

 

[skywriter]

My pleasure, thanks for the feedback.