My Sanity: Zeros being removed when code run?

[Mr Denove]

Please help before the long sleeved jacket comes. A user updates a file and it shows a timestamp of 2022 and the zeros are removed from part of the file.
There is code to remove zeros but only in one column which is far removed from where the data goes missing.
I did get them to insert =Now() into a blank excel and it showed the correct date (same as in the timestamp).

If i run the update everything is fine and correct.

Ive never seen this before, can anyone please explain why the other person is experiencing this types of errors?
Thank you in advance.
Mr D

 

[Joe4]

I think this is a request where we definitely need to see more details, i.e.
1. The code that is being run
2. A sample of the data it is being run against (note that you can post screen images using the tool mentioned here: XL2BB - Excel Range to BBCode)

 

[Mr Denove]

Further to the reply here is the code that is run.

Sub Dependency_Updates()

'Imports the Status of the Dependent Asset
'Imports the Stage if still on a Tracker

Dim WB2 As Workbook 'Dependency Status.xlsm
Dim WS2 As Worksheet 'Sheets("Data")
Dim WS3 As Worksheet 'Sheets("Sign Off Vested")
Dim WB1 As Workbook 'This Workbook
Dim WS1 As Worksheet 'Legal Status
Dim CRge As Range
Dim PRge As Range
Dim VRge As Range
Dim Ret2 As Workbook

Application.ScreenUpdating = False
     
Application.DisplayAlerts = False

Application.DisplayStatusBar = False

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Application.AskToUpdateLinks = False


    Set WB1 = ThisWorkbook
    
    
 On Error Resume Next
Set WB2 = Workbooks("Dependency Status.xlsm")

If Err <> 0 Then
    On Error GoTo 0
    
    Set WB2 = Workbooks.Open(Filename:="File Path Removed", _
    ReadOnly:=True)
    
End If
    'Dependendcy Status Worksheets
    Set WS3 = WB2.Sheets("Sign Off Vested")
    Set WS2 = WB2.Sheets("Data")
    
    'Tracker
    Set WS1 = WB1.Sheets("Tracker")
    
    
        ThisWorkbook.Activate
    With Selection
    Sheets("Tracker").Unprotect "vesting"
        If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
        Set myrange = WS1.Range("A8")
        LastRow = WS1.Range("A1").CurrentRegion.Rows.Count
        

    End If
    
    End With
    
 '*********
 'CREATE VLOOKUP
 '*********
 Dim Vrange As Range
 Dim VLastRow As Long
 VLastRow = WS2.UsedRange.Rows(WS2.UsedRange.Rows.Count).Row
 Set Vrange = WS2.Range("A2:A" & VLastRow)
 

 
A = "'[Dependency Updates.xlsm]Data'!$A$2:$I$"
B = A & VLastRow
C = "=IFERROR(Vlookup(" & "AM8," & B & ",3,FALSE" & "),"""")"
D = "=IFERROR(Vlookup(" & "AM8," & B & ",2,FALSE" & "),"""")"
 Dim Vrow As Long
 Vrow = WS1.Range("A1").CurrentRegion.Rows.Count - 1
 WS1.Range("AN8:AN" & LastRow) = C
 WS1.Range("AO8:AO" & LastRow) = D
 Application.Calculation = xlCalculationAutomatic

 WS1.Range("AN8:AO" & Vrow).Calculate
 WS1.Range("AN8:AN" & Vrow).Value = WS1.Range("AN8:AN" & Vrow).Value
 
Sheets("Tracker").Range("AM5").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
Sheets("Tracker").Range("AM5").Value = Sheets("Tracker").Range("AM5").Value

Application.Calculation = xlCalculationAutomatic

Sheets("Tracker").Range("AN8:AO" & LastRow).Value = Sheets("Tracker").Range("AN8:AO" & LastRow).Value
Sheets("Tracker").Range("AN8:AO" & LastRow).Replace _
What:="0", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True


WB2.Close Savechanges:=False
        
Application.ScreenUpdating = True
     
Application.DisplayAlerts = True

Application.DisplayStatusBar = True

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

Application.AskToUpdateLinks = True

Call ALL_ResetA

MsgBox "Dependency Data Imported"
        
 End Sub

 

[Mr Denove]

This is an example of what has happened to dates, which seems to be quite random. Anything else containing a zero the zero has disappeared.

Ive re-built and re-run the codes without any error and awaiting the other user to come online to run and check what happens to them.

 

[Joe4]

Your code is telling it to replace all zeroes with nothing in this part of the code here:

Sheets("Tracker").Range("AN8:AO" & LastRow).Replace _
What:="0", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True

 

[Mr Denove]

Yes I am needing the zeros removed from the 2 specified columns after the vlookup has run and just leave blanks.

 

[Joe4]

But the way you have written it, it will replace ANY zero it finds in ANY part of the entry.
If you want it to only replace on cells that are EXACTLY equal to zero, then you need to add an extra argument to that function, i.e.
Sheets("Tracker").Range("AN8:AO" & LastRow).Replace _
What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True

 

[Mr Denove]

Yes but the vlookup will only return a zero if the result is blank from the source data hence I only want to remove them as they are not required. But they are only replaced in these 2 columns by the code. Its the full dataset thats been affected by this issue which isnt looked at in that code (and 24 other users using the same type of file set up the same way arent experiencing this issue).
May be coincidence but there was a software update done to the users laptop shortly before any of this happened.

 

[Joe4]

The data you posted, which columns are they?
The zeroes that are being erroneously removed, what column is that happening in?

Note that if you exclude the argument in red from the Replace functionality, it may just use whatever was the last one used, which could be different for each user.
Those sort of things you should explicitly tell it what to do, and not leave it to chance.
Did you at least try that change and see if it makes any difference for the user having issues?

 

[Fluff]

If you add this line as shown

Sheets("Tracker").Range("A1").Find "*"
Sheets("Tracker").Range("AN8:AO" & LastRow).Replace _
What:="0", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True

Does that get rid of the problem?