Is there an easier way?

  • Thread starter Thread starter hotherps
  • Start date Start date
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
 
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)
 
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
 
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
 
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
 
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
 
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...
 
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
 
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 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
 
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)
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Concat a three column range into one cell Then Next... 2
Sum work hours to hours & nearest 10th of an hour 14
Displaing result in Entire worksheet 4
Search other sheets for numbers & color them 10
Code to Concat a row in loop 2
Loop is slow trying array code 17
Array to Multiple Arrays 3

Back
Top