Excel Forum

Code: Select all

IF(RC[+2]=""ลาออก"",""ลาออก""

Code: Select all

Sub Calculate()
Dim InMonth As Byte

If DataBaseName = "" Then
    MsgBox "ยังไม่ได้เลือกไฟล์ Database กรุณาเลือกก่อนคำนวณ"
    Exit Sub
End If

InMonth = Application.InputBox("ป้อนลำดับเดือนที่ต้องการคำนวณ  (1 - 12 เท่านั้น) :", "ป้อนลำดับเดือน", Type:=1)

If InMonth > 0 And InMonth <= 12 Then
    GoTo StartCal
Else
    MsgBox "ป้อนเดือนผิด"
    Exit Sub
End If
StartCal:
Workbooks(DataBaseName).Worksheets("Cover").Activate
MonthR = Range("H7")
YearR = Range("J7")
YearOff = Range("J5")

If PayMent = PM(3) Then
    PayMent = ""
Else
    PayMent = PayMent
End If

Workbooks(TemplateWB).Worksheets("Mapping").Activate

For i = 3 To 14
    MonthT(i) = Range("H" & i)
Next i

For i = 3 To 14
    If MonthR = MonthT(i) Then
    MonthR = i - 2
    End If
Next i

Workbooks(DataBaseName).Worksheets("Database").Activate
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LastRow + 1) = Name2
Range("C" & LastRow + 1) = LastName
Range("D" & LastRow + 1) = MooNo
Range("E" & LastRow + 1) = BaanNum
Range("F" & LastRow + 1) = DayNo
Range("H" & LastRow + 1) = MonthNo
Range("J" & LastRow + 1) = YearNo
Range("K" & LastRow + 1) = Note
Range("L" & LastRow + 1) = PayMent
Range("R" & LastRow + 1) = MooBaan
Range("G" & LastRow + 1) = "/"
Range("I" & LastRow + 1) = "/"
Range("S" & LastRow + 1) = IDNO
Range("A2") = 1
FM = "=R[-1]C+1"
Range("A3:A" & LastRow + 1).Formula = FM

'FAge = "=RC[1]-RC[-3]+RC[7]"
'Range("M2:M" & LastRow + 1).Formula = FAge

First60 = "=DATE(RC[-6]-543+60,RC[-8]+1,RC[-10]-1)"
Range("P2:P" & LastRow + 1).Formula = First60

'Set ตัวแปร "&MonthR&" Month Report, YearR = Year Report, YearOff = ปีงบ

Times60 = "=IF(" & MonthR & "<10,IF(OR(RC[-9]<10,AND(RC[-9]=10,RC[-11]=1),AND(RC[-9]=12,RC[-11]<>1))," & YearR & "-YEAR(RC[-1])-543+1," & YearR & "-YEAR(RC[-1])-543),IF(OR(RC[-9]<10,AND(RC[-9]=10,RC[-11]=1),AND(RC[-9]=12,RC[-11]<>1))," & YearR & "-YEAR(RC[-1])-543+1," & YearR & "-YEAR(RC[-1])-543)+1)"
Range("Q2:Q" & LastRow + 1).Formula = Times60



FY = "=IF(RC[-6]>=10,YEAR(TODAY())-2,YEAR(TODAY())-1)+543"
Range("N2:N" & LastRow + 1).Formula = FY



FAgeN = "=IF(RC[+2]<70,""D"", " & _
        "IF(RC[+2]<80,""C"", " & _
        "IF(RC[+2]<90,""B"", " & _
        "IF(RC[+2]<120,""A"", " & _
        "IF(RC[+2]=""ลาออก"",""ลาออก"")))))"
Range("K2:K" & LastRow + 1).FormulaR1C1 = FAgeN

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
        
MsgBox "คำนวณเรียบร้อยแล้ว"
    
Workbooks(DataBaseName).Worksheets("Database").Activate

Range("B" & LastRow + 1).Select

End Sub