Need Help with VBA Code for Sum Formula for Variable Cells

[Chili SHot]

Column A

Column B

	1
	2
	3
=700-sum(A2:A4)

The code below is what I want, but I would like to modify the code to use 700 - sum (the column B) and show the result in Column A. Please advise.


Sub MySum()

Dim cr As Long
Dim lr As Long

' Get current row of activecell
cr = ActiveCell.Row

' Get first row of range
fr = ActiveCell.Offset(-1, 0).End(xlUp).Row

' Enter sum formula into activecell
ActiveCell.FormulaR1C1 = "=SUM(R[-" & cr - fr & "]C:R[-1]C)"

End Sub

 

[Joe4]

Welcome to the Board!

What row will your data in column B start on? Row 1? Row 2?
Do you just want it to sum down to the very last row with data in column B, and then put the sum in column, on the next row?
Will there always be just one set of data in column B we will be summing?

 

[Joe4]

If my assumptions are correct, and the first row with data is row 2, then you could use this code:

Sub MyInsertSum()

    Dim lr As Long
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate column A with formula
    Range("A" & lr + 1).Formula = "=700-SUM(B2:B" & lr & ")"
    
End Sub

If the data starts on row 1, just change "B2" to "B1" in the formula.

 

[Chili SHot]

If my assumptions are correct, and the first row with data is row 2, then you could use this code:

Sub MyInsertSum()

    Dim lr As Long
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Populate column A with formula
    Range("A" & lr + 1).Formula = "=700-SUM(B2:B" & lr & ")"
   
End Sub

If the data starts on row 1, just change "B2" to "B1" in the formula.

Thank you @Joe4!

 

[Joe4]

You are welcome.

 

[Chili SHot]

xxxx (Row1)						Column A	Column B
row 2					1	$ -	$ -
row3					1	$ -	$ -
					1	$ -	$ -
					1	$ -	$ -
					1	$ -	$ -
					1	$ -	$ -
					1	$ -	$ -
					2	$ -	$ -
					2	$ -	$ -
					1	$ -	$ -
					1	$ -	$ -
				Supplement		=700-sum( row2:Row13)
xxx Row14

 

[Chili SHot]

This is what I am trying to do. I tried to modify the columns in your code it is not working. Please see below.

Sub MyInsertSum()

Dim lr As Long

' Find last row in column H with data
lr = Cells(Rows.Count, "H").End(xlUp).Row

' Populate column G with formula
Range("G" & lr + 1).Formula = "=700-SUM(H2:H" & lr & ")"

End Sub

 

[Joe4]

View attachment 66092

This is what I am trying to do. I tried to modify the columns in your code it is not working. Please see below.

Sub MyInsertSum()

Dim lr As Long

' Find last row in column H with data
lr = Cells(Rows.Count, "H").End(xlUp).Row

' Populate column G with formula
Range("G" & lr + 1).Formula = "=700-SUM(H2:H" & lr & ")"

End Sub

Please explain in English exactly what you are trying to do in this instance, as it does not look the same as your original question.

 

[Chili SHot]

@Joe4 I got it. Thanks a lot! I fix it.

Sub MyInsertSum()

Dim lr As Long

' Find last row in column H with data
lr = Cells(Rows.Count, "H").End(xlUp).Row

' Populate column G with formula
Range("G" & lr).Formula = "=700-SUM(H2:H" & lr - 1 & ")"

End Sub