Using the For Each Loop in Excel 1

[logius]

Okay, here's the setup: I've got a range (in a single row) of non-sequential cells saved into a named range (let's call it MyRange.. original, ain't I?) Now, there's going to be a second range (in a single column) of non sequential cells that I'm going to get (let's call it Bob). What I want to do is once I have BOTH sets of ranges, use them as coordinates to select a third set of cells that I'll save into yet another range (let's call it AnotherRangeThatImPuttingStuffInto).

Here's a kind of graphical representation.
oct-99 nov-99 dec-99 jan-00 feb-00 mar-01
Set 1 81 87 92 95 75 84
Set 2 34 39 38 43 44 48
Set 3 20 24 27 31 33 39
Set 4 139 139 145 148 146 149
Set 5 76 79 62 60 57 53

The dates in bold are stored in MyRange, and the Sets in bold are stored in Bob. I was thinking of using the For Each loop to go through all the cells in MyRange and then just take their column values and hook them up with Bob's row values then set the cooresponding cells to AnotherRangeThatImPuttingStuffInto (using the Union method). Problem is, I don't know how to do that! This is the first time I've actually used For Each, and the examples I've seen on the web aren't all that insightful.

Help me out here, guys, please.

 

[Sawatzky]

Dear logius

Dim hugo As Range 'the German bob <g>
Dim baerbel As Range
For Each hugo In Range(&quot;bob&quot
For Each baerbel In Range(&quot;myrange&quot
Debug.Print &quot;column &quot; & hugo.Column
Debug.Print &quot;Row &quot; & baerbel.Row
' or wathever you want

Next baerbel

Next hugo

HTH

yours Astrid

 

[logius]

Dude, thanks. I'm still tweaking it, but this code does exactly what I needed. Next order of seurkraut is on me. =)

Quick question, though. Do you know of a way to test if a range is empty? I tried:

    If Range3 = Nothing Then 'I tried = Null too
       'blah blah blah
    Else
       'uga uga uga
    End If

but it doesn't work. Says that I can't use &quot;Nothing&quot; like that. Any suggestions?

 

[Sawatzky]

Dear logius,


it is like this:

If Range3 IS Nothing Then

'blah blah blah
Else
'uga uga uga
End If

or if you are testing for something that could be NULL

If ISNULL(couldbenull) Then

'blah blah blah
Else
'uga uga uga
End If



regards Astrid

 

[logius]

Dude, you're cool.