Need help with this Formula

Sandy3503

New Member
Joined
Jun 23, 2021
Messages
9
Platform
  1. Windows
VBA Code:
Sub addAccount()
    Dim aT As Worksheet
    Set aT = Sheets("Account Template")
    Dim tB As Worksheet
    Set tB = Sheets("Trial Balance")
    Dim cRow As Integer
    Dim lRow As Integer
    Dim dS As Worksheet
    Set dS = Sheets("Data Sheet")
    Dim SheetName As String

    
    aT.Copy before:=tB
    On Error Resume Next
    ActiveSheet.Name = frmaddAccount.ltbAcct.Value
    On Error GoTo 0
    
    ActiveSheet.Cells(2, 2).Value = frmaddAccount.ltbAcct.Value
    
    cRow = 4
    Do Until tB.Cells(cRow, 2).Value = Empty
        cRow = cRow + 1
    Loop
    
    lRow = 2
    Do Until dS.Cells(lRow, 5).Value = Empty
        lRow = lRow + 1
    Loop
    
    
    tB.Cells(cRow, 2).Value = frmaddAccount.ltbAcct.Value
    dS.Cells(lRow, 5).Value = frmaddAccount.ltbAcct.Value
    
    ActiveSheet.Name = SheetName.Value
    
    tB.Cells(cRow, 4).Formula = "If(-SUM('SheetName'!E4:E1000)+SUM('SheetName'!D4:D1000)<=0,,-SUM('SheetName'!E4:E1000)+SUM('sheetname'!D4:D1000))"
    
End Sub

I need the Formula in the last line of code to work. I can copy a sheet and add the sheet but now i need it so that when the sheet is copied and added to the trial balance that this formula appears in the cells to calculate the balance. Any idea of how that might work? I am new to coding and just really confused with this.
 

Attachments

  • Trial Balance.PNG
    Trial Balance.PNG
    9.8 KB · Views: 4

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,392
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try This for formula but change all Sheet1 at formula to SheetName:
VBA Code:
tB.Cells(cRow, 4).FormulaR1C1 ="=IF(SUM(Sheet1!R4C4:R1000C4)-SUM(Sheet1!R4C5:R1000C5)<=0,"""",SUM(Sheet1!R4C4:R1000C4)-SUM(Sheet1!R4C5:R1000C5))"

OR
VBA Code:
tB.Cells(cRow, 4).FormulaR1C1 ="=IF(SUM(" & Sheetname & "!R4C4:R1000C4)-SUM(" & Sheetname & "!R4C5:R1000C5)<=0,"""",SUM(" & Sheetname & "!R4C4:R1000C4)-SUM(" & Sheetname & "!R4C5:R1000C5))"
 

Forum statistics

Threads
1,141,605
Messages
5,707,355
Members
421,503
Latest member
Rickys03

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