PDA

View Full Version : Closed: My Macro keeps crashing!



KronkeyDong
06-03-2009, 02:50 PM
I'm using Excel 2003 SP3. The goal is to only account for data as values are increasing. If the number in a cell is 0 it is to be ignored. the data is in columns Q to AI. A 10 cell example is shown below

0, 100, 200, 0, 400, 0, 0, 200, 0, 300 (initial)
0, 100, 200, 0, 400, 0, 0, 0, 0, 0 (final)


Sub Jc_Ic_autocutoffzz()
Dim zp As Integer
Dim z2 As Integer
Dim x As Integer
Dim jcrit1 As Integer
Dim jcrit2 As Integer
Dim jcrit3 As Integer
For zp = 3 To 200
If Cells(zp, 17) = 0 Then
Cells(zp, 17) = 0.000001
Else
Cells(zp, 17).Value = Cells(zp, 17)
End If
For jcrit2 = 18 To 36
If Cells(zp, jcrit2) = 0 Then
jcrit2 = jcrit2 - 1
End If
Next jcrit2
x = 18
Do While x < 37
jcrit3 = x - 1
If Cells(zp, x) < Cells(zp, jcrit3) Then
Cells(zp, x + 20).Value = 0
Else
Cells(zp, x + 20).Value = Cells(zp, x)
End If
x = x + 1
Loop
For z2 = 17 To 36
If Cells(zp, z2) = 0.000001 Then
Cells(zp, z2 + 20).Value = 0
End If
Next z2
Next zp
End Sub


Right now it keeps crashing so I can't even know if it is working. The next step would be to turn any repeated cells to zero and but leave the left most one. Dang, i stank at this.

p45cal
06-03-2009, 03:55 PM
I see a potential problem with



For jcrit2 = 18 To 36
If Cells(zp, jcrit2) = 0 Then
jcrit2 = jcrit2 - 1
End If
Next jcrit2

if/when cells(zp,jcrit2).value = 0, since it decrements the loop control variable jcrit2 when the Next statement increments it again - an endless loop.

mdmackillop
06-04-2009, 04:45 AM
See this thread (http://vbaexpress.com/forum/showthread.php?t=27017)