Select Case not working

**RadBrad** · 08-30-2009, 12:26 PM

 Set GL = ActiveSheet.Range("d6:as6")
 Set SW = ActiveSheet.Range("aw6:cz6")
         
         If Process = "GL" Then
            GL.Activate
                        
            For Each Cell In Range("d6:as6")  '<--------- How do I call this properly?
                If Cell.Value = Designation Then
                    
                    Select Case Quantity.Value  '<----- Object Required
                    
                        Case 1 To 14
                            PostCol = Cells.Column
                            
                        Case 15 To 24
                            PostCol = Cells.Offset(0, 1).Column
                            
                        Case 24 To 999
                            PostCol = Cells.Offset(0, 2).Column
                      
                    End Select
                  End If
              Next Cell
          End If

This is part of a function I am trying to make. I am getting an "Object Required" error. Quantity is passed from the Sub to the function o.k. (Inteli-Sense shows the value for me). Can I not use a Select Case operation this way?

Also, when I start the loop, that is the only way I get it to recognize the range. How do I start that loop properly?

All help is appreciated!
Thanks!

**mikerickson** · 08-30-2009, 03:11 PM

What do you mean "start the loop properly"?

For Each Cell in Range("D6:AS6")
'...
Next Cell

is fine syntax.

The variables Designation and Quantity need to be assigned values.

Is Quantity a Range?

**RadBrad** · 08-30-2009, 04:26 PM

Farther up the code, designation and quantity are assigned.

I guess what I mean by Proper is that if I am going to assign the range as cell values, would I need to set a range, like my GL and SW?

I can see by trial and error that I do not. I was trying to define a range that I thought was changing, but is not.

And I figured out the Select Case question. It is just Select Case Quantity without the "value" part, which makes sense, too.

Thanks!

**mikerickson** · 08-30-2009, 05:37 PM

Is this closer to what you want?

Dim RangeForLooping as Range, Cell as Range
Set RangeForLooping = Range("D6:AS6")

For Each Cell in RangeForLooping.Cells
    '...
Next Cell

**RadBrad** · 08-31-2009, 01:11 AM

Yes! That is exactly what I was looking for.

What I am writing this module for is to post data from a daily scheduling worksheet to several different tracking sheets based on the sales streams, processes used, styles of objects, and diameters. In the future, I will have to add different styles, sales streams, and processes and I'd like to test each sheet to define the ranges needed. Before your answer, I was looking at having to adjust the module by hand as the worksheets increased in size.

Thank you very much!