What method is best to sum a column?

[MPW]

I am dynamically populating a blank sheet with contents from another sheet. I want to sum certain columns. Here is what I am using.

cr = Sheets("X").Cells(1, Columns.Count).End(xlToLeft).Column        
For c3 = 1 To cr     
     If Left(Sheets("X").Cells(1, c3), 1) = "#" Then
          rall = Sheets("X").Cells(Rows.Count, c3).End(xlUp).Row
          Sheets("X").Cells(1, c3 + 1).Formula = & _
                    "=Sum(R[" & 2 & "]C[" & c3 + 1 & "]:R[" & rall & "]C[" & c3 + 1 & "])"
          Sheets("X").Cells(1, c3 + 2).Formula = & _
                    "=Sum(R[" & 2 & "]C[" & c3 + 2 & "]:R[" & rall & "]C[" & c3 + 2 & "])"
            End If
        Next c3

When run it apparently creates the formulas correctly, however they are summing the wrong columns. For example:

"=Sum(R[" & 2 & "]C[" & c3 + 1 & "]:R[" & rall & "]C[" & c3 + 1 & "])"

creates

=SUM(R[2]C[4]:R[40]C[4])

If I am reading the r1c1 ref correctly this should sum ("D2:D40"). Instead it sums ("H2:H40").

If I convert to traditional formulas it looks like ("H2:H40"). This is the 1st time I tried to construct r1c1 formulas like this. So what am I missing?

Please show me a better way.

 

[DanteAmor]

When you use [] it means that you are going to put a relative reference, then the positions depend on where you put the formula.
Maybe you need to use absolute references, without []:

Sheets("X").Cells(1, c3 + 1).Formula = _
          "=Sum(R" & 2 & "C" & c3 + 1 & ":R" & rall & "C" & c3 + 1 & ")"

You can also use the traditional connotation:

Sheets("X").Cells(1, c3 + 1).Formula = "=Sum(D2:D" & rall & ")"

But I am not sure what you want to sum, if you explain a little what you have on your sheet and what is the final objective.

 

[MPW]

Interesting! That did the trick. As I said, I never tried to compile an r1c1 ref before, I always used the traditional notation. I wanted to try the r1c1 since the columns are not static. I am sure there is a way to do this with trad. notation, but thought this might work better. I was curious what would happen if the sheet was set to trad notation when I ran it. It set the formulas to use absolute values.

=SUM($D$2:$D$40)

FYI, I am summing the number of lines and keystrokes assigned to a certain reader. We use Excel as a script to record the Bible in different languages.

Thanks for the help, I can move on in life now

 

[DanteAmor]

Im glad to help you, thanks for the feedback.