PDA

View Full Version : Solved: Select case statement problem



Bazza
07-31-2009, 05:56 AM
hi there. i have a bit of a problem with a select case statement - subscript out of range error.
possibly its cos there are more than one statements to execute for each case. not sure. help wud b appreciated. :help

see below.

Sub TheSelectCase2()

Select Case Worksheets("sheet1").Range("A538").text


Case "Tav 4"
Worksheets("sheet1").Range("a540").Value = "Tav 4"
Worksheets("sheet1").Range("a541").Value = "blank"
Worksheets("sheet1").Range("a542").Value = "blank"
Worksheets("sheet1").Range("a543").Value = "blank"
Worksheets("sheet1").Range("a544").Value = "blank"
Worksheets("sheet1").Range("a545").Value = "blank"
Worksheets("sheet1").Range("a546").Value = "blank"
Worksheets("sheet1").Range("a547").Value = "blank"
Worksheets("sheet1").Range("a548").Value = "blank"


Case "Tav 2"
Worksheets("sheet1").Range("a540").Value = "Tav 2"
Worksheets("sheet1").Range("a541").Value = "blank"
Worksheets("sheet1").Range("a542").Value = "blank"
Worksheets("sheet1").Range("a543").Value = "blank"
Worksheets("sheet1").Range("a544").Value = "blank"
Worksheets("sheet1").Range("a545").Value = "blank"
Worksheets("sheet1").Range("a546").Value = "blank"
Worksheets("sheet1").Range("a547").Value = "blank"
Worksheets("sheet1").Range("a548").Value = "blank"

Case "SWC"
Worksheets("sheet1").Range("a540").Value = "SWC"
Worksheets("sheet1").Range("a541").Value = "blank"
Worksheets("sheet1").Range("a542").Value = "blank"
Worksheets("sheet1").Range("a543").Value = "blank"
Worksheets("sheet1").Range("a544").Value = "blank"
Worksheets("sheet1").Range("a545").Value = "blank"
Worksheets("sheet1").Range("a546").Value = "blank"
Worksheets("sheet1").Range("a547").Value = "blank"
Worksheets("sheet1").Range("a548").Value = "blank"

End Select
End Sub

Benzadeus
07-31-2009, 06:11 AM
Change
Select Case Worksheets("sheet1").Range("A538").Text

For
Select Case Worksheets("sheet1").Range("A538")

Benzadeus
07-31-2009, 06:12 AM
Also, be sure that the sheet that you are working on (tab name) is sheet1.

Bob Phillips
07-31-2009, 06:15 AM
Subscript out of rage suggests there is no sheet called Sheet1

Bazza
07-31-2009, 06:51 AM
thankx guys.

quick response and my little macro works perfectly.
cheers

mdmackillop
07-31-2009, 07:04 AM
A little "shortening"

Sub TheSelectCase2()
With Sheets("Sheet1")
Select Case .Range("A538").Text
Case "Tav 4"
.Range("a540").Value = "Tav 4"
.Range("a541").Resize(8) = "blank"
Case "Tav 2"
.Range("a540").Value = "Tav 2"
.Range("a541").Resize(8) = "blank"
Case "SWC"
.Range("a540").Value = "SWC"
.Range("a541").Resize(8) = "blank"
End Select
End With
End Sub

Bazza
08-06-2009, 03:31 AM
Thankx. Macro is now a zillion lines shorter.

However the following had to be changed before it worked: "text" had to change it to "value"

Sub TheSelectCase2()
With Sheets("Sheet1")
Select Case .Range("A538").Text

Bob Phillips
08-06-2009, 05:51 AM
Why?

fleetfoot
08-20-2009, 06:51 AM
Not being a boffin on VBA, i have absolutely no idea. it just worked with the one and not the other........Sorry. :dunno