VBA Formula Help

[fredert]

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.

Sub addacctandworksheet()
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).Name = frmAddAcct.lbxAccts.Value
Sheets(2).Cells(2, 2).Value = frmAddAcct.lbxAccts
Sheets(2).Visible = True
Sheets("Trial Balance").Cells(nextRow, 2).Value = frmAddAcct.lbxAccts.Value


End Sub

 

[MARK858]

I am just going to work but is this not related to your previous thread?

http://www.mrexcel.com/forum/showthread.php?642531-VBA-Formula-Question

 

[fredert]

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).Name = frmAddAcct.lbxAccts.Value
Sheets(2).Cells(2, 2).Value = frmAddAcct.lbxAccts
Sheets(2).Visible = True
Sheets("Trial Balance").Cells(nextRow, 2).Value = frmAddAcct.lbxAccts.Value

 

[MARK858]

Yeah it is. I tried what you said.

And the result was?????

 

[fredert]

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
sheetname = frmAddAcct.lbxAccts.Value
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).Name = frmAddAcct.lbxAccts.Value
Sheets(2).Cells(2, 2).Value = frmAddAcct.lbxAccts
Sheets(2).Visible = True
Sheets("Trial Balance").Cells(nextRow, 2).Value = frmAddAcct.lbxAccts.Value
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)"