I’m new to VBA but i write a code to find first peak in a set of data from a plot but know when a i have a bit changed plot i can’t figure out how to find it here are 2 examples enter image description herefirst is where code is working perfectly and finding the marked value, enter image description here, the second is not finding the first marked value but first maximum value.
Here is the code
Option Explicit
Sub FindFirstPeakInSheetV1(wsSource As Worksheet, ByRef firstPeak As Double, ByRef foundPeak As Boolean)
Dim lastRow As Long
Dim i As Long
Dim b As Long
' Initialize the foundPeak flag to False
foundPeak = False
' Find the last row with data in column B
lastRow = wsSource.Cells(wsSource.Rows.Count, 2).End(xlUp).Row
' Clear the auxiliary column (column C)
wsSource.Range("C1:C" & lastRow).ClearContents
' Loop through the range in column B and fill auxiliary column with peaks
For i = 2 To lastRow - 1
If IsNumeric(wsSource.Cells(i, 2).Value) And _
IsNumeric(wsSource.Cells(i - 1, 2).Value) And _
IsNumeric(wsSource.Cells(i + 1, 2).Value) Then
If wsSource.Cells(i, 2).Value > wsSource.Cells(i - 1, 2).Value And _
wsSource.Cells(i, 2).Value > wsSource.Cells(i + 1, 2).Value And _
wsSource.Cells(i, 4).Value > 0 And _
wsSource.Cells(i, 2).Value > 2 Then
wsSource.Cells(i, 3).Value = wsSource.Cells(i, 2).Value ' Fill auxiliary column with peaks
End If
End If
Next i
' Find the first peak in the auxiliary column
For i = 2 To lastRow
If IsNumeric(wsSource.Cells(i, 3).Value) And wsSource.Cells(i, 3).Value <> "" Then
firstPeak = wsSource.Cells(i, 3).Value
foundPeak = True
Exit For
End If
Next i
End Sub
Sub FindFirstPeakTest1()
Dim wsSource As Worksheet
Dim wsResults As Worksheet
Dim firstPeak As Double
Dim foundPeak As Boolean
Dim resultRow As Long
' Set the results sheet (change "Results" to your desired results sheet name)
Set wsResults = ThisWorkbook.Sheets("Results")
' Initialize the result row for output in the Results sheet, starting from D3
resultRow = 3
' Loop through each worksheet in the workbook except the Results sheet and the specified sheets
For Each wsSource In ThisWorkbook.Worksheets
If wsSource.Name <> "Results" And wsSource.Name <> "Parameters" And wsSource.Name <> "Statistics" Then
' Call the subroutine to find the first peak in the current sheet
Call FindFirstPeakInSheetV1(wsSource, firstPeak, foundPeak)
' Output the first peak or a message if no peak found
If foundPeak Then
wsResults.Cells(resultRow, 4).Value = firstPeak ' Output the first peak in column D
Else
wsResults.Cells(resultRow, 4).Value = "No peak found"
End If
' Move to the next row for the next sheet's result
resultRow = resultRow + 1
End If
Next wsSource
End Sub
Can i somehow modify the code or write a new one to do what i expect?
Axel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.