Select Case code error

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
You are missing some Next statements to match the For Each statements.


----- original message -----
 
Hi Howard,

Am Fri, 6 Sep 2013 22:47:34 -0700 (PDT) schrieb Howard:
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.
Click to expand...

you don't need a SELECT CASE:

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

Dim c As Range
Dim myRng As String

myRng = Target & "x"
For Each c In Range(myRng)
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


Regards
Claus B.
 
you don't need a SELECT CASE:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$H$1" Then Exit Sub



Dim c As Range

Dim myRng As String



myRng = Target & "x"

For Each c In Range(myRng)

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





Regards

Claus B.
Click to expand...

Ah, yes. I see where you are going here.
Given the code I posted that works quite nice and is way more concise.

My bad is that the code I posted has further errors (not syntax, but rather as the code relates to the data.)

This offset only works with Swine which is in col A.
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)

Dairy is in col B and the offsets should have been 3 and 4, Beef 2 and 3.

Sorry for the poor posting, but I am keeping the suggestion for further reference.

Thanks Claus.
Regards,
Howard
 
Hi Howard,

Am Sat, 7 Sep 2013 01:37:37 -0700 (PDT) schrieb Howard:
This offset only works with Swine which is in col A.
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)

Dairy is in col B and the offsets should have been 3 and 4, Beef 2 and 3.
Click to expand...

you can do that also a bit easier (enlarge the code - I have only three
items in it):

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

Dim c As Range
Dim myOffset As Integer

Select Case Target.Value
Case "Swine"
myOffset = 4
Case "Diary"
myOffset = 3
Case "Beef"
myOffset = 2
End Select

With Range(Target & "x").SpecialCells(xlCellTypeConstants)
.Copy Range("I50").End(xlUp).Offset(1, 0)
.Offset(columnoffset:=myOffset).Copy _
Range("J50").End(xlUp).Offset(1, 0)
.Offset(columnoffset:=myOffset + 1).Copy _
Range("K50").End(xlUp).Offset(1, 0)
End With
..
..


Regards
Claus B.
 
Hi again,

Am Sat, 7 Sep 2013 11:03:04 +0200 schrieb Claus Busch:
you can do that also a bit easier (enlarge the code - I have only three
items in it):
Click to expand...

or:

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

Dim c As Range
Dim myOffset As Integer
Dim rngBig As Range

Select Case Target.Value
Case "Swine"
myOffset = 4
Case "Diary"
myOffset = 3
Case "Beef"
myOffset = 2
End Select

With Range(Target & "x").SpecialCells(xlCellTypeConstants)
Set rngBig = Union(Range(Target & "x") _
.SpecialCells(xlCellTypeConstants), _
.Offset(columnOffset:=myOffset), _
.Offset(columnOffset:=myOffset + 1))
rngBig.Copy Range("I50").End(xlUp).Offset(1, 0)
End With


Regards
Claus B.
 
Hi again,



Am Sat, 7 Sep 2013 11:03:04 +0200 schrieb Claus Busch:







or:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$H$1" Then Exit Sub



Dim c As Range

Dim myOffset As Integer

Dim rngBig As Range



Select Case Target.Value

Case "Swine"

myOffset = 4

Case "Diary"

myOffset = 3

Case "Beef"

myOffset = 2

End Select



With Range(Target & "x").SpecialCells(xlCellTypeConstants)

Set rngBig = Union(Range(Target & "x") _

.SpecialCells(xlCellTypeConstants), _

.Offset(columnOffset:=myOffset), _

.Offset(columnOffset:=myOffset + 1))

rngBig.Copy Range("I50").End(xlUp).Offset(1, 0)

End With





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

I like!!

Thanks Claus.
 
Hi Howard,

Am Sat, 7 Sep 2013 03:42:55 -0700 (PDT) schrieb Howard:
I like!!
Click to expand...

thank you for the feedback.
Most of the time I use SELECT CASE only to initialize variables. The
main action with these new variables I do after END SELECT.
So you can easier read the code.


Regards
Claus B.
 
Back
Top