VBA Formula Question?

fredert

New Member
Joined
Jun 14, 2012
Messages
12
In my trial balance I put the the following code in to reflect the current balance or either a debit or a credit balance for the cash account that is on a separate sheet. How do I write a sub to use that same forumula but change to where it grabs the information for the accounts receivable sheet for example? It will take a long time if I keep adding accounts to the list and I have to manually enter in the code every time.



=IF(SUM(Cash!D4:D49)>SUM(Cash!E4:E49),SUM(Cash!D4:D49)-SUM(Cash!E4:E49), " " ) (Debit is greater than credit put it on the credit side of trial balance, the credit side is empty)

=IF(SUM(Cash!E4:E49)>SUM(Cash!D4:D49),SUM(Cash!E4:E49)-SUM(Cash!D4:D49), " ") (Credit is greater than debit so put it on the credit side of trial balance, the debit side is blank.)

Trial_Balance.jpg
[/URL] image host[/IMG]
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

xladept

Board Regular
Joined
Apr 29, 2012
Messages
199
Hi fredert,

This could be a start:

Code:
Sub TB(): Dim i As Integer, j As Integer, Balance As Single
With Sheets("Cash")
For j = 4 To 49
Balance = .Cells(j, 4) - .Cells(j, 5)
If Balance < 0 Then

End If
Next j
End With
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I know you said about a VBA solution but couldn't you just use the INDIRECT function to reference the sheet names and then drag the formula down? For instance for your 1st formula assuming that the sheet name "Cash" is cell A5 the formula would be something like

Code:
=IF(SUM(INDIRECT(A5&"!$D$4:$D$49"))>SUM(INDIRECT(A5&"!$E$4:$E$49")),SUM(INDIRECT(A5&"!$D$4:$D$49"))-SUM(INDIRECT(A5&"!$E$4:$E$49")), " " )
 

xladept

Board Regular
Joined
Apr 29, 2012
Messages
199
Hi Fredert,

Here's more of what I mean:

Code:
Sub TB(): Dim i As Integer, j As Integer, Balance As Single
With Sheets("Cash")
For j = 4 To 49
Balance = Balance + .Cells(j, 4) - .Cells(j, 5)
Next j
If Balance < 0 Then
'A/R Debit Cell=Balance*-1
Else
'A/R Credit Cell=Balance
End If
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

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