Select Case code error

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
 
J

joeu2004

You are missing some Next statements to match the For Each statements.


----- original message -----
 
H

Howard

Holy moley, that really jumps out once the obvious is pointed out.

Thanks,
joeu2004
 
C

Claus Busch

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.
 
H

Howard

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
 
C

Claus Busch

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.
 
C

Claus Busch

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.
 
H

Howard

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.
 
C

Claus Busch

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Case Code does not run... 10
Copy row info but not in normal column sequence 6
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet 27
Resize a row but copy but skip columns I don't want to copy 6
Clean this select case code up a bit. 2
Array with many cells from a row but not in order or together 11
A simple if <> nn then Msgbox "No Match" 5
Sheet to sheet code Type Mismatch 6

Top