Simple For-Each question ... but not for me ...

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I'm trying to perform some code against two small ranges, but it's not
working .... here's the first line of the code:
For Each r In Worksheets("ADMIN").Range("c16:c34", "I16:I33")

When I run the code, it runs the actions against ALL cells in range
C16:I33!

What am I doing wrong?

TIA,
ray
 
You might try:

For Each r In
UNION(Worksheets("ADMIN").Range("c16:c34"),Worksheets("ADMIN").Range("I16:I33"))

AFAIK, Range(Cell1,Cell2) syntax is generally used to specify start (Cell1)
& end (Cell2) points of a single contiguous range. Since you fed it multiple
cells rather than the single cell arguments it expected, Excel simply
constructed a Range based on the topleft cell of Cell1 and the bottomright
cell of Cell2, resulting in C16:I33.

HTH,
 
try it this way:

With Worksheets("ADMIN")
For Each r In Union(.Range("C16:C34"), .Range("I16:I33"))
'your code here
Next r
End With
 
Try a union instead of a range

Sub test1()

With Worksheets("ADMIN")
For Each r In Union(.Range("c16:c34"), .Range("I16:I33"))

Next r
End With
End Sub
 
Thanks George .... that did the trick! And I also appreciate the tip
on specifying ranges -- good to know!

Regards,
ray
 
Back
Top