VBA Formula Question?

[fredert]

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

[/URL] image host[/IMG]

 

[xladept]

Hi fredert,

This could be a start:

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]

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

=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]

Hi Fredert,

Here's more of what I mean:

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