Results 1 to 3 of 3

VBA Code anomaly

Threaded View

  1. #1
    Registered User
    Join Date
    11-18-2023
    Location
    Nigeria
    MS-Off Ver
    Office 365
    Posts
    1

    VBA Code anomaly

    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.
    Last edited by alansidman; 11-18-2023 at 07:19 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Strange anomaly with code and project window.
    By kjg in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2021, 04:46 AM
  2. Possible Search Anomaly
    By LJMetzger in forum ExcelTip.com Feedback / Comments / Suggestions
    Replies: 3
    Last Post: 12-23-2019, 03:09 AM
  3. Another counting anomaly
    By Alan Beban in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2018, 07:22 PM
  4. Summation Anomaly
    By dups1822 in forum Excel General
    Replies: 3
    Last Post: 07-14-2017, 08:39 AM
  5. [SOLVED] Formula anomaly
    By Mulpuzzle in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 07-29-2016, 12:14 AM
  6. Clearing Form Controls - Code Anomaly
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2009, 11:01 PM
  7. Printing Anomaly
    By Otto Moehrbach in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1