vba Copy Paste Conditional to put remark 1 2 3 .. etc

[DocAElstein]

The data is being cleared, But it is being put back in!


To explain:-
These two code lines clear the range when the condition is not met
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents

This next code line puts all the data in. It does this after the Select Case / End Select
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Because it does this after the Select Case / End Select, the code line will be done both when the condition is met and when the condition is not met.

So , the range is being cleared if the condition is not met. But it is then being re filled.
The ranges are filled from the array, arrS3(Cnt)


There are two possibilities to overcome this problem.

_1 empty the array , ( instead of clearing the range )

Dim Cnt As Long, Clms As Long
    For Cnt = 2 To UBound(arrS1(), 1) '  "row" count, cnt
    '2b)(ii)
    Dim Lc As Long: Let Lc = Ws3.Cells.Item(Cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
     Let arrS3(Cnt) = Ws3.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
     Select Case arrS1(Cnt, 9) ' column I
      Case "SELL" 'If column I is  SELL
        If arrS1(Cnt, 11) > arrS2(Cnt, 5) Then ' if column K is Greater than column E of sheet2 Then
        ' Condition not met ... clear the data from cloumn B till the end of the data in that entire row
        ' Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
            For Clms = 2 To Lc
             Let arrS3(Cnt)(1, Clms) = ""
            Next Clms
        Else
        ' Condition is met
        Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
        End If
      
      Case "BUY"  'If column I is  BUY
       If arrS1(Cnt, 11) < arrS2(Cnt, 6) Then  ' if column K is lower than column F of sheet2 Then
       ' Condition is not met ....clear the data from cloumn B till the end of the data in that entire row
       ' Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
            For Clms = 2 To Lc
             Let arrS3(Cnt)(1, Clms) = ""
            Next Clms
       Else
       ' Condition is met
        Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
      
       End If
     End Select
'    '3b) output "row"
     Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
    Next Cnt
 

OR:-

_ 2 Use the code line which puts in all the data for the met condition within the Select Case / End Select

Rem 3
Dim Cnt As Long
    For Cnt = 2 To UBound(arrS1(), 1) '  "row" count, cnt
    '2b)(ii)
    Dim Lc As Long: Let Lc = Ws3.Cells.Item(Cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
     Let arrS3(Cnt) = Ws3.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
     Select Case arrS1(Cnt, 9) ' column I
      Case "SELL" 'If column I is  SELL
        If arrS1(Cnt, 11) > arrS2(Cnt, 5) Then ' if column K is Greater than column E of sheet2 Then
        ' Condition not met ... clear the data from cloumn B till the end of the data in that entire row
         Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
        Else
        ' Condition is met
        Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
        Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
        End If
      
      Case "BUY"  'If column I is  BUY
       If arrS1(Cnt, 11) < arrS2(Cnt, 6) Then  ' if column K is lower than column F of sheet2 Then
       ' Condition is not met ....clear the data from cloumn B till the end of the data in that entire row
        Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
       Else
       ' Condition is met
        Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
        Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
       End If
     End Select
''    '3b) output "row"
'     Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
    Next Cnt