Code: Select all
Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = 1
For i = 9 To 39
If Target.Address = Range("$G$" & i).Address Then
Dim lot As String
lot = Range("$G$" & i + 1).Value
'If Len(lot) <= 0 Then
'Exit Sub
'End If
'Dim a As String
Dim x As String
'a = getLotabc(x)
x = Range("$G$" & i).Value
Call ShowMFGDate(lot)
'>>>Bee
Call ExpDate(lot)
'<<<Bee
End If
If Target.Address = Range("$G$" & i).Address Then
If UCase(Target.Value) = UCase(Range("I" & i).Value) Then
Range("$G$" & i).Value = lot
'>>>test
ElseIf Range("$G$" & i + 1) = "" Then
Range("$I$" & i + 1).Value = ""
'<<<test
End If
End If
'-------------Exp-------------'
If Target.Address = Range("$G$" & i).Address Then
If UCase(Target.Value) = UCase(Range("J" & i).Value) Then
Range("$G$" & i).Value = lot
'>>>test
ElseIf Range("$G$" & i + 1) = "" Then
Range("$J$" & i + 1).Value = ""
'<<<test
End If
End If
'--------------Exp------------'
Next i
End Sub
Function ConvertDate(lot As String) As Date
Dim nyear As Integer
Dim nmonth As String
Dim nday As Integer
Dim sTmp As String
sTmp = Mid(CStr(VBA.Year(Now())), 1, 3)
'nyear = CInt(sTmp & Mid(lot, 1, 1))
nyear = CInt("0" & sTmp & Mid(lot, 1, 1))
sTmp = Mid(UCase(lot), 2, 1)
Select Case sTmp
Case "X"
nmonth = 10
Case "Y"
nmonth = 11
Case "Z"
nmonth = 12
Case Else
nmonth = CInt("0" & sTmp)
End Select
nday = CInt("0" & Mid(lot, 3, 2))
Dim dt As Date
dt = DateSerial(nyear, nmonth, nday)
ConvertDate = dt
End Function
Sub ShowMFGDate(lot As String)
Dim showlot As String
Dim i As Integer
i = 1
For i = 9 To 39
showlot = Range("G" & i).Value
If UCase(showlot) = "No" Then
Range("G" & i).Value = showlot
Exit Sub
End If
Dim dt As Date
dt = ConvertDate(showlot)
'Range("I" & i).Value = dt
'dt = Range("I" & i)
lot = Range("I" & i)
Range("I" & i).Value = Format(dt, "dd-MMM-YY")
Next i
End Sub
Function getLotabc(x As String)
Dim a As String
Dim i As Integer
i = 1
For i = 9 To 39
a = Range("$G$" & i).Value
getLotabc = a
Next i
End Function
'--------------------Exp-----------------------
Function ConvertDateExp(lot As String) As Date
Dim nyear As Integer
Dim nmonth As Integer
Dim nday As Integer
Dim sTmp As String
sTmp = Mid(CStr(VBA.Year(Now())), 1, 3)
nyear = CInt("0" & sTmp & Mid(lot, 1, 1))
sTmp = Mid(UCase(lot), 2, 1)
Select Case sTmp
Case "X"
nmonth = 10
Case "Y"
nmonth = 11
Case "Z"
nmonth = 12
Case Else
nmonth = CInt("0" & sTmp)
End Select
Dim DDy As String
DDy = Mid(CStr(VBA.Year(Now())), 3, 2)
'nmonth = CInt("0" & sTmp & Mid(lot, 1, 1))
'===================Date===================
'nday = Mid("0" & lot, 3, 2)
nday = CInt("0" & Mid(lot, 3, 2))
If nday = "00" Then
nday = "00"
DDy = "00"
ElseIf nday <> "01" Then
If CInt(nday) > 1 And CInt(nday) < 11 Then
DDy = "0" & CStr(CInt(nday) - 1)
Else
DDy = CStr(CInt(nday) - 1)
End If
ElseIf nday = "01" Then
'===================Month===================
If nmonth <> "01" Then
If CInt(nmonth) > 1 And CInt(nmonth) < 11 Then
nmonth = "0" & CStr(CInt(nmonth) - 1)
Else
nmonth = CStr(CInt(nmonth) - 1)
End If
ElseIf nmonth = "01" Then
nmonth = "12"
ye = CStr(CInt(nyear) - 1)
End If
'===================Date===================
Select Case CInt(nmonth)
Case 1, 3, 5, 7, 8, 10, 12
DDy = "31"
Case 4, 6, 9, 11
DDy = "30"
Case 2
If CInt(nyear) Mod 4 = 0 Then
DDy = "29"
ElseIf CInt(nyear) Mod 4 <> 0 Then
DDy = "28"
End If
End Select
End If
'===================Date===================
If CInt(nmonth) = 2 And CInt(DDy) > 28 Then
If CInt(nyear) Mod 4 = 0 Then
DDy = "29"
ElseIf CInt(nyear) Mod 4 <> 0 Then
DDy = "28"
End If
End If
nday = CInt("0" & Mid(lot, 3, 2))
If nmonth = 8 And DDy > 28 Then
If CInt(nyear) Mod 4 = 0 Then
DDy = "29"
ElseIf CInt(nyear) Mod 4 <> 0 Then
DDy = "28"
End If
End If
Dim dt As Date
dt = DateSerial(nyear + 1, nmonth - 6, DDy)
ConvertDateExp = dt
End Function
Sub ExpDate(lot As String)
Dim i As Integer
i = 1
For i = 9 To 39
Dim showlot As String
showlot = Range("$G$" & i).Value
If UCase(showlot) = "NO" Then
Range("$G$" & i).Value = showlot
Exit Sub
End If
Dim dt As Date
dt = ConvertDateExp(showlot)
lot = Range("$J$" & i)
Range("$J$" & i).Value = Format(dt, "dd-MMM-YY")
Next i
End Sub
Function getLotabc1(x As String)
Dim a As String
Dim i As Integer
i = 1
For i = 9 To 39
a = Range("$G$" & i).Value
getLotabc = a
Next i
End Function
You do not have the required permissions to view the files attached to this post.
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
ไฟล์ที่จะแนบ Code ได้ต้องมีนามสกุลเป็น .xlsm เช่นนี้เป็นต้น ลองแนบมาใหม่ครับ
