VBA to add a Value From on Cell to the End of a Formula

[Kscoof]

I have a formula in column AV that is summing some values in a table and another formula in AW that is summing some different values in the same table. I cannot sum Columns AV and AW together in the same cell as it creates a circular reference because I am using a sumif as part of the equation and they are in the same array. I want to create a macro that will add the value in column AW to the corresponding cell in AV. The columns go from row 14 to row 74. For example, if the calculation changes in cell AW21 then I want it to add the value of AW21 to AV21 at the end of the formula. I need to keep the formula in column AV and can only have one result at a time to the end of the formula in column AV. So when there is a change the old result needs removed. Here is the macro I have so far. It will add the value to the end of the formula but won’t remove the old. Also, it won’t add a value when the calculation in AW changes. I must hand enter a number for it to work. I have tried a bunch of different things but below is what I have currently. Any suggestions are appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("AW14:AW74")) Is Nothing Then

If IsNumeric(Target.Value) Then

With Target.Worksheet.Range(Target.Address)

.Offset(0, -1).Formula = .Offset(0, -1).Formula & "+" & Target.Value

End With

End If

End If

End Sub

 

[Kscoof]

To summarize, I want the money in the columns labeled "Excess Money to Main Brokerage" and "Sale of Real Estate" to be added back to the column "Main Brokerage" in the same row. So that value of $2,066,734 needs in AM53 needs added to AC53. The column labeled "Excess Money to Main Brokerage" to already being added back into the "Main Brokerage". I need a way to add the "Sale of Real Estate" column without causing a circular reference.

 

[StephenCrump]

Thanks for the simpler example. The problem isn't the SUMIFS. Here's what's causing your circular reference:

AM14: =IF(SUM(AB14:AK14)=0,0,(SUMIFS(AB13:AK13,$AB$11:$AK$11, ....

Do you need this test? Presumably a zero total means in practice that all account balances are zero (rather than some combination of +ve and -ve that happens to add to zero?) In which case your formula will return zero anyway, and the test is redundant?

 

[Kscoof]

I tried that and it still gives a circular reference. I added the IF(SUM(AB14:AK14)=0,0, so I won't sum a negative value below the real estate sold value. Basically when all the accounts run dry and the real estate is sold it sums the value in the real estate column. If there is negative values it also sums. I was thinking it's giving a circular reference because it's summing all the columns in the table (looking for Real Estate) and then trying to add the value back into the same table (Main Brokerage column) which gives a circular reference. Am I looking at this correctly?