About Us

Perfect... Thankyou very much!!

"Bob Phillips" wrote:

Sub TB()
Dim aryIndex, arySite
Dim i As Long

aryIndex = Array("a very long formula!", "a very long formula!", "a very
long formula!")
arySite = Array("CAS", "ADM", "ADMIN")

Application.Calculation = xlCalculationManual

For i = LBound(aryIndex) To UBound(aryIndex)

With Sheets(arySite(i))
.Range("D8").FormulaR1C1 = aryIndex(i)
.Range("D8").Copy
.Range("D8:AC846").PasteSpecial Paste:=xlPasteFormulas
.Calculate
.Range("D8").Copy
.Range("D8:AC846").PasteSpecial Paste:=xlPasteValues
End With

End With

Application.CutCopyMode = False
Application.Calculation = xlCalculationManual
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Willabo" wrote in message
...
Hi,
I have a section of code for updating a TB Workbook and would like to
create
a loop circuling through the constants...

Option Explicit
Const INDEX1 As String = "a very long formula!"
Const INDEX2 As String = "a very long formula!"
Const INDEX3 As String = "a very long formula!"
Const SITE1 As String = "CAS"
Const SITE2 As String = "ADM"
Const SITE3 As String = "ADMIN"

Sub TB()
Application.Calculation = xlCalculationManual
Sheets(SITE1).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX1
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Sheets(SITE2).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX2
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Sheets(SITE3).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX3
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Application.Calculation = xlCalculationManual
End Sub

Nothing i've tried works!!

Thanks
Will

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
do loops	saravanan	Excel Worksheet Functions	0	June 13th 06 10:53 AM
For next loops	Kate	Excel Discussion (Misc queries)	5	May 22nd 06 01:11 PM
How to Chart the Maximum Point within Two Loops	Faye	Charts and Charting in Excel	3	May 11th 06 08:00 PM
Do loops	grandfilth	Excel Discussion (Misc queries)	1	November 10th 05 12:00 AM
Max limit of 7 nested loops	Subu	Excel Worksheet Functions	2	May 28th 05 02:14 PM

Menu

About Us