vba help - Create sum formula dynamically as per criteria

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
840
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Need your help in creating Sum formula Dynamically. as per Criteria List.
in K Column.

Below is my attempted code, which is working. are there any chances to improve in code.
or any other method, but it should be in Sum formula only. as my criteria list lies in other workbook.


VBA Code:
Sub Create_SumFormula()
    
    Dim rgSearch As Range
    Dim cell As Range
    Dim fname As String
    
    Dim L As String
    Dim n As String
    Dim s As String
    Dim ms As String
    
    Set rgSearch = Range("K3", Range("k" & Rows.Count).End(xlUp)) 'Search each cell in D Column.
        
        For Each cell In rgSearch
            Set cell = Range("D:D").Find(cell.Value)
                If Not cell Is Nothing Then
                    L = Split(cell.Offset(, 1).Address, "$")(1)
                    n = Split(cell.Offset(, 1).Address, "$")(2)
                        
                    If s = "" Then
                        s = L & n
                        ms = s
                    Else
                        s = L & n
                        ms = ms & "," & s
                    End If
        
                End If
        Next cell

    If ms <> "" Then
        sumformula = "=SUM(" & ms & ")"
        Range("e18:G18").Formula = sumformula
        
        'Recorded code
        Range("E18:G18").FormulaR1C1 = "=SUM(R[-12]C,R[-10]C,R[-8]C)"
        
    End If


End Sub

Below is a Table.

Book2.xlsm
BCDEFGHIJK
1Table
2Cost Head100020003000Expendiature List
3Basic220223271735091Telephone Reimbursement
4DA388862022731564Fuel & Maintenance
5HRA230442148336847Driver's Salary
6Telephone Reimbursement100200300
7Bonus242033203320467
8Fuel & Maintenance300500800
9Car Allowance264322053129890
10Driver's Salary4003001200
11
12
13
14
15
16Reimbursement Payble80010002300
17
18Reimbursement Payble80010002000
Sheet1
Cell Formulas
RangeFormula
E18:F18,E16:F16E16=SUM(E$6,E$10,E$8)
G16,G18G16=SUM(G6,G8,G10)



Thanks
mg
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

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