Problem with Case statement

[Rhythmical]

Hello,

I'm having trouble with a VB code I'm trying to use. Basically I have two columns one with the data the other without. The data in the left had side is a series of numbers separated in phone number format. I need the code to go down through the rows and test the data against certain criteria. If it finds a match I need it to paste into the right hand column.

Any help you could provide would be great! I can not work out why below does not work?

Sub Sell()
Dim myCell As Range

For Each myCell In Range("C2:C1859")
    With myCell.Offset(0, 1)
        Select Case myCell.Value


     Select Case myCell.Value

          Case Is = "02 8756 0"
                
                .Value = "Aspect Bush"
              
          Case Is = "02 8756 1"

              .Value = "Aspect Bush"

          Case Is = "03 8634 0"

              .Value = "800 Bourke"

          Case Is = "03 8634 1"

              .Value = "800 Bourke"

          Case Is = "03 8634 2"

              .Value = "800 Bourke"
              
          Case Is = "03 8634 3"

              .Value = "800 Bourke"
              
          Case Is = "03 8634 4"

              .Value = "800 Bourke"
              
          Case Is = "03 8634 8"

              .Value = "800 Bourke"
              
          Case Is = "03 8641 0"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 1"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 2"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 3"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 4"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 5"

              .Value = "500 Bourke"
              
          Case Is = "03 8641 7"

              .Value = "Aspect 120"
              
          Case Is = "03 8641 8"

              .Value = "Aspect 383"
              
          Case Is = "03 8641 9"

              .Value = "Aspect 383"
              
          Case Is = "03 9277 8"

              .Value = "Aspect 383"
              
         Case Is = "03 9277 9"

              .Value = "Aspect 383"

         Case Else

              .Value = "0"
             
    End Select

End With

Next myCell

End Sub

 

[dave3009]

You have a superfluous Select Case

For Each myCell In Range("C2:C1859")
    With myCell.Offset(0, 1)
        Select Case myCell.Value    '<--------Here


     Select Case myCell.Value

          Case Is = "02 8756 0"

 

[Rhythmical]

You have a superfluous Select Case

For Each myCell In Range("C2:C1859")
    With myCell.Offset(0, 1)
        Select Case myCell.Value    '<--------Here


     Select Case myCell.Value

          Case Is = "02 8756 0"

Thanks dave3009.

I got rid of that but still no good. Any other ideas?

 

[Rhythmical]

Finally got it working. realized every time I changed something I didn't press the play button. I'm such an idiot

 

[ukmikeb]

Hi

Perhaps -

    Select Case myCell.Value
 
          Case Is = 0287560, 0287561
 
              .Value = "Aspect Bush"
 
          Case Is = 0386340, 0386341, 0386342, 0386343, 0386344, 0386348
 
             .Value = "800 Bourke"
 
          Case Is = 0386410, 0386411, 0386412, 0386413, 0386414, 0386415
 
              .Value = "500 Bourke"

etc, etc...

hth

Edit - You solved it in the meantime.