Using case in vba

QuestionEdit

I'm having difficulty with a Select case funcion. I have written this code:

Select Case (cycleShortcut)
               Case F2 'Finance Reporting
                   Workbooks("Cover_Sheet").Worksheets(asName).Range("B21").Offset(0, TypeOfCount) = Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(0, TypeOfCount) + 1
               Case FA 'Fixed Assets SoD
                   Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(1, TypeOfCount) = Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(1, TypeOfCount) + 1
               Case "P1" 'Purchasing And AP SoD
                   Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(2, TypeOfCount) = Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(2, TypeOfCount) + 1
               Case Pi 'Production Inventory SoD
                   Workbooks("Cover_Sheet.xls").Worksheets(asName).Range("B21").Offset(3, TypeOfCount) = Workbooks("Cover_Sheet.xls").Worksheets(asName).Range("B21").Offset(3, TypeOfCount) + 1
               Case R1 'Revenue SoD
                   Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(4, TypeOfCount) = Workbooks("Cover_Sheet").Sheets(asName).Range("B21").Offset(4, TypeOfCount) + 1
           End Select

cycleShortcut is a String variable. When I run the macro it goes trough the whole code, and does not update anything (nor there is any error. When I run part of the code (one case at a time) it works fine.

Could you kindly take a look at the funcion? I'm not sure what am I doing wrong..

Best Kuba

AnswerEdit

Kuba,

If I take out the long lines of code you have for incrementing a count, then you can see you case statement. Select Case (cycleShortcut)

              Case F2 'Finance Reporting
              Case FA 'Fixed Assets SoD
              Case "P1" 'Purchasing And AP SoD
              Case Pi 'Production Inventory SoD
               Case R1 'Revenue SoD
          End Select

Note that only "P1" is a string. All others are just uninitialized (assumed) variables. If they are supposed to represent string values that cycleshortcut could have, then you need to put them in quotes

Select Case (cycleShortcut)

              Case "F2" 'Finance Reporting
              Case "FA" 'Fixed Assets SoD
              Case "P1" 'Purchasing And AP SoD
              Case "Pi" 'Production Inventory SoD
              Case "R1" 'Revenue SoD

End Select

Hopefully that will fix the problem. This assumes that the lines of code that I removed are good - that the variables you use in those statements have proper values and so forth.