Copy formulas down using VBA Macro

[bassemjohn1]

Hello guys,

am trying to find a way where I can let my Macro read that: First, I filter for the bank category in column H, then i add 2 formulas in column F and G then copy it down across all bank rows
then i filter for USD-SEK-EUR and add 2 different Formulas in column f and G and copy it down also then clear the filter.

AEA AED 2 Template For July.xls
	C	D	E	F	G	H
1	Credit amount	Debit amount	Value date	Amount bank	Recon Amt	Entity
2	22,790.99		1-Jul-20	22790.99	-22790.99	Bank
3	6,052.90		1-Jul-20			Bank
4		(1,782.48)	1-Jul-20			Bank
5		(89.12)	1-Jul-20			Bank
6	10,634.95		2-Jul-20			Bank
7	25,000.00		2-Jul-20			Bank
8	22,575.00		26-Jul-20			Bank
9	36,960.00		26-Jul-20			Bank
10	36,725.00		26-Jul-20			Bank
11	2,205.00		26-Jul-20			Bank
12		(14,827.00)	26-Jul-20			Bank
13		(47.25)	26-Jul-20			Bank
14	3,990.00		26-Jul-20			Bank
15		(139,274.62)	26-Jul-20			Bank
16		(15.75)	26-Jul-20			Bank
17	5,261.55		27-Jul-20			Bank
18		(7,000.00)	27-Jul-20			Bank
19		(6,200.00)	27-Jul-20			Bank
20		(47.25)	27-Jul-20			Bank
21		(23,525.70)	27-Jul-20			Bank
22		(162.75)	27-Jul-20			Bank
23		(7,281.03)	27-Jul-20			Bank
24		(162.75)	27-Jul-20			Bank
25	11,400.03		27-Jul-20			Bank
26	11,202.47		27-Jul-20			Bank
27	53,340.00		27-Jul-20			Bank
28	55,352.40		27-Jul-20			Bank
29		(1,158,450.60)	27-Jul-20			Bank
30		(236.25)	27-Jul-20			Bank
31		(392,996.98)	27-Jul-20			Bank
32		(89.25)	27-Jul-20			Bank
33		(48,496.29)	28-Jul-20			Bank
34		(47.25)	28-Jul-20			Bank
35	4,562.30		28-Jul-20			Bank
36	24,723.41		28-Jul-20			Bank
37	10,325.51		29-Jul-20			Bank
38		(162.75)	29-Jul-20			Bank
39		(5,170,200.00)	29-Jul-20			Bank
40		(47.25)	29-Jul-20			Bank
41		(3,500.30)	29-Jul-20			Bank
42		(70,450.68)	29-Jul-20			Bank
43	1,600,000.00		29-Jul-20			Bank
44		6,052.90	7-Jul-20	-6052.9	-6052.9	EUR
45		10,634.95	7-Jul-20			EUR
46		25,000.00	7-Jul-20			EUR
47		2,256.64	7-Jul-20			EUR
48		22,575.55	7-Jul-20			EUR
49		47,462.63	7-Jul-20			EUR
50		6,301.49	7-Jul-20			EUR
51		14,674.28	7-Jul-20			EUR
52		46,513.46	7-Jul-20			EUR
53		-975.00	23-Jul-20			EUR
54		-70,000.00	9-Jul-20			EUR
55		-297.00	9-Jul-20			EUR
56		-2,750.00	14-Jul-20			EUR
57		-3,370.00	22-Jul-20			EUR
58		-4,720.00	22-Jul-20			EUR
59		-5,291.38	22-Jul-20			EUR
60		-48,496.29	26-Jul-20			EUR
61		-7,000.00	26-Jul-20			EUR
62		-392,996.98	26-Jul-20			EUR
63		-1,158,450.60	26-Jul-20			EUR
64		-7,281.03	27-Jul-20			EUR
65		-23,525.70	27-Jul-20			EUR
66		-3,500.30	29-Jul-20			EUR
67		-1,769.25	18-Jul-20			EUR
68		-1,782.48	1-Jul-20			EUR
69		-89.12	1-Jul-20			EUR
70		-1,500.00	21-Jul-20			EUR
71		-134,917.57	22-Jul-20			EUR
72		-1,522.50	23-Jul-20			EUR
73		-808.50	27-Jul-20			EUR
74		-210.00	29-Jul-20			EUR
75		1,600,000.00	29-Jul-20			EUR
76		36,120.00	19-Jul-20			USD
77		-36,206.00	23-Jul-20			USD
78		-7,828.42	23-Jul-20			USD
79		-5,425.50	23-Jul-20			USD
80		-1,767.59	23-Jul-20			USD
81		-414.75	23-Jul-20			USD
82		-20,853.77	26-Jul-20			USD
83		-224,405.65	26-Jul-20			USD
84		-387,013.02	26-Jul-20			USD
85		149,090.00	29-Jul-20			SEK
86		-23,000.00	7-Jul-20			SEK
87		-22,546.79	23-Jul-20			SEK
88		-6,664.00	23-Jul-20			SEK
89		-1,239.40	15-Jul-20			SEK
90		-1,000.00	15-Jul-20			SEK
91		-331.50	15-Jul-20			SEK
92		-240.03	15-Jul-20			SEK
93		-3,400.00	15-Jul-20			SEK
94		-7,092.00	22-Jul-20			SEK
95		-14,827.00	26-Jul-20			SEK
96		-139,274.62	26-Jul-20			SEK
Recon

Cell Formulas
Range	Formula
F2	F2	=IF(ISBLANK(C2),D2,C2)
G2	G2	=IFERROR(IF(C2>0,-C2,D2),D2)
F44	F44	=IF(ISBLANK(C44),-D44,-D44)
G44	G44	=IF(D44>0,-D44,D44)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Cell Value	duplicates	text	YES

the Macro can't seem to understand what i want and always end up not copying the formula down.

so what is the best way to do it while recording macro so macro can easily understand that I filter first then copy formulas down.

 

[bassemjohn1]

Cool. Try this and let me know if it works. Thanks.

Sub ProcessData()

    Dim rng As Range
    Dim ws As Worksheet
    Set ws = Application.ActiveSheet
   
    endrow = ws.Range("A1").CurrentRegion.Rows.Count
    ws.Range("$A$1:$H$" & endrow).AutoFilter Field:=8, Criteria1:="=Bank"
    Set rng = ws.Range("F2:F" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-2],RC[-3])"
    Set rng = ws.Range("G2:G" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IFERROR(IF(RC[-4]>0,-RC[-4],RC[-3]),RC[-3])"
    ws.ShowAllData

    ws.Range("$A$1:$H$" & endrow).AutoFilter Field:=8, Criteria1:=Array("EUR", "SEK", "USD"), Operator:=xlFilterValues
    Set rng = ws.Range("F2:F" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),-RC[-2],-RC[-2])"
    Set rng = ws.Range("G2:G" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(RC[-3]>0,-RC[-3],RC[-3])"
    

    ws.ShowAllData
    AddConditionalFormatting ws
End Sub
Sub AddConditionalFormatting(ws As Worksheet)
    Dim HighlightDuplicates As Object
   
    ws.Range("G:G").FormatConditions.AddUniqueValues
    Set HighlightDuplicates = ws.Range("G:G").FormatConditions(1)
   
    With HighlightDuplicates
        .SetFirstPriority
        .DupeUnique = xlDuplicate
        .Font.Color = -16383844
        .Interior.Color = 13551615
        .StopIfTrue = False
    End With
End Sub

WOW ! it works flawlessly, am really thankful for your time ! and the time you gonna save me each month from now on :D

 

[Dan_W]

WOW ! it works flawlessly, am really thankful for your time ! and the time you gonna save me each month from now on :D

You're very welcome.