Extract Value from R1C1 formula

[Allarius]

I am trying to sum up entire rows by column. IE: I have Rows 4:6 that I am working with. I want it to sum A4:A6 into A7, B4:B6 into B7, C4:C6 into C7, etc, up to a predetermined Column.

I came up with a couple different solutions. The first was just manually looping through the rows and adding them up. This worked pretty well, but it felt expensive. It's entirely possible I'm trying to overengineer this step and I could just stick with the looping, but it feels messy and inefficient.

So after doing some research I developed a solution using R1C1 notation. This was great because I could assign all of the math in one easy step.

Range(Cells(last.Row + 1, 4), Cells(last.Row + 1, 11)).FormulaR1C1  = "=SUM(R[-" & last.Row - payRowArray(i) + 1 & "]C:R[-1]C)"

This solution does exactly what I need it to, however I do not like the fact that any changes to the file could affect the results since it is using relative references and storing the formula instead of the value from the formula.
What I would like to do is use this formula to create the result and store the value directly into the cell to avoid losing the correct information if someone else comes along and adjusts the spreadsheet.

I have seen something simliar online using Application.Evaluate and Application.ConvertFormula, but I'm not sure if that solution does what I want or what the correct syntax would be. This seems like it might work, but I'm not sure if I can apply this to an entire range like I could with the above formula.
Any help would be greatly appreciated! Also any insight into whether this is an unnecessary attempt at optimization would be great as well.

 

[James006]

Hi,
If you are curious about Evaluate ... you can test following

Range("A7") = Evaluate("=SUM(A4:A6)")

 

[James006]

Should you need to apply Evaluate to your selected number of columns ...

Sub SumEvaluate()
' To Sum a Given Number of Columns
Dim j As Integer    ' Number of Columns
    For j = 1 To 8
      Cells(7, j) = Evaluate("=SUM(" & Cells(4, j).Address & ":" & Cells(6, j).Address & ")")
    Next j
End Sub

 

[Allarius]

Should you need to apply Evaluate to your selected number of columns ...

Sub SumEvaluate()
' To Sum a Given Number of Columns
Dim j As Integer    ' Number of Columns
    For j = 1 To 8
      Cells(7, j) = Evaluate("=SUM(" & Cells(4, j).Address & ":" & Cells(6, j).Address & ")")
    Next j
End Sub

This is very similar to the first incarnation of my code, except I just added the values together instead of using a formula.

For i=  LBound(payorArray) To UBound(payorArray) - 1
    For j = 4 To 11
        .Cells(payRowArray(i), j).Value2 = .Cells(payRowArray(i), j).Value2 + .Cells(payRowArray(i+1) - 1, j).Value2
    Next j
Next i

I was always under the impression that double looping like this is computationally expensive, which is why I was looking for a more direct approach. Is this an incorrect view? Will I see a negligible difference in runtime? Using R1C1 notation I can apply the function to an entire range at once, but it doesn't seem like Evaluate allows R1C1 notation, which is what led me to .ConvertFormula.

Am I just overthinking this?

 

[James006]

You are welcome

For sure, you have noticed:
1) .FormulaR1C1 adds a formula in your cell
2) Evaluate adds the result of your formulas to your cells

 

[Allarius]

You are welcome

For sure, you have noticed:
1) .FormulaR1C1 adds a formula in your cell
2) Evaluate adds the result of your formulas to your cells

Yes that is correct, but what I would like to know is if there a way to use the R1C1 notation that I posted in the OP and extract the value to place into the cell instead of the formula. Evaluate doesn't seem to work with R1C1 notation, nor does it seem to be able to affect an entire range without needing a loop to run through the columns 1 by 1(Unless I am not understanding the complete functionality of Evaluate).

Or are you saying that it is not worth doing in terms of computational complexity?

 

[James006]

is there a way to use the R1C1 notation that I posted in the OP and extract the value to place into the cell instead of the formula ?

Unfortunately, No... the choice is between the formula or the formula's result ...