Snasui Excel Forum

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
 

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    i = 1
    Application.EnableEvents = False
    For i = 9 To 39
        If Range("g" & i).Value = "" Then
            Range("i" & i).Value = ""
            Range("j" & i).Value = ""
        End If
        If Target.Address = Range("$G$" & i).Address Then
            Dim lot As String
            lot = Range("$G$" & i + 1).Value
            Dim x As String
            x = Range("$G$" & i).Value
            Call ShowMFGDate(lot)
            Call ExpDate(lot)
        End If
        'Fuction MFG
        If Target.Address = Range("$G$" & i).Address Then
            If UCase(Target.Value) = UCase(Range("I" & i).Value) Then
                Range("$G$" & i).Value = lot
                ElseIf Range("$G$" & i + 1) = "" Then
                Range("$I$" & i + 1).Value = ""
            End If
        End If
        'Fuction EXP
        If Target.Address = Range("$G$" & i).Address Then
            If UCase(Target.Value) = UCase(Range("J" & i).Value) Then
                Range("$G$" & i).Value = lot
            ElseIf Range("$G$" & i + 1) = "" Then
                Range("$J$" & i + 1).Value = ""
            End If
        End If
    Next i
    Application.EnableEvents = True
End Sub