Quicker way to use For, If and Case?

[dancingcab]

I have a column of data and I'm looking to find all the points where the sign changes and define these points as either "peak" or "baseline" based on which way the sign changes. My data can have several hundred rows. I have code that works (see below), but I was wondering if there is a quicker way? This step does seem to be quite slow.

This is my code:

Sub Sign()'Finds peaks and baseline points (i.e. changes in dy/dx sign)


limit = ActiveSheet.UsedRange.Rows.Count
    
For i = 3 To limit
        
        If Sgn(Range("H" & i).Value) <> Sgn(Range("H" & i + 1).Value) Then
        Point = Sgn(Range("H" & i).Value)
    Select Case Point
        Case 1
            Range("I" & i) = "Peak"
        Case -1
            Range("I" & i) = "Baseline"
    End Select
        End If
 Next i
 
 Range("I1").Value = "Peak/Baseline" 'Labels column
 
 End Sub

Here is an example of some data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]dy/dx[/TD]
[/TR]
[TR]
[TD="align: right"]0.000108[/TD]
[/TR]
[TR]
[TD="align: right"]2.84E-05[/TD]
[/TR]
[TR]
[TD="align: right"]9.26E-06[/TD]
[/TR]
[TR]
[TD="align: right"]4.53E-06[/TD]
[/TR]
[TR]
[TD="align: right"]1.79E-06[/TD]
[/TR]
[TR]
[TD="align: right"]1.16E-06[/TD]
[/TR]
[TR]
[TD="align: right"]4.21E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-8.4E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-4.2E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-2.1E-07[/TD]
[/TR]
[TR]
[TD="align: right"]3.16E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-1.4E-07[/TD]
[/TR]
[TR]
[TD="align: right"]2.21E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-5.8E-07[/TD]
[/TR]
[TR]
[TD="align: right"]2.11E-07[/TD]
[/TR]
[TR]
[TD="align: right"]9.47E-08[/TD]
[/TR]
[TR]
[TD="align: right"]-4.8E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-8.4E-08[/TD]
[/TR]
[TR]
[TD="align: right"]-2.1E-07[/TD]
[/TR]
[TR]
[TD="align: right"]5.26E-08[/TD]
[/TR]
[TR]
[TD="align: right"]-4.2E-07[/TD]
[/TR]
[TR]
[TD="align: right"]1.16E-07[/TD]
[/TR]
[TR]
[TD="align: right"]-2.3E-07[/TD]
[/TR]
[TR]
[TD="align: right"]4.53E-07[/TD]
[/TR]
[TR]
[TD="align: right"]1.05E-08[/TD]
[/TR]
[TR]
[TD="align: right"]-3.5E-07[/TD]
[/TR]
[TR]
[TD="align: right"]5.05E-07[/TD]
[/TR]
[TR]
[TD="align: right"]2.11E-07[/TD]
[/TR]
[TR]
[TD="align: right"]5.26E-07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Peak/Baseline[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
[TR]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Peak[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Is it any quicker using arrays?

Sub Sign() 'Finds peaks and baseline points (i.e. changes in dy/dx sign)
    Dim limit As Long
    Dim Data As Variant
    Dim Output() As Variant
    Dim Point As Long
    limit = ActiveSheet.UsedRange.Rows.Count
    Data = Range("H3:H" & limit + 1).Value
    ReDim Preserve Output(1 To UBound(Data, 1) - 1)
    For i = 1 To UBound(Data, 1) - 1
        If Sgn(Data(i, 1)) <> Sgn(Data(i + 1, 1)) Then
            Point = Sgn(Data(i, 1))
            Select Case Point
                Case 1
                    Output(i) = "Peak"
                Case -1
                    Output(i) = "Baseline"
            End Select
        End If
    Next i
    Range("I3").Resize(limit - 2) = Application.Transpose(Output)
    Range("I1").Value = "Peak/Baseline" 'Labels column
End Sub