Intro VBA course - select case code

Resolved · Low Priority · Version 2010

Fri 27 May 2016, 16:32 Delegate James said...

James has attended:
Excel VBA Introduction course

Intro VBA course - select case code

Hi

Can you please send me the code for the EmployeeTimes spreadsheet that I used on Wednesday, I'm having trouble getting select case to work.

Thanks

James

Tue 31 May 2016, 10:35 Trainer Marius said...

RE: Intro VBA course - select case code

Hi James,

Thank you for your post. Here is the solution code for both parts of the Employee Times exercise:

Sub HoursWorked()

Range("G2").Select

Do Until ActiveCell.Offset(0, -5) = "" ' Until EmployeeID empty

ActiveCell = ((ActiveCell.Offset(0, -1) - ActiveCell.Offset(0, -2)) * 24)

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Sub PayRate()

Range("H2").Select

Do Until ActiveCell.Offset(0, -6) = ""

Select Case ActiveCell.Offset(0, -4)

Case Is = "A1"
ActiveCell = ActiveCell.Offset(0, -1) * 55
Case Is = "A2"
ActiveCell = ActiveCell.Offset(0, -1) * 50
Case Is = "B1"
ActiveCell = ActiveCell.Offset(0, -1) * 45
Case Is = "B2"
ActiveCell = ActiveCell.Offset(0, -1) * 40
Case Is = "B3"
ActiveCell = ActiveCell.Offset(0, -1) * 35
Case Is = "C1"
ActiveCell = ActiveCell.Offset(0, -1) * 30
Case Is = "C2"
ActiveCell = ActiveCell.Offset(0, -1) * 25
Case Else
ActiveCell = "Error"

End Select

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Enjoy your day
Marius Barnard
Excel Trainer