Is there an easier way?

H

hotherps

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
 
B

Bob Phillips

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)
 
M

merjet

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.
Click to expand...

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
 
D

Don Guillett

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
 
A

Alan Beban

Bob said:
For Each Cell in Un
ion(range("G11:H125"),range("I11:K125"),range("L11:N125"))
Click to expand...
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
 
A

Alan Beban

hotherps said:
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
Click to expand...
Moving from "G11:H125" to "Ill:K125" is *not* "moving over three columns
at a time".

Alan Beban
 
A

Alan Beban

Did you test this before posting?

Alan Beban
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
Click to expand...
 
A

Alan Beban

Don said:
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
Click to expand...
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
 
D

Don Guillett

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>
 
A

Alan Beban

Don said:
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>
Click to expand...
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
 
B

Bob Phillips

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)
 
A

Alan Beban

hotherps said:
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
Click to expand...
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
 
Top