Hello everyone,
I hope I could get some help here with my vba code below:
Sub CountCyclesWithTimeframe()
Dim wsData As Worksheet
Dim wsOutput As Worksheet
Dim tbl As ListObject
Dim cycleCounter As Integer
Dim rowNumber As Long
Dim currentCycle As String
Dim currentTimeframe As String
Dim outputRow As Long
Dim symbolsRow As Long
' Set your worksheet
Set wsData = ThisWorkbook.Sheets("FDAX 12-23")
' Retrieve the timeframe from cell E2 on the "Trend Analysis" worksheet
Dim wsTrendAnalysis As Worksheet
Set wsTrendAnalysis = ThisWorkbook.Sheets("Trend Analysis")
currentTimeframe = wsTrendAnalysis.Range("E2").Value
' Check if the worksheet "OutputData" exists
On Error Resume Next
Set wsOutput = ThisWorkbook.Sheets("OutputData")
On Error GoTo 0
If wsOutput Is Nothing Then
' Create a new output worksheet
Set wsOutput = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.count))
wsOutput.Name = "OutputData"
Else
' Clear existing content in the worksheet
wsOutput.Cells.Clear
End If
' Set your table
Set tbl = wsData.ListObjects("FDAX") ' Replace "FDAX" with your table name
' Initialize cycle counter and output row
cycleCounter = 1
outputRow = 2 ' Starting row for output data
symbolsRow = 2 ' Starting row for symbol data
' Loop through the table data
For rowNumber = 1 To tbl.ListRows.count
' Get the current trend value and timeframe
currentCycle = tbl.ListColumns("MomentumDescription").DataBodyRange(rowNumber).Value
currentTimeframe = tbl.ListColumns("TimeFrame").DataBodyRange(rowNumber).Value
' Check if it's the start of a new cycle based on selected timeframe
If currentTimeframe = wsTrendAnalysis.Range("E2").Value Then
' Check if the next cycle is "SQ Cancelled" or it's the last row
If currentCycle = "New Wave Positive" Or rowNumber = tbl.ListRows.count Then
' Output only the first eight cycles
If cycleCounter > 5 Then Exit For
' Output headers for cycle and symbol
wsOutput.Cells(1, (cycleCounter * 2) - 1).Value = "LV" & cycleCounter
wsOutput.Cells(1, cycleCounter * 2).Value = "Arrow" & cycleCounter
' Reset output row for new cycle
outputRow = 2
symbolsRow = 2
cycleCounter = cycleCounter + 1
End If
End If
' Output the trend in the respective cycle column based on selected timeframe
If currentTimeframe = wsTrendAnalysis.Range("E2").Value Then ' Adjust for other timeframes as needed
If cycleCounter <= 5 Then
wsOutput.Cells(outputRow, (cycleCounter * 2) - 1).Value = currentCycle ' Adjust column for cycle
' Populate Symbols column with corresponding data from CurrentMomentum column
wsOutput.Cells(symbolsRow, cycleCounter * 2).Value = tbl.ListColumns("CurrentMomentum").DataBodyRange(rowNumber).Value ' Adjust column for symbols
outputRow = outputRow + 1 ' Move to the next row for the next element in the cycle
symbolsRow = symbolsRow + 1 ' Move to the next row for Symbols data
End If
End If
Next rowNumber
End Sub
For any reason unknown to me above code fails to return the desired filter from the table.
The code is supposed to loop through the "MomentumDescription" column and fetch only the "New Wave Positive" elements that are between any two "SQ Cancelled" element in that column within a specified timeframe in cell E5 from the "Timeframe" column in a worksheet named "Trend Analysis". The "SQ Cancelled" is the boundary that marks a cycle (LV). The code is supposed to exit after the first five cycles and write out the output in a new worksheet named "OutputData". But the output of the code only return just one of the "New Wave Positive" element in each cycle together with other elements that are not needed from the dataset.
Can someone please help me out with the logic to only fetch the total occurrences of "New Wave Positive" Element within the specified timeframe that is between every "SQ Cancelled" element. I also need the last "SQ Cancelled" element to be fetched together with the "New Wave Positive" elements per cycle.
Bookmarks