Scatz
05-17-2007, 12:47 PM
Hi,
I am working on a project at the moment where we read a lot of data(3751rows, 4 columns) at a time from some equipment. What I want to do is search through the data and find the max/min values. The problem is the data is cycular so I don't simply want 1 value. When I do find a relative max/min value I want to highlight the cell and copy/paste it to a different sheet.
I have tried the following, and I get a runtime 9 error. VBA obviously doesn't like my use of k so I commented it out and then it works (don't know why it didn't:dunno). However, the result gives me around 30 max values (I haven't adjusted the code yet for th min values) and there should only be 7!!!
I have attached a sample of data with a graph. Essentailly all I want are the 7 or so max and min values. I don't know if there is a much better way of trying to get them? Perhaps from the graph directly? I have tried to smooth the data but I still get the same problem. I know excel/VBA is giving me exactly what I ask it to, I just don't know how to ask it to give me what I want!
This is the very last bit of a long stream of VBA to try and sort this data and it's my first time using VBA so it's taken ages to get this far and I just can't seemt o get around the problem :banghead:
Any help would be great. Thanks in advance
Scatz
Sub GetPeaks()
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 1
k = 3
For i = 4 To 3751
If Worksheets("Summary").Cells(i, 13).Value > _
Worksheets("Summary").Cells(i - 1, 13).Value And _
Worksheets("Summary").Cells(i, 4).Value < _
Worksheets("Summary").Cells(i + 1, 4).Value Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Worksheets("Summary").Cells(i, 13).Copy
Worksheets("Data Table").Cells(k, 1).Paste
k = k + 1
i = i + 1
Else
i = i + 1
End If
Next i
End Sub
Edited 18-May-07 by geekgirlau. Reason: insert line breaks
I am working on a project at the moment where we read a lot of data(3751rows, 4 columns) at a time from some equipment. What I want to do is search through the data and find the max/min values. The problem is the data is cycular so I don't simply want 1 value. When I do find a relative max/min value I want to highlight the cell and copy/paste it to a different sheet.
I have tried the following, and I get a runtime 9 error. VBA obviously doesn't like my use of k so I commented it out and then it works (don't know why it didn't:dunno). However, the result gives me around 30 max values (I haven't adjusted the code yet for th min values) and there should only be 7!!!
I have attached a sample of data with a graph. Essentailly all I want are the 7 or so max and min values. I don't know if there is a much better way of trying to get them? Perhaps from the graph directly? I have tried to smooth the data but I still get the same problem. I know excel/VBA is giving me exactly what I ask it to, I just don't know how to ask it to give me what I want!
This is the very last bit of a long stream of VBA to try and sort this data and it's my first time using VBA so it's taken ages to get this far and I just can't seemt o get around the problem :banghead:
Any help would be great. Thanks in advance
Scatz
Sub GetPeaks()
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 1
k = 3
For i = 4 To 3751
If Worksheets("Summary").Cells(i, 13).Value > _
Worksheets("Summary").Cells(i - 1, 13).Value And _
Worksheets("Summary").Cells(i, 4).Value < _
Worksheets("Summary").Cells(i + 1, 4).Value Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Worksheets("Summary").Cells(i, 13).Copy
Worksheets("Data Table").Cells(k, 1).Paste
k = k + 1
i = i + 1
Else
i = i + 1
End If
Next i
End Sub
Edited 18-May-07 by geekgirlau. Reason: insert line breaks