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

Kscoof

New Member
Joined
Dec 23, 2022
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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
 
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.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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
Back
Top