Need help with this Formula

Sandy3503

New Member
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
On Error GoTo 0

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

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
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.

Well-known Member
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))"``

Replies
1
Views
131
Replies
3
Views
109
Replies
7
Views
150
Replies
3
Views
248
Replies
16
Views
150

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.

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

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