View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Howard Howard is offline
external usenet poster
 
Posts: 536
Default Select Case code error

This errors out as "Case without Select Case" on the Case Is = "Beef".

I first wrote the code for Swine only, just one case and it worked fine. I added the other cases and now the error, but only on Case is = "Beef".

Range H1 is a drop down with Swine, Dairy, Beef, Poultry.

Swinex and Dairyx etc. are named ranges.

Thanks.
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$H$1" Then Exit Sub

Dim c As Range

Select Case Target.Value

Case Is = "Swine"
For Each c In Range("Swinex")
If c.Value < "" Then
c.Copy Range("I50").End(xlUp).Offset(1, 0)
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)
End If
Next

Case Is = "Dairy"
For Each c In Range("Dairyx")
If c.Value < "" Then
c.Copy Range("I50").End(xlUp).Offset(1, 0)
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)
End If

'Case without Select Case - Cafe Is highlighted
Case Is = "Beef"
For Each c In Range("Beefx")
If c.Value < "" Then
c.Copy Range("I50").End(xlUp).Offset(1, 0)
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)
End If

Case Is = "Poultry"
For Each c In Range("Poultryx")
If c.Value < "" Then
c.Copy Range("I50").End(xlUp).Offset(1, 0)
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)
End If

Case Is = ""
MsgBox "Blank stuff"

End Select

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("I2:K9")
.Header = xlGuess
.SortMethod = xlPinYin
.Apply
End With

End Sub