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