bassemjohn1
Board Regular
- Joined
- Jun 26, 2020
- Messages
- 51
- Office Version
- 2016
- Platform
- Windows
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.
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.
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.