PDA

View Full Version : My Macro stinks



KronkeyDong
06-03-2009, 02:35 PM
I really didn't want to bug anybody about help with this, but I can't figure it out and I've been working on it for a week now and I'm feeling pretty dumb. I'm using Excel 2003 SP3. I'm collecting data and only want to use the data as the values are increasing. Once the values start decreasing they are not of interest. The goal is to cut out the useful data (make them into zeros). To add difficulty though there are zeros within the data and these are to be ignored and the previous value (a column to the left) that isn't a zero is to be compared to see if the data is increasing or decreasing. The data is column Q to column AI in multiple rows (each row isn't compared though). Here is a 10 cell wide example of what the goal is

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

I'm pretty sure my current attempt doesn't account for the zeroes that well, but I'm not sure because it keeps crashing like a son of a gun.



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


Yep, I'm going bonkers. :wot

mdmackillop
06-03-2009, 03:09 PM
Option Explicit
Sub Test()
Dim Endd As Range, rng As Range, cel As Range, c As Range
Dim i As Long, x As Long
For i = 1 To 2
x = 0
Set Endd = Cells(i, 1).End(xlToRight)
Set rng = Range(Cells(i, 2), Endd)
For Each cel In rng
x = x + 1
If cel < Application.Max(Cells(i, 1).Resize(, x)) And cel <> 0 Then
For Each c In Range(cel, Endd)
c.Value = 0
Next
End If
Next
Next
End Sub

mdmackillop
06-03-2009, 03:14 PM
For data starting in Column Q


Option Explicit
Sub Test()
Dim Endd As Range, rng As Range, cel As Range, c As Range
Dim i As Long, x As Long
For i = 1 To 2 '<=== Set required number or rows
x = 0
Set Endd = Cells(i, "Q").End(xlToRight)
Set rng = Range(Cells(i, "R"), Endd)
For Each cel In rng
x = x + 1
If cel < Application.Max(Cells(i, 1).Resize(, x)) And cel <> 0 Then
For Each c In Range(cel, Endd)
c.Value = 0
Next
End If
Next
Next
End Sub