Is there an easier way?
 

I need to referenece the following ranges, I'd rather not repeat the
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thank

--
Message posted from http://www.ExcelForum.com

Is there an easier way?
 

For Each Cell in Un
ion(range("G11:H125"),range("I11:K125"),range("L11 :N125"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in message
...
I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thanks


---
Message posted from http://www.ExcelForum.com/

Is there an easier way?
 

I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

You probably need to adjust the "5 To 17", but the following
should do it.

Dim iCt As Integer
Dim c as Range

For iCt = 5 To 17 Step 3
For Each c In Sheets("Sheet1").Range(Cells(11, iCt), Cells(125, iCt +
2))
'run code Next c
Next iCt

HTH,
Merjet

Is there an easier way?
 

See if this helps. 7 is col G . Change the 12 for your last column desired.

Sub runranges()
For i = 7 To 12 Step 2
For Each c In Range(Cells(11, i), Cells(15, i + 1))
MsgBox c.Address 'your code
Next c
Next i
End Sub
--
Don Guillett
SalesAid Software

"hotherps " wrote in message
...
I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thanks


---
Message posted from http://www.ExcelForum.com/

Is there an easier way?
 

Bob Phillips wrote:

For Each Cell in Un
ion(range("G11:H125"),range("I11:K125"),range("L11 :N125"))

This isn't any different from

For Each Cell in Range("G11:N125")

and it assumes that the code is the same for of the three ranges.

Alan Beban

Is there an easier way?
 

Thanks Guys they both worked!

--
Message posted from http://www.ExcelForum.com

Is there an easier way?
 

hotherps < wrote:

I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thanks


---
Message posted from http://www.ExcelForum.com/

Moving from "G11:H125" to "Ill:K125" is *not* "moving over three columns
at a time".

Alan Beban

Is there an easier way?
 

Did you test this before posting?

Alan Beban

merjet wrote:

I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.


You probably need to adjust the "5 To 17", but the following
should do it.

Dim iCt As Integer
Dim c as Range

For iCt = 5 To 17 Step 3
For Each c In Sheets("Sheet1").Range(Cells(11, iCt), Cells(125, iCt +
2))
'run code Next c
Next iCt

HTH,
Merjet

Is there an easier way?
 

hotherps < wrote:
Thanks Guys they both worked!!


---
Message posted from http://www.ExcelForum.com/

To do what??? They didn't work for me.

Alan Beban

Is there an easier way?
 

Don Guillett wrote:

See if this helps. 7 is col G . Change the 12 for your last column desired.

Sub runranges()
For i = 7 To 12 Step 2
For Each c In Range(Cells(11, i), Cells(15, i + 1))
MsgBox c.Address 'your code
Next c
Next i
End Sub
Did you test the code before posting? It operates on ranges G:H, I:J
(instead of I:K) and K:L (instead of L:N).

Alan Beban

Is there an easier way?
 

I looked at his FIRST range which referenced g:H
so it should be step 3 and i+2 for every third column
but the op could have figured that out.
"It's the thought that counts" <G

--
Don Guillett
SalesAid Software

"Alan Beban" wrote in message
...
Don Guillett wrote:

See if this helps. 7 is col G . Change the 12 for your last column
desired.

Sub runranges()
For i = 7 To 12 Step 2
For Each c In Range(Cells(11, i), Cells(15, i + 1))
MsgBox c.Address 'your code
Next c
Next i
End Sub
Did you test the code before posting? It operates on ranges G:H, I:J
(instead of I:K) and K:L (instead of L:N).

Alan Beban

Is there an easier way?
 

Don Guillett wrote:

I looked at his FIRST range which referenced g:H
so it should be step 3 and i+2 for every third column
but the op could have figured that out.
"It's the thought that counts" <G

Well, that doesn't get what the OP asked for either, so I'm not sure
what you're suggesting he could have figured out.

The OP indicated that "the range moves over three columns at a time,
like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc."

That's incoherent; G11:H125 to I11:K125 is *not* "moving over three
columns." And Don Guillett's revised code doesn't track with G:H, I:K,
and L:N, but with G:I, J:L, and M:O.

If the OP cares to clarify, I'll be happy to provide code.

Alan Beban

Is there an easier way?
 

glad to see such a positive approach!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan Beban" wrote in message
...
hotherps < wrote:

I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thanks


---
Message posted from http://www.ExcelForum.com/

Moving from "G11:H125" to "Ill:K125" is *not* "moving over three columns
at a time".

Alan Beban

Is there an easier way?
 

hotherps < wrote:

I need to referenece the following ranges, I'd rather not repeat them
over, the range moves over three columns at a time, like this:

For Each CELL In range("G11:H125")
'run code
For Each CELL In range("I11:K125")
'run code
For Each CELL In range("L11:N125")
'run code
etc.

Thanks


---
Message posted from http://www.ExcelForum.com/

Sub runranges()
Dim rng As Range, n As Long, iCt As Long, c As Range
Set rng = Sheets("Sheet1").Range("A1")
n = 13
For iCt = 7 To n Step 3
If iCt = 7 Then
For Each c In Range(rng(11, iCt), rng(15, iCt + 1))
Debug.Print c.Address 'your code
Next c
ElseIf iCt = 10 Then
iCt = iCt - 1
For Each c In Range(rng(11, iCt), rng(15, iCt + 2))
Debug.Print c.Address 'your code
Next c
Else
For Each c In Range(rng(11, iCt), rng(15, iCt + 2))
Debug.Print c.Address 'your code
Next c
End If
Next iCt
End Sub

Alan Beban