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
Below is my data Column E should filled down.
Thanks
mg
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
VBA 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