syntax for SUM formula in VBA

[MargAtCanon]

Hi,
I'm writing some vba code where I want a formula to be pasted into a range of cells. I'm OK with other formulas because I copy a coleagues stuff. However, I am having trouble with this SUM because I don't really understand the syntax.
Can someone please help me?
Thank you.

ActiveCell.FormulaR1C1 = "=SUM(RC" & Area1SalesRepCol: OtherSalesRepCol ")"

 

[Weaver]

I think if the variables are column numbers rather than letters, this ought to work

ActiveCell.FormulaR1C1 = "=SUM(RC" & Area1SalesRepCol & ":RC" & OtherSalesRepCol ")"

 

[AlphaFrog]

What end-result formula (not VBA) do you want?

What are the values of Area1SalesRepCol an OtherSalesRepCol ? Are they column letters e.g. "A" and "C" or are they ranges e.g. Range("A:A") and Range("C:C") or integers e.g. 1 and 3.?

Depending on what end-result formula you want and the values of the two variables, that will determine the syntax of the VBA statement.

 

[MargAtCanon]

thank you both.
I tried the suggestion from Weaver and i get the Compile error: Expected end of statement.

To answer AlphaFrog's question, my formula should look like this once it's pasted into the worksheet.
=SUM(AH2:AO2)

 

[AlphaFrog]

To answer AlphaFrog's question, my formula should look like this once it's pasted into the worksheet.
=SUM(AH2:AO2)

What are the values of Area1SalesRepCol and OtherSalesRepCol ? Are they column letters e.g. "A" and "C" or are they ranges e.g. Range("A:A") and Range("C:C") or integers e.g. 1 and 3.?

 

[mikerickson]

ActiveCell.FormulaR1C1 = "=SUM(RC" & Area1SalesRepCol & ":RC" & OtherSalesRepCol [COLOR="Red"]&[/COLOR] ")"

 

[MargAtCanon]

Thank you mikerickson.

That has worked. I had experimented with the & and " but couldn't quite get the combination right.

I know this is very basic, but can you explain the ampersand's role? I have always thought it acts to join things. Can you recommend some reading so I can understand the very basics of VBA? In the course I have done and in the other texts I have read, there is an assumption that this is already known. Trying to pick this up in my 50s, I have a gap in my knowledge that is very frustrating.

 

[MargAtCanon]

AlphaFrog,

The values of Area1SalesRepCol and OtherSalesRepCol are column letters.
They are identified earlier in the code by

Area1SalesRepCol = colIndex(backorderWS, Range("Area1_SalesRep"))

the named range Area1_SalesRep is a name in a cell in another worksheet.
Does this explain?