Solved: Select case statement problem

[Bazza]

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.

see below.

[vba]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

[/vba]

[Benzadeus]

Change
[VBA] Select Case Worksheets("sheet1").Range("A538").Text[/VBA]

For
[VBA] Select Case Worksheets("sheet1").Range("A538")[/VBA]

[Benzadeus]

Also, be sure that the sheet that you are working on (tab name) is sheet1.

[Bob Phillips]

Subscript out of rage suggests there is no sheet called Sheet1

[Bazza]

thankx guys.

quick response and my little macro works perfectly.
cheers

[mdmackillop]

A little "shortening"
[VBA]
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
[/VBA]

[Bazza]

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"

[VBA]Sub TheSelectCase2()
With Sheets("Sheet1")
Select Case .Range("A538").Text [/VBA]

[Bob Phillips]

Why?

[fleetfoot]

Not being a boffin on VBA, i have absolutely no idea. it just worked with the one and not the other........Sorry.