Copy formulas down using VBA Macro

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. 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.

AEA AED 2 Template For July.xls
CDEFGH
1 Credit amount Debit amount Value date Amount bank Recon Amt Entity
2 22,790.99 1-Jul-2022790.99-22790.99Bank
3 6,052.90 1-Jul-20Bank
4 (1,782.48)1-Jul-20Bank
5 (89.12)1-Jul-20Bank
6 10,634.95 2-Jul-20Bank
7 25,000.00 2-Jul-20Bank
8 22,575.00 26-Jul-20Bank
9 36,960.00 26-Jul-20Bank
10 36,725.00 26-Jul-20Bank
11 2,205.00 26-Jul-20Bank
12 (14,827.00)26-Jul-20Bank
13 (47.25)26-Jul-20Bank
14 3,990.00 26-Jul-20Bank
15 (139,274.62)26-Jul-20Bank
16 (15.75)26-Jul-20Bank
17 5,261.55 27-Jul-20Bank
18 (7,000.00)27-Jul-20Bank
19 (6,200.00)27-Jul-20Bank
20 (47.25)27-Jul-20Bank
21 (23,525.70)27-Jul-20Bank
22 (162.75)27-Jul-20Bank
23 (7,281.03)27-Jul-20Bank
24 (162.75)27-Jul-20Bank
25 11,400.03 27-Jul-20Bank
26 11,202.47 27-Jul-20Bank
27 53,340.00 27-Jul-20Bank
28 55,352.40 27-Jul-20Bank
29 (1,158,450.60)27-Jul-20Bank
30 (236.25)27-Jul-20Bank
31 (392,996.98)27-Jul-20Bank
32 (89.25)27-Jul-20Bank
33 (48,496.29)28-Jul-20Bank
34 (47.25)28-Jul-20Bank
35 4,562.30 28-Jul-20Bank
36 24,723.41 28-Jul-20Bank
37 10,325.51 29-Jul-20Bank
38 (162.75)29-Jul-20Bank
39 (5,170,200.00)29-Jul-20Bank
40 (47.25)29-Jul-20Bank
41 (3,500.30)29-Jul-20Bank
42 (70,450.68)29-Jul-20Bank
43 1,600,000.00 29-Jul-20Bank
446,052.907-Jul-20-6052.9-6052.9EUR
4510,634.957-Jul-20EUR
4625,000.007-Jul-20EUR
472,256.647-Jul-20EUR
4822,575.557-Jul-20EUR
4947,462.637-Jul-20EUR
506,301.497-Jul-20EUR
5114,674.287-Jul-20EUR
5246,513.467-Jul-20EUR
53-975.0023-Jul-20EUR
54-70,000.009-Jul-20EUR
55-297.009-Jul-20EUR
56-2,750.0014-Jul-20EUR
57-3,370.0022-Jul-20EUR
58-4,720.0022-Jul-20EUR
59-5,291.3822-Jul-20EUR
60-48,496.2926-Jul-20EUR
61-7,000.0026-Jul-20EUR
62-392,996.9826-Jul-20EUR
63-1,158,450.6026-Jul-20EUR
64-7,281.0327-Jul-20EUR
65-23,525.7027-Jul-20EUR
66-3,500.3029-Jul-20EUR
67-1,769.2518-Jul-20EUR
68-1,782.481-Jul-20EUR
69-89.121-Jul-20EUR
70-1,500.0021-Jul-20EUR
71-134,917.5722-Jul-20EUR
72-1,522.5023-Jul-20EUR
73-808.5027-Jul-20EUR
74-210.0029-Jul-20EUR
751,600,000.0029-Jul-20EUR
7636,120.0019-Jul-20USD
77-36,206.0023-Jul-20USD
78-7,828.4223-Jul-20USD
79-5,425.5023-Jul-20USD
80-1,767.5923-Jul-20USD
81-414.7523-Jul-20USD
82-20,853.7726-Jul-20USD
83-224,405.6526-Jul-20USD
84-387,013.0226-Jul-20USD
85149,090.0029-Jul-20SEK
86-23,000.007-Jul-20SEK
87-22,546.7923-Jul-20SEK
88-6,664.0023-Jul-20SEK
89-1,239.4015-Jul-20SEK
90-1,000.0015-Jul-20SEK
91-331.5015-Jul-20SEK
92-240.0315-Jul-20SEK
93-3,400.0015-Jul-20SEK
94-7,092.0022-Jul-20SEK
95-14,827.0026-Jul-20SEK
96-139,274.6226-Jul-20SEK
Recon
Cell Formulas
RangeFormula
F2F2=IF(ISBLANK(C2),D2,C2)
G2G2=IFERROR(IF(C2>0,-C2,D2),D2)
F44F44=IF(ISBLANK(C44),-D44,-D44)
G44G44=IF(D44>0,-D44,D44)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GCell ValueduplicatestextYES




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

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Cool. Try this and let me know if it works. Thanks.
VBA Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,129,796
Messages
5,638,385
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top