Loops...

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Perfect... Thankyou very much!!

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)