PDA

View Full Version : Solved: Loop Problem



bradh_nz
08-24-2007, 05:44 AM
Hello, I have the below code to run a loop howver I am having problmes getting it to start in the desired range )Range("G6:W10").Select) as it starts running in cell A1


Sub LoopFormula()
ScreenUpdating = False
Range("G6:W10").Select
cc = Selection.Columns.Count
rc = Selection.Rows.Count

For i = 1 To cc
For j = 1 To rc


Cells(j, i).Value = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
Cells(j, i).Copy
Cells(j, i).PasteSpecial Paste:=xlPasteValues


Next j
Next

ScreenUpdating = False
End Sub

Any help appreciated

Bob Phillips
08-24-2007, 06:00 AM
Sub LoopFormula()
Application.ScreenUpdating = False
Range("G6:W10").Select
cc = Selection.Columns.Count
rc = Selection.Rows.Count

For i = 7 To 7 + cc - 1
For j = 6 To 6 + rc - 1
Cells(j, i).FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
Cells(j, i).Copy
Cells(j, i).PasteSpecial Paste:=xlPasteValues
Next j
Next

Application.ScreenUpdating = False
End Sub

Bob Phillips
08-24-2007, 06:02 AM
You can even simplify it



Sub LoopFormula()
Application.ScreenUpdating = False

For Each cell In Range("G6:W10")
cell.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
cell.Value = cell.Value
Next

Application.ScreenUpdating = False
End Sub

bradh_nz
08-24-2007, 06:02 AM
great thanks

mdmackillop
08-24-2007, 10:05 AM
Without looping?

Sub NoLoopFormula()
Application.ScreenUpdating = False

With Range("G6:W10")
.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
.Copy
.PasteSpecial xlPasteValues
End With
Application.ScreenUpdating = False
End Sub