bradh_nz

08-23-2007, 04:54 AM

Hello

I have a formula that takes to long to calculate for an entire workbook (crashs workbook).

I though I could loop through selected cells copying the formula into the cell, calculating, copy and paste as values and move onto the next however I cannot seem to get it to work. Here is my attempt.

Sub Insert_Formulas()

Sheets("Detailed analysis").Select

Dim C As Range

'Loop format pivot table borders

Range("H8:L32").Select

For Each C In Selection.Cells

ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.Interior.ColorIndex = 15

Next C

End Sub

I have a formula that takes to long to calculate for an entire workbook (crashs workbook).

I though I could loop through selected cells copying the formula into the cell, calculating, copy and paste as values and move onto the next however I cannot seem to get it to work. Here is my attempt.

Sub Insert_Formulas()

Sheets("Detailed analysis").Select

Dim C As Range

'Loop format pivot table borders

Range("H8:L32").Select

For Each C In Selection.Cells

ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.Interior.ColorIndex = 15

Next C

End Sub