PDA

View Full Version : Multiple Range Loop



bradh_nz
08-23-2007, 07:40 AM
I currently have this loop working however if I try to get it to select multiple ranges it errors, any ideas
ie need this

Range("G6:W79,Z6:AD79").Select For Each C In Selection.Cells


Sub Insert_Formulas()
Dim C As Range
'Loop format pivot table borders
Range("H8:L32").Select For Each C In Selection.Cells
C.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

Bob Phillips
08-23-2007, 07:48 AM
When looping and capturing a variable as the loop control, you use that variable not whole range, like this



Sub Insert_Formulas()
Dim C As Range
'Loop format pivot table borders
Range("G6:W79,Z6:AD79").Select
For Each C In Selection
C.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
C.Copy
C.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
C.Interior.ColorIndex = 15
Next C
End Sub

Bob Phillips
08-23-2007, 07:50 AM
You can also do it without selecting



Sub Insert_Formulas()
Dim C As Range
'Loop format pivot table borders
For Each C In Range("G6:W79,Z6:AD79")
C.FormulaR1C1 = "=VLOOKUP(R5C,INDIRECT.EXT(""'""&RC5),3,FALSE)"
C.Value = C.Value
C.Interior.ColorIndex = 15
Next C
End Sub

Katiebee178
12-12-2011, 05:01 AM
The previous code you posted last was very helpful to me Thanks!
Is it possible to treat the two ranges as separate? I am trying to store all the info form two columns as x and y values and then put them through an if statement?