Payroll worksheet with VLOOKUP issues

Hello and Welcome to the Forum

Thanks a lot for attaching your sample file.

A couple of quick remarks :

1. From looking at your formula, there is no evident mistake

2. What is your expected result ?

3. Since everything is password-protected, there is no possibility to edit ...

Hi again,

By looking into more details .... would appear to me :

1. Formula in cell Y2 should be : =X2&IF(J2="Express Cycle",J2,"")&W2

2. Formula in cell Z2 should be : =IFERROR(VLOOKUP(Y2,'Master Pay Rates'!$A$11:$C$2002,3,FALSE),"-")

This would require to be tested ... since I cannot do it

Thanks.

Just tested both modifications as per post #3 ... can confirm they do operate as expected ...

Hope the modifications are working fine.

You are right : "Express Cycle" does represent an exception to the general rule and it would, indeed, come from Column J

Quote from PETRODATAjames

Does "Express Cycle" need to be there for the exception? Can it be any name as long as it's not MEGA All Levels, which is what is in J2?

May be I was not clear enough ... "Express Cycle" only needs to be part of your formula ... if and only if you do have to manage "Express Cycle" in your reference pay rates sheet ... otherwise, you can obviously remove it ....

Quote from PETRODATAjames

I know I have a lot of questions, and I am learning very much here. Can the command button on the tab 'Instructions' prompt a question before clearing the sheets? I would like the question to be "Would you like to clear the 'Instructor and Location Payrate data?' If yes, let the macro run as is, if no, can a new macro be added that will not clear the data on the tab 'Instructor and Location Payrate data'

Thanks

Yes ... obviously a Pop-Up Message can be shown with what is called a "Yes-No Message Box" ...

Since the are two areas to be cleared ... the initial question is :

Are you going to ask only ONCE to clear both areas ???

or

Are you going to ask the question TWICE to give the total freedom to the User ???

By the way ... you have not yet confirmed .... that your initial Vlookup question is now fixed ....

Meanwhile ... for illustration purposes ... below your Button has a Yes-No Message Box ... to be tested ...

Private Sub CommandButton1_Click()
Dim answer As Integer
 answer = MsgBox("Would you like to clear the 'Instructor and Location Payrate' data ?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation")
 If answer = vbYes Then
            With ThisWorkbook
                With .Sheets("Instructor Pay Data")
                    .Unprotect "Password"
                    With .Range("A2:V2000")
                        .Locked = False
                        .ClearContents
                     End With
                    .Range("W1:Z2000").Locked = True
                    .Protect "Password"
                 End With
                .Sheets("Instructor and Location Payrate").Range("B2:D2000").ClearContents
            End With

  Else
  ' User Answered No
  ' Insert here ....Another Macro
 End If
End Sub

Glad to hear the Lookup issue is fixed

Thanks for your Thanks

Regarding the suggestion for a Yes-No Message Box in post #13 :

1. Have you tested it ?

2. Do you feel confident enough to amend the example above in order to have the Message Box appearing Twice ... ?

For sure, you know the best way to Learn is ... through practice

What about ...

Private Sub CommandButton1_Click()
Dim answer As Integer
' First Question to the User
 answer = MsgBox("Would you like to clear the ' Instructor Pay ' data ?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation")
    If answer = vbYes Then
       With ThisWorkbook
           With .Sheets("Instructor Pay Data")
               .Unprotect "Password"
               With .Range("A2:V2000")
                   .Locked = False
                   .ClearContents
                End With
               .Range("W1:Z2000").Locked = True
               .Protect "Password"
            End With
       End With
    Else
     ' User Answered No to the First Question
     ' If need be, Insert here ....Another Macro
    End If
' Second Question to the User
 answer = MsgBox("Would you like to clear the 'Instructor and Location Payrate' data ?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation")
    If answer = vbYes Then
       ThisWorkbook.Sheets("Instructor and Location Payrate").Range("B2:D2000").ClearContents
    Else
     ' User Answered No to the Second Question
     ' If need be, Insert here ....Another Macro
    End If
End Sub

Hope this will help

Once you have tested the macro, feel free to share your comments

Thanks a lot for your Thanks AND for the Like