Bonus Value Update

[Gunasekaran]

Hello Team,

I need assistance with a VBA code that involves dynamic logic. I have a source data sheet and a working sheet (WS). The goal is to update the Bonus Number based on the Month of Report indicated in column A. If the report is for the 1st of June, the entire month should be considered, not just specific dates.(1st of June)

The Bonus Chart shows a 3-term Bonus for the month of June in the Prorata_Table. According to the code, I need to update the value of 3 for all employees. However, for the current year's June, the value should be 1, as these employees are still in June. For May Month employees, the value should be 2, and for June and later, the default value is 3.

I have already written some code, but I am having trouble with the logic. Please note that I am only checking columns A and E. Can you please assist me with this?

Sub QB_Validation()
    Dim des As Worksheet
    Dim ptoa As Worksheet
    Dim LastRowInOT As Long
    Dim LastRowInPTO As Long
    Dim cell As Range
    Dim dateValue As Date
    Set des = ThisWorkbook.Sheets("Ws")
    Dim joinDate As Date
    Dim monthDays As Long
    Dim daysFromJoining As Long
    Dim AWS As Double
    Dim thresholdDate As Date
    
    ' Initialize factors for each month
    Dim MonthFactors(1 To 12) As Integer
    MonthFactors(1) = 4 ' January
    MonthFactors(2) = 2 ' February
    MonthFactors(3) = 3 ' March
    MonthFactors(4) = 4 ' April
    MonthFactors(5) = 2 ' May
    MonthFactors(6) = 3 ' June
    MonthFactors(7) = 4 ' July
    MonthFactors(8) = 2 ' August
    MonthFactors(9) = 3 ' September
    MonthFactors(10) = 4 ' October
    MonthFactors(11) = 2 ' November
    MonthFactors(12) = 3 ' December

    LastRow = des.Cells(des.Rows.Count, "A").End(xlUp).Row
    LastCol = des.Cells(1, des.Columns.Count).End(xlToLeft).Column

    des.Cells(1, LastCol + 1).Value = "Month"
    des.Cells(1, LastCol + 2).Value = "EPF ER Ratio"
    des.Cells(1, LastCol + 3).Value = "QB %"
    des.Cells(1, LastCol + 4).Value = "Bonus Calc"
    des.Cells(1, LastCol + 5).Value = "EPF Calc"
    des.Cells(1, LastCol + 6).Value = "Total"

    For x = 2 To LastRow ' Assuming row 1 has headers
        reportDate = des.Cells(x, 1).Value
        joinDate = des.Cells(x, 5).Value
        reportYear = Year(reportDate)
        joinYear = Year(joinDate)

        ' Check if the report year matches the join year
        If reportYear <> joinYear Then
            
            des.Cells(x, LastCol + 1).Value = MonthFactors(Month(reportDate))
        Else
            
            monthsDifference = Abs(Month(reportDate) - Month(joinDate))
            
            If monthsDifference = 0 Then
                
                des.Cells(x, LastCol + 1).Value = 1
            Else
                
                des.Cells(x, LastCol + 1).Value = MonthFactors(Month(reportDate) - monthsDifference + 1)
            End If
        End If
    Next x
End Sub

 

[BobBridges]

There's a part of this I'm not following:

I have a source data sheet and a working sheet (WS).

And some other sheets too.

...update the Bonus Number...

You didn't say, but the only thing I see that looks like it might be a bonus number is col C of "Quarterly Bonus Chart"?

If the report is for the 1st of June...

Which it is, throughout both the Source and WS worksheets

According to the code, I need to update the value of 3 for all employees....

Not sure what you mean. The value of 3 will always be 3. You mean you want to update the value of some cell or cells. But which ones?

...for the current year's June, the value should be 1, as these employees are still in June.

In what sense are employees still "in" June, especially now that it's October? Oops, I mean November?