Debugging Do While - Loops

[richardson]

Hi - I'm working on a 'quick' macro to fill out an excel spreadsheet for me. In it I am trying to assign values based on certain cells then compare those with Col A, if there's a match then look in col B for a match, if there's a match look in Col C etc.

When it has found the right data it is supposed to return it to a certain cell. If it can't find it, it should return a zero.

I've done this with nested do-loops. There is a 'write command' in the top do loop but not in the first sub do loop. I have been trying to stop the macro after one time through the sub do loop but it appears have already looped a large # of times.

I'll paste my code in right away because these words just aren't cutting it. And Below my code I'll paste the excel data that I'm trying to manipulate. So much for writing a quick macro. These things are so much easier in my head. I'm starting to think i should have just manually copied the numbers.

Thanks for any help you feel like giving


Dim PNCol As Single
Dim inputrow As Single
Dim Year As Single
Dim Month As Single
Dim Day As Single
Dim rownum As Single
Dim i As Single
Dim j As Single
Dim k As Single
Dim PN As String
Dim itemp As Single
Dim jtemp As Single
Dim ktemp As Single

PNCol = 10

Do While PNCol < 250
inputrow = 2
PN = Worksheets("Sheet4").Cells(inputrow - 1, PNCol).Text
Do While inputrow < 1096

Year = Worksheets("Sheet4").Cells(inputrow, 7).Value
Month = Worksheets("Sheet4").Cells(inputrow, 8).Value
Day = Worksheets("Sheet4").Cells(inputrow, 9).Value
rownum = 2
Do While rownum < 4154
i = 0
j = 0
k = 0
If PN = Worksheets("Sheet4").Cells(rownum, 1).Value Then
Do While i < 4154
If Year = Worksheets("Sheet4").Cells(rownum + i, 2).Value Then
itemp = i
i = 4154
Exit Do
ElseIf Year < Worksheets("Sheet4").Cells(rownum + i, 2).Value Then
itemp = i
i = 4154
Exit Do
Else
i = i + 1
End If
Loop
If Year < Worksheets("Sheet4").Cells(rownum + itemp, 2).Value Then
Worksheets("Sheet4").Cells(inputrow, PNCol).Value = 0
Exit Do
End If
Do While j < 4154
If Month = Worksheets("Sheet4").Cells(rownum + itemp + j, 3).Value Then
jtemp = j
j = 4154
Exit Do
ElseIf Month < Worksheets("Sheet4").Cells(rownum + i + j, 3).Value Then
jtemp = j
j = 4154
Exit Do
Else
j = j + 1
End If
Loop
If Month < Worksheets("Sheet4").Cells(rownum + itemp + jtemp, 3).Value Then
Worksheets("Sheet4").Cells(inputrow, PNCol).Value = 0
Exit Do
End If
Do While k < 4154
If Day = Worksheets("Sheet4").Cells(rownum + itemp + jtemp + k, 4).Value Then
ktemp = k
k = 4154
Exit Do
ElseIf Day < Worksheets("Sheet4").Cells(rownum + itemp + jtemp + k, 4).Value Then
ktemp = k
k = 4154
Exit Do
Else
k = k + 1
End If
Loop
If Day < Worksheets("Sheet4").Cells(rownum + itemp + jtemp + ktemp, 4).Value Then
Worksheets("Sheet4").Cells(inputrow, PNCol).Value = 0
Exit Do
End If
Exit Do
Else
rownum = rownum + 1
End If
Worksheets("Sheet4").Cells(inputrow, PNCol).Value = Worksheets("Sheet4").Cells(rownum + i + j + k, 5).Value
Loop

inputrow = inputrow + 1

Loop

PNCol = PNCol + 1

Loop
End Sub




Excel data

PN Yr Mth Day Qty Year Month Day 358676-3 3102561-3
358676-3 2004 1 23 1 2004 1 1
3 11 3 2004 1 2
3 13 1 2004 1 3
4 3 3 2004 1 4
4 13 4 2004 1 5
4 26 4 2004 1 6
5 6 2 2004 1 7
5 11 6 2004 1 8
5 12 3 2004 1 9
5 13 1 2004 1 10
5 19 3 2004 1 11
5 22 4 2004 1 12
5 25 1 2004 1 13
5 26 1 2004 1 14
5 27 5 2004 1 15
6 2 4 2004 1 16
6 4 4 2004 1 17
6 8 8 2004 1 18
6 9 5 2004 1 19
6 14 2 2004 1 20
6 17 4 2004 1 21
6 21 4 2004 1 22
6 25 7 2004 1 23
6 29 1 2004 1 24
7 8 2 2004 1 25
7 24 4 2004 1 26
7 28 4 2004 1 27
8 19 2 2004 1 28
8 24 2 2004 1 29
8 28 4 2004 1 30
9 30 3 2004 1 31
10 1 2 2004 2 1
10 3 2 2004 2 2
10 12 3 2004 2 3
10 14 1 2004 2 4
10 17 4 2004 2 5
10 18 4 2004 2 6
11 13 7 2004 2 7
11 16 2 2004 2 8
11 18 2 2004 2 9
11 19 2 2004 2 10
11 22 2 2004 2 11
11 30 5 2004 2 12
12 21 1 2004 2 13
12 29 3 2004 2 14
2005 1 5 4 2004 2 15
1 11 4 2004 2 16
1 14 2 2004 2 17
1 17 4 2004 2 18
1 18 8 2004 2 19
1 20 3 2004 2 20
1 24 5 2004 2 21

 

[RalphA]

Without getting into your actual code, let me say that you can enter a space at any point in your code, and type in the command STOP. Now, if you run your code, it will execute up to that point. By inserting judiciously located STOPs, and with the code visible, you can fairly inteligently trouble shoot your code.

Let us know if the above allowed you to find an answer to your problem.