# VBA Formula Help

#### fredert

##### New Member
I am making an accounting project. I have a formula that copies a template worksheet and renames it to the name of the account I just added The code is below. It also puts the name of the sheet in the next available line in my trial balance. I created a formula within the debit side of the cell and and the credit side of the cell to find the balance on the account sheet and put it in the trial balance. Here it is:

=IF(SUM(Cash!E4:E49)>SUM(Cash!D4:D49),SUM(Cash!E4:E49)-SUM(Cash!D4:D49), " ")

Is there a way I can use this formula to be put automatically in the next empty debit and credit cell so I don't have to manually put it in? Basically can I put it in with this code. Also it should take the name of the new account so it shows the appropriate balance.

nextRow = 4
Do Until Sheets("Trial Balance").Cells(nextRow, 2).Value = Empty
nextRow = nextRow + 1

Loop
Sheets("Account Template").Visible = False
Worksheets("Account Template").Copy After:=Worksheets("Journal")
Sheets(2).Visible = True

End Sub

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Yeah it is. I tried what you said. I have this code but its not working. I need the sheet name to be whatever I choose next from my userform. So if I choose land then I want it to find the sum of the two columns in the land sheet. What do I put in the bolded part to make it work.

Dim i As Integer, j As Integer, Balance As Single
With Sheets("Sheets(2)")
For j = 4 To 49
DebitBalance = DebitBalance + .Cells(j, 4) - .Cells(j, 5)
CreditBalance = CreditBalance + .Cells(j, 5) - .Cells(j, 4)
Next j
If DebitBalance > CreditBalance Then
'A/R Debit Cell=Balance*-1
Sheets("Trial Balance").Cells(nextRow, 4) = DebitBalance + Cells(j, 4) - .Cells(j, 5)
Else
'A/R Credit Cell=Balance
Sheets("Trial Balance").Cells(nextRow, 4) = CreditBalance + Cells(j, 5) - .Cells(j, 4)
End If
End With

nextRow = 4
Do Until Sheets("Trial Balance").Cells(nextRow, 2).Value = Empty
nextRow = nextRow + 1

Loop
Sheets("Account Template").Visible = False
Worksheets("Account Template").Copy After:=Worksheets("Journal")
Sheets(2).Visible = True

k. My teacher wants me to copy the formula and paste it into the cell when I add a new sheet. I just found that out. Here is what I am trying to do. The bold underlined part it the problem. That's the code I am trying to paste into the empty last empty cell. Whats wrong with it?

Dim sheetname As String
nextRow = 4
Do Until Sheets("Trial Balance").Cells(nextRow, 2).Value = Empty
nextRow = nextRow + 1

Loop
Sheets("Account Template").Visible = False
Worksheets("Account Template").Copy After:=Worksheets("Journal")
Sheets(2).Visible = True
Sheets("Trial Balance").Cells(nextRow, 4).Value = "=IF(SUM(& sheetname & !E4:E49)>SUM( & sheetname & !D4:D49),SUM( & sheetname !E4:E49)-SUM( & sheetname & !D4:D49), 0)"

Replies
0
Views
98
Replies
3
Views
273
Replies
10
Views
490
Replies
11
Views
325
Replies
4
Views
102

1,203,064
Messages
6,053,319
Members
444,653
Latest member
Curdood

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

### Which adblocker are you using?

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