using offset with FormulaR1C1

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

S Himmelrich

This code works, but I'm having an issue with providing a value copy
of the FormulaR1C1 the another column in the same row. I've basically
indicated the line of code that is not working.

For Each myArea In myRng.Areas
myFormula = "=sum(r[-1]c:r[-" & myArea.Cells.Count & "]c)"
Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
0)
With FormCell
.FormulaR1C1 = myFormula
.Offset(0, 1).FormulaR1C1 = myFormula <- This is the
problem line, I want to put the value myFormula
' .Offset(0, 3).FormulaR1C1 = myFormula
End With
Next myArea
 
Do you mean you want the value for the .offset(0,1) column or do you want the
value from the column to the left?

with .offset(0,1)
.formular1c1 = myformula
.value = .value
end with

or
.offset(0,1).value = .value



S said:
This code works, but I'm having an issue with providing a value copy
of the FormulaR1C1 the another column in the same row. I've basically
indicated the line of code that is not working.

For Each myArea In myRng.Areas
myFormula = "=sum(r[-1]c:r[-" & myArea.Cells.Count & "]c)"
Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
0)
With FormCell
.FormulaR1C1 = myFormula
.Offset(0, 1).FormulaR1C1 = myFormula <- This is the
problem line, I want to put the value myFormula
' .Offset(0, 3).FormulaR1C1 = myFormula
End With
Next myArea
Click to expand...
 
Super -> .offset(0,1).value = .value

on the same line of code is there a way to put BOLD formatting?


Do you mean you want the value for the .offset(0,1) column or do you want the
value from the column to the left?

      with .offset(0,1)
          .formular1c1 = myformula
          .value = .value
      end with

or
     .offset(0,1).value = .value



S said:
This code works, but I'm having an issue with providing a value copy
of the FormulaR1C1 the another column in the same row.  I've basically
indicated the line of code that is not working.
Click to expand...
For Each myArea In myRng.Areas
            myFormula = "=sum(r[-1]c:r[-" & myArea.Cells..Count & "]c)"
            Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
0)
            With FormCell
                .FormulaR1C1 = myFormula
                .Offset(0, 1).FormulaR1C1 = myFormula   <- This is the
problem line, I want to put the value myFormula
        '        .Offset(0, 3).FormulaR1C1 = myFormula
            End With
        Next myArea
Click to expand...
Click to expand...
 
..offset(0,1).value = .value
..offset(0,1).font.bold = true

Lots of times, it's quicker to record a macro when you do it manually.

S said:
Super -> .offset(0,1).value = .value

on the same line of code is there a way to put BOLD formatting?

Do you mean you want the value for the .offset(0,1) column or do you want the
value from the column to the left?

with .offset(0,1)
.formular1c1 = myformula
.value = .value
end with

or
.offset(0,1).value = .value



S said:
This code works, but I'm having an issue with providing a value copy
of the FormulaR1C1 the another column in the same row. I've basically
indicated the line of code that is not working.
Click to expand...
For Each myArea In myRng.Areas
myFormula = "=sum(r[-1]c:r[-" & myArea.Cells.Count & "]c)"
Set FormCell = myArea.Cells(myArea.Cells.Count).Offset(1,
0)
With FormCell
.FormulaR1C1 = myFormula
.Offset(0, 1).FormulaR1C1 = myFormula <- This is the
problem line, I want to put the value myFormula
' .Offset(0, 3).FormulaR1C1 = myFormula
End With
Next myArea
Click to expand...
Click to expand...
Click to expand...
 
Back
Top