Using A Macro/VBA code to re-set formulas

[robertguy]

Hi can any one help me with a Macro/VBA code to re-set formulas in
pre-defined set of cells i.e. in my case C14:C40

At present I have a copy of the Formulas in another part of th
worksheet and when I want to reset the worksheet (as the user can ove
right the formulas as the formula is based on either the sum of anothe
two cells or the users input) I use a Macro to copy and paste (special
the formulas.

What I require is a Macro/VBA Code which already has the formula store
in the actual Macro/VBA code i.e. so they are not stored as copy in m
worksheet


Ay help would be greatly appreciated

Thanks


Ro

 

[Ron de Bruin]

Hi robertguy

You can use this to add a formula in the range

Range("C14:C40").Formula = "=sum(a14:b14)"

 

[JE McGimpsey]

I'd suggest a different approach.

Have a second sheet with just the formulas, that you can hide (you can
set the .Visible property to xlVeryHidden so that it doesn't appear on
the Format/Sheets/Unhide list). Then when you want to reset the sheet,
copy from the hidden sheet:

Public Sub Reset()
For Each cell In _
Sheets("HiddenSheet").Cells.SpecialCells(xlCelltypeFormulas)
With cell
Sheets("Sheet1").Range(.address).Formula = .Formula
End With
Next cell
End Sub

 

[Trevor Shuttleworth]

Rob

you don't say what your formula is so you'll need to adjust this to your
needs:

Sub CopyFormulae()
Application.ScreenUpdating = False
Range("C14").Formula = "=SUM(A14:B14)" ' <<< Change the Formula here
Range("C14").AutoFill Range("C14:C40")
Application.ScreenUpdating = True
End Sub

Regards

Trevor

 

[robertguy]

Trevor

When I try to put my formulae in

e.g. =IF(ISERROR(VLOOKUP(Pick2,Data!$B$2:$C$1067,2,FALSE)),
",(VLOOKUP(Pick2,Data!$B$2:$C$1067,2,FALSE)))

it throws up and error due to the quotation maks alreay in the formu
!


Is there a way around this ?



Ro