vba autofilter - not working in accounting number format

[Mallesh23]

Hi Team,

I am trying to Autofilter Column E and Column F.
Criteria1 = 0 in both the column .......update Remarks as Cleared.

Both Column E and F are in Accounting format.

if I convert Column E and F into general format , My macro works.
But if it is in Accounting format macro not working.

Also there is Formula in Column E.Round up.

please help , Thanks in advance for help.


Below is my Attempted Code

Sub Autofilter_UpdateRemarks()

    With ActiveSheet.Range("$A$1:$G$13")
        
        'if I convert Values into general it works.
        '.AutoFilter Field:=5, Criteria1:="0"  ' this works if columns are in General
        '.AutoFilter Field:=6, Criteria1:="0"  this works if columns are in General
         
         ' Not working if format is in Accounting
        .AutoFilter Field:=5, Criteria1:=0
        .AutoFilter Field:=6, Criteria1:=0
        
        If .Range("$g$1:$G$13").SpecialCells(12).Count > 1 Then
            .Range("$g$2:$G$13").SpecialCells(12).Value = "Cleared"
            .Cells.AutoFilter
        Else
            .Cells.AutoFilter
        End If
    End With
        
End Sub

Below is my data Column E should filled down.

Book4
	A	B	C	D	E	F	G
1	Invoice Amount	DR	CR	XYZ	Outstanding Balance	Sap Balance	Remarks
2	1,000.00	-	-	1,000.00	-	-
3	1,000.00	-	-	1,000.00	-	-
4	1,000.00	-	-	1,000.00	-	-
5	1,000.00	-	-	1,000.00	-	-
6	1,000.00	-	-	1,000.00	-	-
7	1,000.00	-	-	1,000.00	-	-
8	1,000.00	-	-	1,000.00	-	-
9	1,000.00	-	-	1,000.00	-	-
10	1,000.00	-	-	1,000.00	-	-
11	1,000.00	-	-	1,000.00	-	-
12	5,317.59				5,317.59	5,317.59
13	4,638.94				4,638.94	4,638.94
14
15		=ROUND(A2-B2-C2-D2,2)
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=ROUND(A2-B2-C2-D2,2)

Thanks
mg

 

[Mallesh23]

Hi Team,

Through loop it works, but same not working via autofilter.

Sub test()

Dim arr As Variant
arr = Range("a1").CurrentRegion.Value2

Dim R As Long
For R = 2 To UBound(arr)
     If arr(R, 5) = 0 And arr(R, 6) = 0 Then
        Cells(R, 7) = "Cleared"
    End If
Next R

End Sub

Thanks
mg

 

[Fluff]

Change the criteria to "-"

 

[Mallesh23]

Hi Fluff,

Amazing, it worked. Thanks a lot for your help. ?



Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.