For Each Range in Worksheet

[jlclyde]

Here is how my data is laid out. I have 4 ranges on a sheet. The
first range is B9:I21, the second range is K9:R21, the third is
B32:I44 and the fourth is K32:R44. Each of them is declared in the
macro. Each of these ranges ere exactly the same. 1st column is
date, 2nd is output, third is Hours. I am currently doing a "for each
i in rng" to look at each i and determine if the date value is found
on a seperate workbook. Each range has a different column to lookup
on the seperate workbook. So now to the question.

Is there away to do a for each range in worksheet like you can do for
for each i in range?
Is there away to tell what rng your i is currently in? (if it were in
the first range I could specify which column on the seperate workbook,
where the data is stored, to look at)

Sorry this was so lengthy,
Jay

 

[Gary''s Student]

One way is to assign a Name to each of the ranges, say alpha, beta, gamma,
delta
Then something like:

Sub dural()
s = "alpha,beta,gamma,delta"
namme = Split(s, ",")
For i = 0 To 3
Set r = Range(namme(i))
Set rbase = r(1)
For i = 1 To 34
' enter coding using an offset from rbase
Next
Next
End Sub


The outer For loops over each of the Names Ranges. The inner For loops over
the cells in that range.

 

[Dave Peterson]

I'm not sure I understand what you're doing, but maybe...

Dim myRng as range
dim myArea as range
dim myCell as range
dim wks as worksheet
Dim FirstArea as boolean

set wks = worksheets("Somesheetnamehere")
with wks
set myrng = .range("b9:i21,k9:r21,b32:i44,k32:r44")
end with

firstarea = true
for each myarea in myrng.areas '4 different areas in myRng
if firstarea = true then
'do something special
'and change that flag to false
firstArea = false
end if
for each mycell in myarea.columns(1)
msgbox mycell.address(0,0)
next mycell
next myarea

 

[jlclyde]

I think what I am going to do is cheat. I am going to put all the
ranges on one sheet, then have the true ranges just equal that sheet.
Then I can just have the code Read Case Select i.row.

Thansk for the insite,
Jay