Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Clean this select case code up a bit. | Excel Programming | |||
Case select returning error when cell contains #N/A : how must i avoid this error | Excel Programming | |||
use of Case-Select, with on error | Excel Programming | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
select case error | Excel Programming |