I have the following code, which takes the User's input, via a dropdown list, and based on the selection made or "description" in column G, the corresponding number, either in column C or column E, is populated in a specific cell in a different worksheet of the same workbook. Also, the code determines that if the amount is in Column E, this number is converted to a negative number. Furthermore, if the User selects the same "description" in Column G for multiple line items, the code sums the corresponding cells, if both numbers are in Column C or it nets the amounts if these amounts are in different columns C or E and it places the total or net of these amounts in that one specific cell in a separate worksheet, as I stated previously.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "TB Import (A)" Then
If Not Application.Intersect(Target, ['TB Import (A)'!C:G]) Is Nothing Then
If Sh.Name = "AJEs (I)" Then
If Not Application.Intersect(Target, ['AJEs (I)'!D:H]) Is Nothing Then
If Sh.Name = "AJEs (I)" Then
If Not Application.Intersect(Target, ['AJEs (I)'!R:V]) Is Nothing Then
Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Set oDataColA = ['TB Import (A)'!C:C]
Set oDataColB = ['TB Import (A)'!E:E]
Set oCatCol = ['TB Import (A)'!G:G]
Set oDataColA = ['AJEs (I)'!F:F]
Set oDataColB = ['AJEs (I)'!H:H]
Set oCatCol = ['AJEs (I)'!D:D]
Set oDataColA = ['AJEs (I)'!T:T]
Set oDataColB = ['AJEs (I)'!U:U]
Set oCatCol = ['AJEs (I)'!R:R]
dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
Application.ScreenUpdating = True
Application.EnableEvents = True
The code above works perfectly, but I would like to know which [b]vba funcion[/b]
I could replace "dSum" with so that instead of reflecting the total or net amount, or a single number, a formula with cell addresses is populated in the corresponding worksheet.
More specifically, in the attached excel spreadsheet you can see that in worksheet "TB Import (A)" I have assigned the "cash" description to three line items. The amounts in these three line items net to $250 dollars, and as such, in worksheet "Balance Sheet (H)" in D6, there is an amount of $250, but what I would like to see instead in "Balance Sheet (H)" cell D6 is the following formula ='TB Import (A)'!C8+'TB Import (A)'!C9-'TB Import (A)'!E10
. The purpose of this change would be to have a better trail to follow, instead of just a number.
Please let me know if you require more detail or further explanation.
Any help on this will be greatly appreciated.