Hi there,
I must say that this Forum was really helpful for me. I found a lot of things there but know I think I need your help. I'm working on Macros but I don't really have so much expirience as much of you. Let me Explain:
Macro:
This was the table
<TBODY>
</TBODY>
What do I need there? Make the Sum of Column Debit and Credit and put it down. (I managed that, see code below)
The result was:
<TBODY>
</TBODY>
I need something more but I can't manage it..
1 - In E5 (it's a dynamic table), under the 300 he should put the difference between D4 and E4.
2 - Find a value, let a say Code 2, and make the sum of all value (Debit&Credit) founded from Code 2 to the end except the previous totals and put it in E8.
Hope you can help me out.
I must say that this Forum was really helpful for me. I found a lot of things there but know I think I need your help. I'm working on Macros but I don't really have so much expirience as much of you. Let me Explain:
Macro:
This was the table
Code | Description | Currency | Debit | Credit |
1 | AAA | USD | 100 | |
2 | BBB | USD | 200 | |
3 | CCC | USD | 1500 |
<TBODY>
</TBODY>
What do I need there? Make the Sum of Column Debit and Credit and put it down. (I managed that, see code below)
The result was:
Code | Description | Currency | Debit | Credit |
1 | AAA | USD | 100 | |
2 | BBB | USD | 200 | |
3 | CCC | USD | 1500 | |
1500 | 300 |
<TBODY>
</TBODY>
I need something more but I can't manage it..
1 - In E5 (it's a dynamic table), under the 300 he should put the difference between D4 and E4.
2 - Find a value, let a say Code 2, and make the sum of all value (Debit&Credit) founded from Code 2 to the end except the previous totals and put it in E8.
Code:
Sub Macro()
Dim Msg As String
Msg = MsgBox("Do you want to continue? This Macro was created for TRIAL BALANCE", vbYesNo, "Trial Balance Macro")
Select Case Msg
Case vbYes ' User chose Yes.
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Dim Rng As Range, r As Range
Set Rng = Range("d8:d" & Range("d" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
For Each r In Rng.Areas
With r
.Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ")"
End With
Next
Dim Dng As Range, e As Range
Set Dng = Range("e8:e" & Range("e" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
For Each e In Dng.Areas
With e
.Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ")"
End With
Next
Range("D8:D6000").Select
Selection.Style = "Comma"
Range("E8:E6000").Select
Selection.Style = "Comma"
Case vbNo ' User chose No.
Exit Sub
End Select
End Sub