Multiple Range Loop

[bradh_nz]

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]

When looping and capturing a variable as the loop control, you use that variable not whole range, like this

[vba]

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
[/vba]

[Bob Phillips]

You can also do it without selecting

[vba]

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
[/vba]

[Katiebee178]

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?