For loop starts with "to" value

[F-150 Phill]

I've been searching for an answer to this for some time but the keywords always bring back the wrong results.

I'm trying to run a simple for loop but when it starts running it starts with the "to" value.

i.e.
For A = Top to Bottom
do something
Next A

In this example, lets say "Top" = 5 and "Bottom" = 20, on the first run of the loop it will start with the 20 and count up from there (A=20, A=21, A=22 etc). "Top" and "Bottom" are variables with the values set earlier in the macro and the "Top" value is always smaller than the "Bottom" value.

If I write the loop like this instead:

For A = Bottom to Top
do something
Next A

where "Top" is still 5 and "Bottom" is still 20, it will start with the value for "Top" and count on (A=5, A=6 etc).

It doesn't work this way for all my loops (there are multiple in this macro), only this one, and this has happened before. What am I doing wrong? Am I using variables that are preprogrammed to do other things? I'm beating my head against the wall trying to figure it out!

 

[MrKowz]

That doesn't sound right. Can you please post your actual code?

 

[F-150 Phill]

How much do you need? It's a long convoluted sub that calls other subs to get other values between multiple worksheets. I'll paste the loop itself simply because the rest is really long. If you want more of the text let me know and I'll put more.

Here's the loop:

For TWK = VSTop(VSGROUP) To VSBottom(VSGROUP)
For LWK = Top to BOTTOM
If Cells(TWK, 2).Value = Cells(LWK, 39).Value Then
Range(Cells(TWK, 27), Cells(TWK, 30)).Value = Range(Cells(LWK, 39), Cells(LWK, 42)).Value
Range(Cells(LWK, 39), Cells(LWK, 42)).ClearContents
Exit For
End If
Next LWK
Next TWK

This loop works a lot like a VLookup:

-TWK (this week) is a range on the left side of the sheet from rows "VSTop()" to "VSBottom()"
-LWK (last week) is a range on the right side of the sheet from rows "Top" to "BOTTOM"

Once TWK finds a match in LWK it should copy the data to another position, delete the old data and go to the next row of TWK.

As soon as it starts on the LWK loop it starts with the "BOTTOM" value and counts up.

for my first run of this loop (VSGROUP = 1):
VSTop(VSGROUP)=5
VSBottom(VSGROUP)=20
Top=4
BOTTOM=90

When it runs the LWK loop it will start with 90 and count up.

 

[MrKowz]

How much do you need? It's a long convoluted sub that calls other subs to get other values between multiple worksheets. I'll paste the loop itself simply because the rest is really long. If you want more of the text let me know and I'll put more.

Here's the loop:

For TWK = VSTop(VSGROUP) To VSBottom(VSGROUP)
For LWK = Top to BOTTOM
If Cells(TWK, 2).Value = Cells(LWK, 39).Value Then
Range(Cells(TWK, 27), Cells(TWK, 30)).Value = Range(Cells(LWK, 39), Cells(LWK, 42)).Value
Range(Cells(LWK, 39), Cells(LWK, 42)).ClearContents
Exit For
End If
Next LWK
Next TWK

This loop works a lot like a VLookup:

-TWK (this week) is a range on the left side of the sheet from rows "VSTop()" to "VSBottom()"
-LWK (last week) is a range on the right side of the sheet from rows "Top" to "BOTTOM"

Once TWK finds a match in LWK it should copy the data to another position, delete the old data and go to the next row of TWK.

As soon as it starts on the LWK loop it starts with the "BOTTOM" value and counts up.

for my first run of this loop (VSGROUP = 1):
VSTop(VSGROUP)=5
VSBottom(VSGROUP)=20
Top=4
BOTTOM=90

When it runs the LWK loop it will start with 90 and count up.

Try stepping through your code with F8, when it gets inside of the loop, hover your mouse over "Top" and "BOTTOM", what values does it show there? I have never seen the behaviour you are describing before, and there is nothing in the code you supplied that is out of the ordinary.

 

[Jonmo1]

That's not the whole code.
We need to see how Top and Bottom recieve their values.
We also need to see the code for the functions VSTop and VSBottom


I would do this for troubleshooting purposes

For TWK = VSTop(VSGROUP) To VSBottom(VSGROUP)
    For LWK = Top to BOTTOM
        Msgbox "TWK = " & TWK & vbNewLine & "LWK = " & LWK & vbNewLine & "Top = " & Top & vbNewLine & "BOTTOM = " & BOTTOM
    Next LWK
Next TWK

 

[F-150 Phill]

Since I'm still in the process of building the code, I've been stepping through it with F8 and that's how I found the problem.

I apologize to your braincells but here's the entire code (you'll have to forgive the mess as I'm basically self taught on macros and probably haven't learned the most efficient methods for writing code)

Public SHT As Worksheet
Public PivotVS() As Variant, PivotVSQTY() As Integer
Public ProjectName As String, PivotVSTop() As Integer, PivotVSBottom() As Integer
-----------------------------------------------------
Sub PivotDataRevisit()
Dim PROJECT As Integer, VSGROUP As Integer
Dim PR() As Variant, VS() As Variant, VSTop() As Variant, VSBottom() As Variant
Dim PivotSheet As Worksheet
'<<<<<<<<Establish project names.>>>>>>>>
For Each Sheet In ActiveWorkbook.Sheets
'If Sheet.Visible <> xlSheetHidden Then Sheet.Select
If InStr(Sheet.Name, "PIVOT") > 0 Then
PROJECT = PROJECT + 1
ReDim Preserve PR(PROJECT)
PR(PROJECT) = Right(Sheet.Name, 3)
End If
Next Sheet

For PROJECT = 1 To 8
'<<<<<<<<Determine what Value Stream Groups are on the PivotTable.>>>>>>>>
Sheets("PIVOT " & PR(PROJECT)).Select
Set PivotSheet = ActiveSheet
NumberOfValueStreams
Sheet1.Select
'MsgBox "OK"
'<<<<<<<<Establish top and bottom of project on Layout Sheet.>>>>>>>>
If ProjArea <> "" Then GoTo ProjArea:
For ProjArea = 1 To 10000
If Cells(ProjArea, 3).Value = PR(PROJECT) Then
Top = ProjArea + 2
'MsgBox "OK"
End If
If Cells(ProjArea, 3).Value = "Grand Total" Then
'MsgBox "OK"
BOTTOM = ProjArea
Exit For
End If
ProjArea: Next ProjArea
'<<<<<<<<Move last week's data over to be sorted to new data later.>>>>>>>>
'MsgBox "OK"
'Range(Sheet1.Cells(Top, 39), Sheet1.Cells(BOTTOM, 42)).Value = Range(Sheet1.Cells(Top, 27), Sheet1.Cells(BOTTOM, 30)).Value
'Range(Sheet1.Cells(Top, 27), Sheet1.Cells(BOTTOM, 30)).ClearContents
'<<<<<<<<Establish VS group ranges within project on Layout Sheet.>>>>>>>>
VSGROUP = 0
For C = Top To BOTTOM
If Left(Cells(C, 2), 3) = PR(PROJECT) Then
VSGROUP = VSGROUP + 1
ReDim Preserve VS(VSGROUP)
VS(VSGROUP) = Right(Cells(C, 2), Len(Cells(C, 2)) - 4)
ReDim Preserve VSTop(VSGROUP)
VSTop(VSGROUP) = C + 1
End If
If Cells(C, 3) = "Total" Then
ReDim Preserve VSBottom(VSGROUP)
VSBottom(VSGROUP) = C - 1
Exit For
'MsgBox (VSBottom(VSGROUP) - VSTop(VSGROUP))
End If
Next C
'MsgBox "OK"
INSERT: If VSBottom(VSGROUP) - (VSTop(VSGROUP) - 1) < PivotVSQTY(VSGROUP) Then
Rows(VSBottom(VSGROUP) + 1).EntireRow.INSERT
VSBottom(VSGROUP) = VSBottom(VSGROUP) + 1
Rows(VSBottom(VSGROUP) - 1).EntireRow.Copy
Rows(VSBottom(VSGROUP)).EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
GoTo INSERT:
End If
SUBTRACT: If VSBottom(VSGROUP) - (VSTop(VSGROUP) - 1) > PivotVSQTY(VSGROUP) Then
Range(Cells(VSBottom(VSGROUP), 2), Cells(VSBottom(VSGROUP), 36)).Delete
VSBottom(VSGROUP) = VSBottom(VSGROUP) - 1
GoTo SUBTRACT:
End If
MsgBox "OK"
Range(Sheet1.Cells(VSTop(VSGROUP), 2), Sheet1.Cells(VSBottom(VSGROUP), 8)).Value = Range(PivotSheet.Cells(PivotVSTop(VSGROUP), 1), PivotSheet.Cells(PivotVSBottom(VSGROUP), 7)).Value
Range(Sheet1.Cells(VSTop(VSGROUP), 11), Sheet1.Cells(VSBottom(VSGROUP), 13)).Value = Range(PivotSheet.Cells(PivotVSTop(VSGROUP), 8), PivotSheet.Cells(PivotVSBottom(VSGROUP), 10)).Value
Range(Sheet1.Cells(VSTop(VSGROUP), 16), Sheet1.Cells(VSBottom(VSGROUP), 16)).Value = Range(PivotSheet.Cells(PivotVSTop(VSGROUP), 11), PivotSheet.Cells(PivotVSBottom(VSGROUP), 11)).Value
Range(Sheet1.Cells(VSTop(VSGROUP), 19), Sheet1.Cells(VSBottom(VSGROUP), 19)).Value = Range(PivotSheet.Cells(PivotVSTop(VSGROUP), 12), PivotSheet.Cells(PivotVSBottom(VSGROUP), 12)).Value
Range(Sheet1.Cells(VSTop(VSGROUP), 21), Sheet1.Cells(VSBottom(VSGROUP), 21)).Value = Range(PivotSheet.Cells(PivotVSTop(VSGROUP), 13), PivotSheet.Cells(PivotVSBottom(VSGROUP), 13)).Value
For TWK = VSTop(VSGROUP) To VSBottom(VSGROUP)
For LWK = BOTTOM To Top
If Sheet1.Cells(TWK, 2).Value = Sheet1.Cells(LWK, 39).Value Then
Range(Sheet1.Cells(TWK, 27), Sheet1.Cells(TWK, 30)).Value = Range(Sheet1.Cells(LWK, 39), Sheet1.Cells(LWK, 42)).Value
Range(Sheet1.Cells(LWK, 39), Sheet1.Cells(LWK, 42)).ClearContents
Exit For
End If
Next LWK
Next TWK


MsgBox "OK"
Next PROJECT
End Sub
------------------------------------------------
Sub NumberOfValueStreams()
'THIS SUB MUST BE RUN ON A "PIVOT" WORKSHEET!
ReDim PivotVS(X)
ReDim PivotVSQTY(X)
If Cells(3, 1) = "(blank)" Then Exit Sub
For VS = 3 To 100
If Cells(VS, 1) = "Grand Total" Then
ReDim Preserve PivotVSQTY(X)
LAST = VS
ReDim Preserve PivotVSBottom(X)
PivotVSBottom(X) = VS - 1
PivotVSQTY(X) = LAST - FIRST
Exit For
End If
If Cells(VS, 1) = "" Then GoTo VS:
A = 0
A: A = A + 1
If Mid(Cells(VS, 1), A, 3) <> "VS-" Then GoTo A:
B = 0
B: B = B + 1
If Len(Cells(VS, 1)) = Len(Mid(Cells(VS, 1), A, A + B + 1)) Then GoTo CarryOn:
If Mid(Cells(VS, 1), A + 2 + B, 1) <> " " Then GoTo B:
CarryOn:
NEWVS = Mid(Cells(VS, 1), A, A + B + 1)
If OLDVS = "" Then
FIRST = VS
X = 1
OLDVS = NEWVS
ReDim Preserve PivotVS(X)
PivotVS(X) = NEWVS
ReDim Preserve PivotVSTop(X)
PivotVSTop(X) = VS
End If
If NEWVS <> OLDVS Then
LAST = VS
ReDim Preserve PivotVSBottom(X)
PivotVSBottom(X) = VS - 1
ReDim Preserve PivotVSQTY(X)
PivotVSQTY(X) = LAST - FIRST
FIRST = VS
X = X + 1
ReDim Preserve PivotVS(X)
PivotVS(X) = NEWVS
ReDim Preserve PivotVSTop(X)
PivotVSTop(X) = VS
OLDVS = NEWVS
End If
VS: Next VS
'MsgBox "OK"
'Z = X
'For X = 1 To Z
'If PivotVS(X) <> "" Then MsgBox (PivotVS(X) & " " & PivotVSQTY(X) & " ROWS. TOP IS ROW " & PivotVSTop(X) & ". BOTTOM IS ROW " & PivotVSBottom(X) & ".")
'Next X
End Sub

 

[F-150 Phill]

There you have it, that's my code (so far), unedited and uncut.

 

[F-150 Phill]

Just for the fun of it I hit run (F5) and let it go until it hit the stop below it (MsgBox "OK") and LWK got up to 32768 before it stepped out of the loop.

... yup, I'm lost!

 

[Jonmo1]

OK, I think I see the problem...

For ProjArea = 1 To 10000
If Cells(ProjArea, 3).Value = PR(PROJECT) Then
Top = ProjArea + 2
'MsgBox "OK"
End If
If Cells(ProjArea, 3).Value = "Grand Total" Then
'MsgBox "OK"
BOTTOM = ProjArea
Exit For
End If
ProjArea: Next ProjArea

You're trying to get the value for Top and Bottom in the same loop.
And I"m guessing in column 3 are Project codes, and they are sorted.
And you will have several of each project.
So there may be like 10 for a project. All in a row.

For every row that the project matches, this is recalculated (Top = ProjArea + 2)
So TOP will = the LAST(not the first) row that matches the project, Plus 2.
And If that is GREATER than the row holding Grand Total, then the loop will never stop (untill an overflow error or something).

 

[F-150 Phill]

I see what you're saying, I'll clarify a little bit what it's looking for. The sheet this macro is looking through is a layout sheet, there are eight "projects" (For PROJECT = 1 To 8) on the sheet each designated with a 3 character name (i.e. 12A, 34B, 56C etc) in a header row. The value of PR(PROJECT) is one of those project names and it searches from row 1 through 10000 until it finds a match (i.e. PR(PROJECT)="12A", Cells(ProjArea, 3).Value="12A"). It will only come across a matching value to PR(PROJECT) once in the loop, that's how it gets the "Top" value. Below that header are multiple tables each designated by group names (VS(VSGROUP)) this is where "For C = Top to BOTTOM" comes into play as it will search for the top and bottom rows of that group (VSTop(VSGROUP) AND VSBottom(VSGROUP)) within that project. The goal is to go to each project and from group to group update the information from the PivotTable sheet, then go to the next group, then next project. The part of the macro where the troubled loop resides moves old comparison data (LWK) far to the right so rows can be added and deleted as necessary to accomodate the new (TWK) data and then gets brought back in a VLookup fashion to line up with the current data.

The problem is still that when I get to the LWK loop it will start at the "to" value. In the code "For LWK = Top to BOTTOM," if I mouseover Top it shows: "Top=4" and "bottom=90," but on the first iteration of the loop if I mouseover LWK it shows: "LWK=90"

Also I noticed that in the full string of code I pasted that BOTTOM and Top are reversed, please disregard this because just before pasting it all on here I reversed them to test what it would do. The results of that are "For LWK = BOTTOM to Top" (Bottom=90, Top=4) the first iteration of the loop will result in "LWK=4."