Sum in a function with a variable

[stephicohu]

I am new in excel vba and have a question that you have answered previously. I cannot find the answer in your database because I can't remember how I word the question. I have the following code:

Dim Num1 As Integer

Num1 = -10

Range("AO3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[" & Cstr(Num1)"]:RC[-9], February!RC[-39]:RC[-35])"

What I am trying to do is to place the Num1 into the sum function so I can change the value of Num1. How do I do this without having an error on the value. I have tried many ideas and they have failed. Help!

 

[Micron]

I think it's because you're missing an ampersand ( & ) in the concatenation

"=SUM(RC[" & Cstr(Num1) & "]:RC[-9], February!RC[-39]:RC[-35])"

 

[stephicohu]

"=SUM(RC[" & Cstr(Num1) & "]:RC[-9], February!RC[-39]:RC[-35])"

Thanks it works now....

 

[Micron]

Please mark your thread as solved so that others don't spend time when they're looking for issues to solve.
Thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

A few additional observations.

• Excel converts all Integer variables to Long values before using them so you might as well declare them as Long to start with (besides, it is shorter to type anyway )
• There is generally no need to actually select cells/ranges before working with them and selecting slows your code. there is no need to select in this case - see my code below.
• There is no need to to coerce the numerical Num1 value to string to put it in the formula - vba will automatically handle that for you.

So, the code could look like this

Dim Num1 As Long

Num1 = -10

Range("AO3").FormulaR1C1 = "=SUM(RC[" & Num1 & "]:RC[-9], February!RC[-39]:RC[-35])"