Applying a Variable to a SUM Formula

Roguekiwi

New Member
Joined
Sep 25, 2002
Messages
3
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 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
 
Upvote 0
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.
 
Upvote 0
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)"
 
Upvote 0
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!!!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,306
Messages
6,171,324
Members
452,396
Latest member
ajl_ahmed

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