Replace Formulas with Values

[mazo]

Hi

I have a formula in a certain cell and then use the AutoFill-fuction

Range("C6").FormulaR1C1 = "=R[-2]C+R1C6"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C900"),
Type:=xlFillDefault

Later I want to use the Values of Range("C6:C900")

How can I get the values?
Or:
Can I use the AutoFillfunction also for Range("C6").Value = ?? ?


Mazo

 

[Gary''s Student]

try:

MSGBOX(RANGE("C6").Value) to display the value of a single cell in that
range. Vary the address according to your needs.

 

[Ikaabod]

This should return every value for all the rows, if that's what you
need.

Sub TestMacro()
Range("C6").FormulaR1C1 = "=R[-2]C+R1C6"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C900"), Type:=xlFillDefault

Dim iMyCount As Integer
iMyCount = 0
Do
Debug.Print ActiveCell.Offset(iMyCount, 0).Value
iMyCount = iMyCount + 1
Loop Until iMyCount = 895
End Sub
-Hi

I have a formula in a certain cell and then use the AutoFill-fuction

Range("C6").FormulaR1C1 = "=R[-2]C+R1C6"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C900"),
Type:=xlFillDefault

Later I want to use the Values of Range("C6:C900")

How can I get the values?
Or:
Can I use the AutoFillfunction also for Range("C6").Value = ?? ?


Mazo
-

 

[GS]

Hi Mazo,

You could try this:

With Range("C6:C900")
.FormulaR1C1 = "=R[-2]C+R1C6"
.Value = .Value
End With

Regards,
Garry