Equal number and date being treated as different when comparing them

[Raghav Chamadiya]

Hello and thank you for any help you can provide. So, I have a userform I am using to edit a main database, It looks like this:

I am using Vlookup to fetch details in the userform when a serial number is input. The code is as follows:

Private Sub txtSerial_AfterUpdate()
Application.ScreenUpdating = False
Application.AutomationSecurity = msoAutomationSecurityLow
Dim nwb As Workbook
Dim sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set nwb = Workbooks.Open("Online sharepoint location")
Set sh = nwb.Sheets("Summary")
If WorksheetFunction.CountIf(sh.Range("A:A"), EditForm.txtSerial.Value) = 0 Then
    MsgBox "This is an incorrect ID"
    Exit Sub
End If
X = EditForm.txtSerial.Value
With EditForm

    .txtProject = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 3, 0)
    .txtTeam = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 4, 0)
    .txtAPL = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 5, 0)
    .txtAE = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 6, 0)
    .cmbRelease = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 7, 0)
    .cmbDS = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 8, 0)
    .txtBatches = CInt(Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 9, 0))
    .dtReview.Value = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 10, 0)
    .dtSubmission.Value = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 11, 0)
    .dtRelease.Value = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 12, 0)
    .dtPlanned.Value = Format(Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 13, 0), "dd/mm/yyyy")
    .cmbPriority = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 14, 0)
    .txtRemarks = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 15, 0)
    .txtQA = Application.WorksheetFunction.VLookup(CLng(EditForm.txtSerial), sh.Range("A:R"), 17, 0)
   

End With
nwb.Close
End Sub

Then I am also trying to edit the database and track the changes. That code is as follows:

  Sub Edit()
'On Error GoTo eh
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AutomationSecurity = msoAutomationSecurityLow
   
TryAgain:
    Dim nwb As Workbook
 
    Set nwb = Workbooks.Open("Online sharepoint location")
   
    Dim iRow As Long
    iRow = WorksheetFunction.CountA(nwb.Sheets("Audit Trail").Range("A:A")) + 1
   
    nwb.Sheets("Summary").Unprotect Password:="pass"
    nwb.Sheets("Audit Trail").Unprotect Password:="pass"
    Dim id As Range
    Set id = nwb.Sheets("Summary").Range("A:A").Find(what:=EditForm.txtSerial.Value, LookIn:=xlValues)
   
    oldValues = ""
    newValues = ""
    titles = ""
   
    LogChanges id.Offset(, 2), EditForm.txtProject.Value
    LogChanges id.Offset(, 3), EditForm.txtTeam.Value
    LogChanges id.Offset(, 4), EditForm.txtAPL.Value
    LogChanges id.Offset(, 5), EditForm.txtAE.Value
    LogChanges id.Offset(, 6), EditForm.cmbRelease.Value
    LogChanges id.Offset(, 7), EditForm.cmbDS.Value
    LogChanges id.Offset(, 8), EditForm.txtBatches.Value
    LogChanges id.Offset(, 9), EditForm.dtReview.Value
    LogChanges id.Offset(, 10), EditForm.dtSubmission.Value
    LogChanges id.Offset(, 11), EditForm.dtRelease.Value
    LogChanges id.Offset(, 12), EditForm.dtPlanned.Value
    LogChanges id.Offset(, 13), EditForm.cmbPriority.Value
    LogChanges id.Offset(, 14), EditForm.txtRemarks.Value
    LogChanges id.Offset(, 16), EditForm.txtQA.Value
    nwb.Sheets("Summary").Protect Password:="pass"
   

    If Len(titles) > 0 Then
           

            With Worksheets("Audit Trail")
           
                .Cells(iRow, 1) = iRow - 1
                .Cells(iRow, 2) = EditForm.txtSerial.Value
                .Cells(iRow, 3) = titles
                .Cells(iRow, 4) = oldValues
                .Cells(iRow, 5) = newValues
                .Cells(iRow, 6) = frm6.txtJust.Value
                .Cells(iRow, 7) = Application.UserName
                .Cells(iRow, 8) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
               
            End With
           
            nwb.Sheets("Audit Trail").Protect Password:="pass"
            'nwb.Sheets("Audit Trail").Visible xlSheetVeryHidden
           
        End If
        Unload frm6
        'MsgBox (titles)
        'MsgBox ("Changes edited succesfully and recorded in Audit trail sheet")
   
    'nwb.Save
    nwb.SaveAs Filename:="Online Sharepoint location"
    nwb.Close
    MsgBox ("Changes edited succesfully and recorded in Audit trail sheet")
    Unload EditForm
Exit Sub
'eh:
    'Ans = MsgBox("Another user is submitting their entry, please wait for a few seconds and then try again.", vbRetryCancel + vbCritical)
    'If Ans = vbRetry Then Resume TryAgain

End Sub

LogChanges function:

Sub LogChanges(c As Range, vNew)
    With c
        sep = IIf(Len(titles) > 0, "; ", "") 'need a separator?
        If .Value <> vNew Then
            'track the changes
            titles = titles & sep & .Parent.Cells(1, .Column).Value 'column titles in Row1
            oldValues = oldValues & sep & ValueOrBlank(.Value)  'track old value
            newValues = newValues & sep & ValueOrBlank(vNew)    'track new value
            .Value = vNew                                       'update the cell
        End If
    End With
End Sub

Function ValueOrBlank(v)
    ValueOrBlank = IIf(Len(v) > 0, v, "[blank]")
End Function

Everything is working as expected except 2 fields: Number of Batches - Because its a number, and Planned release date because its a textfield date. I have to keep that date in textfield because its not a mandatory field.

Here is a screenshot of the audit trail sheet, and my problem:

In the last row you can see the number of batches and Planned date coming whereas they shouldn't have as I didn't change them
Regarding number of batches I tried putting text there and then it started working properly. So the problem is because it is a number. Please help

 

[mole999]

would this work =TEXT(A1,"mm/dd/yyyy")