# Applying a Variable to a SUM Formula

#### Roguekiwi

##### New Member
Gidday
I have defined a variable as an integer and now wish to make a sum of that integer but am just having no joy. Any help, the code is below.

Dim Finalrow As Integer
Selection.End(xlDown).Select
Finalrow = ActiveCell.Row
Range("C2").Select

ActiveCell.Offset(Finalrow + 1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[- Finalrow ]C:R[-1]C)"

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Gidday boy,
Finalrow = ActiveCell.Row
*this ascertains a row number, let's say 10
Range("C2").Select
*this selects a cell in row 2
ActiveCell.Offset(Finalrow + 1, 0).Select
*this selects a cell which is 10+1 rows below row 2
?is that what you want?
[/quote]

Hope that helps,
=dn

Can you work with this?

[d2].Formula = "=sum(c2:" & Range("c65536").End(xlUp).Address(0, 0) & ")"

Sorry, I was not to clear - it is the last line of the script that is just not working for me - I want to incorporate the Variable Finalrow into the Sum formula.

Thanks

Excel won't recognise the Finalrow variable in your formula. So you need assign your formula to a string variable and pass the variable to the FormulaR1C1 property. Like this:

Code:
``````Sub Test()
Dim Finalrow As Integer
Dim Formula As String
Finalrow = Range("C65536").End(xlUp).Row
Range("C" & Finalrow + 1).Select
Formula = "=SUM(R[-" & Finalrow & "]C:R[-1]C)"
ActiveCell.FormulaR1C1 = Formula
End Sub``````

This also selects the correct cell in column C. Your procedure would have put the formula 3 rows below the last row because you started in row 2.

Beware with .End(xlDown), you could hit an Overflow error, Andrew's way is safer if it can be used. If not, change your last line to:

ActiveCell.Formula = "=SUM(R[-" & Finalrow & "]C:R[-1]C)"

While researching my own formula issues, I came across this thread that I think applies to my situation. However, I can't for the life of me get it to work properly. Formula R1C1 is a foreign concept to me which is probably why I can't seem to wrap my head around it.

I'm trying to insert a formula using VBA and variables. The original formula is...
=IF(DG4<>0,DF4/(DF4+DG4),IF(AND(DG4=0,DF4<>0),1,0))

I need to use variables for the columns because the column where the formula is inserted, initially DH, can change depending on the data. LC will reference the last column used (previously DH) but I'm not sure how to use that in this formula or how to reference the other columns offset to the left, 1 and 2 columns over. Any help would be greatly appreciated!!!

I was able to make it work with the following:

"=IF(" & COLB & "4<>0," & COLA & "4/(" & COLA & "4+" & COLB & "4),IF(AND(" & COLB & "4=0," & COLA & "4<>0),1,0))"

However, it was more time consuming because I had to define the variables based on other factors. Is there a way to write this so that I don't have to define the variables?

Replies
3
Views
267
Replies
5
Views
243
Replies
10
Views
331
Replies
3
Views
173
Replies
3
Views
172

1,202,983
Messages
6,052,907
Members
444,611
Latest member
ggwpnore

### 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.

### Which adblocker are you using?

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

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