Debugging Do While - Loops

richardson

New Member
Joined
Jul 12, 2006
Messages
11
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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.
 

Forum statistics

Threads
1,141,402
Messages
5,706,239
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top