สอบถาม การวนค่าเเล้วำม่เป็นค่าว่างใน column excel ทำยังไงคะ

Sub Worksheet_Change(ByVal Target As Range)

 Dim i As Integer
 i = 1
 For i = 9 To 500

 If Target.Address = Range("$G$" & i).Address Then

Dim lot As String

lot = Range("$G$" & i).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)

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
           
        End If
    End If
    
         Next i
         
End Sub

Function ConvertDate(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(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(sTmp)
        
    End Select
    
    nday = CInt(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 500
    
   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 500
    
    a = Range("G9" & i).Value
    getLotabc = a
    
    Next i
    
End Function