[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

[joeu2004[_2_]]

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


----- original message -----

"Howard" wrote in message
...
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

[Howard]

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

Thanks,
joeu2004

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[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):

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

[Howard]

On Saturday, September 7, 2013 2:38:43 AM UTC-7, Claus Busch wrote:
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):



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

I like!!

Thanks Claus.

[Claus Busch]

Hi Howard,

Am Sat, 7 Sep 2013 03:42:55 -0700 (PDT) schrieb Howard:

I like!!

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2