Cell Values Disappear After "End With" Is Executed-CodePudding

I'm facing a weird dilemma where the cell values disappear as soon as the code hits End With line. (As pointed out by Spinner, it's the .Value line making the values disappear).

I have a complex structure of code execution, and the same code to input cell values is already being used elsewhere in the code correctly with minor difference. This one is working under Nested For/If Loops and the column names and value reference is changed as per the requirement.

Can't seem to figure out the issue. Check Column X in the GIF below.

With Processed
Processed.Activate
Processed.AutoFilterMode = False
Processed.ShowAllData
Range("J:J").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("J:J").Value = Range("J:J").Value
Range("K:K").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("K:K").Value = Range("K:K").Value
Range("L:L").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("L:L").Value = Range("L:L").Value
Range("M:M").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("M:M").Value = Range("M:M").Value
Range("O:O").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("O:O").Value = Range("O:O").Value
Range("P:P").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("P:P").Value = Range("P:P").Value
Cells.Interior.ColorIndex = xlColorIndexNone
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Cells.EntireColumn.AutoFit
Range("J1").Value = "DATE 1"
Range("K1").Value = "DATE 2"
Range("M1").Value = "DATE 3"
Range("N1").Value = "DAY 1"
Range("Q1").Value = "DAY 2"
Range("R1").Value = "BUSINESS UNIT"
Range("S1").Value = "DC TIME DIFFERENCE"
Range("T1").Value = "EXCLUDING FREEZING TIME (SAT-THU)"
Range("U1").Value = "EXCLUDING FREEZING TIME (FRI)"
Range("V1").Value = "EXCLUDING FREEZING TIME (OVERALL)"
Range("W1").Value = "M-CUST TIME"
Range("X1").Value = "M-CUST TIME EXCLUDING FREEZING TIME (SAT-THU)"
Range("Y1").Value = "M-CUST TIME EXCLUDING FREEZING TIME (FRI)"
Range("Z1").Value = "M-CUST TIME EXCLUDING FREEZING TIME (OVERALL)"
Cells.EntireColumn.AutoFit
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
Set Tbl = .ListObjects.Add(xlSrcRange, TblRng, xlYes)
Tbl.TableStyle = "TableStyleMedium2"
LastRow = Processed.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
For Each i In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    If Range("E" & i.Row).Value = "M-CUST" Then
        InciNum = Range("A" & i.Row).Value
        TaskNum = Range("F" & i.Row).Value
        Range("A1").AutoFilter Field:=1, Criteria1:=InciNum, Operator:=xlFilterValues
        For Each j In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
            If TaskNum < Range("F" & j.Row).Value And Range("E" & j.Row).Value <> "Cancelled" Then
                Range("M" & j.Row).Value = Range("L" & j.Row).Value   1
            End If
        Next j
    End If
    Next i
Processed.ShowAllData

For Each i In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    StartDate = Range("L" & i.Row).Value
    EndDate = Range("P" & i.Row).Value
    Range("S" & i.Row).Value = DateDiff("n", StartDate, EndDate)
    Range("N" & i.Row).Value = Format(StartDate, "dddd")
    Range("Q" & i.Row).Value = Format(EndDate, "dddd")

    If Range("S" & i.Row).Value < 0 Then
        Range("S" & i.Row).Value = 0
    End If
Next i

For Each i In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    If Range("E" & i.Row).Value = "M-CUST" Then
        StartDate = Range("P" & i.Row).Value
        InciNum = Range("A" & i.Row).Value
        TaskNum = Range("F" & i.Row).Value
        Range("A1").AutoFilter Field:=1, Criteria1:=InciNum, Operator:=xlFilterValues
        For Each j In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
            If TaskNum < Range("F" & j.Row).Value And Range("E" & j.Row).Value <> "Cancelled" Then
                EndDate = Range("O" & j.Row).Value
                Range("W" & i.Row).Value = DateDiff("n", StartDate, EndDate)
                If .Range("R2").Value Like "B2B" Then
                    TimeUpr2 = TimeSerial(22, 0, 0)
                Else
                    TimeUpr2 = TimeSerial(23, 0, 0)
                End If

                With .Range("X2")
                    sDateIni = Range("P" & i.Row).Address(0, 1, xlR1C1, False, .Cells)
                    sDateEnd = Range("O" & j.Row).Address(0, 1, xlR1C1, False, .Cells)
                    sFmlHours = kFmlHours
                    sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
                    sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
                    sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
                    sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
                End With
                With .Range("X2:X" & LastRow)
                    .FormulaR1C1 = sFmlHours    'Enter formula
                    .Value = .Value             'Replace Formula with Value
                End With
                With .Range("Y2")
                    sDateIni = Range("P" & i.Row).Address(0, 1, xlR1C1, False, .Cells)
                    sDateEnd = Range("O" & j.Row).Address(0, 1, xlR1C1, False, .Cells)
                    sFmlHours = kFmlHours2
                    sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
                    sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
                    sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr2)
                    sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr2)
                End With
                With .Range("Y2:Y" & LastRow)
                    .FormulaR1C1 = sFmlHours    'Enter formula
                    .Value = .Value             'Replace Formula with Value
                End With
                For Each ii In Range("Z2:Z" & LastRow).SpecialCells(xlCellTypeVisible)
                    .Range("Z" & i.Row).Value = .Range("X" & i.Row).Value   .Range("Y" & i.Row).Value
                Next ii
            End If
        Next j
        End If
    Next i
Processed.ShowAllData

If .Range("R2").Value Like "B2B" Then
    TimeUpr2 = TimeSerial(22, 0, 0)
Else
    TimeUpr2 = TimeSerial(23, 0, 0)
End If

With .Range("T2")
        sDateIni = Range("L2").Address(0, 1, xlR1C1, False, .Cells)
        sDateEnd = Range("P2").Address(0, 1, xlR1C1, False, .Cells)
        sFmlHours = kFmlHours
        sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
        sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
        sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
        sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
End With
With .Range("T2:T" & LastRow)
        .FormulaR1C1 = sFmlHours    'Enter formula
        .Value = .Value             'Replace Formula with Value
End With
With .Range("U2")
        sDateIni = Range("L2").Address(0, 1, xlR1C1, False, .Cells)
        sDateEnd = Range("P2").Address(0, 1, xlR1C1, False, .Cells)
        sFmlHours = kFmlHours2
        sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
        sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
        sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr2)
        sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr2)
End With
With .Range("U2:U" & LastRow)
        .FormulaR1C1 = sFmlHours    'Enter formula
        .Value = .Value             'Replace Formula with Value
End With
For Each i In Range("V2:V" & LastRow).SpecialCells(xlCellTypeVisible)
    .Range("V" & i.Row).Value = .Range("T" & i.Row).Value   .Range("U" & i.Row).Value
Next i
Range("S:S").NumberFormat = "General"
Range("T:T").NumberFormat = "General"
Range("U:U").NumberFormat = "General"
Range("V:V").NumberFormat = "General"

Cells.EntireColumn.AutoFit End With Application.ScreenUpdating = True End Sub

CodePudding user response：

Man, but that gif approach is eye watering. :)
Still, managed to figure out a basic interpretation problem:
o The values disappear on execution of the line above the End With
o i.e. it's when you step to the End With (not when that line is executed)
o That means .Value = .Value is what's clearing the cells (not the End With)

And that of course makes sense, as End With does nothing other than just that.

Can't figure out why the particular data in the 'view' provided is cleared.
But, as Toddleson says, not being able to directly test the code makes working that out nigh on impossible.

CodePudding user response：

I would suggest dumping the data to a text file on each iteration of the i/j loop for further examination.

With .Range("X2:X" & LastRow)
        .FormulaR1C1 = sFmlHours    'Enter formula
         dump i, j, .Offset() '<--- add line here
        .Value = .Value             'Replace Formula with Value
End With

Sub dump(i, j, rng As Range)
    Dim fso, ts, c, s As String
    s = Format(Now, "HHMMSS") & "_i" & i & "_j" & j & ".txt"
    Set fso = CreateObject("Scripting.FilesystemObject")
    Set ts = fso.createTextFile("dump_" & s)
    With ts
        .writeline "Range= " & vbTab & rng.Address(External:=True)
        .writeline "i= " & i & vbTab & "j= " & j
        For Each c In rng
            .writeline c.Address & vbTab & c.Value2 & vbTab & c.FormulaR1C1
        Next
        .Close
    End With
End Sub

CodePudding user response：

I think that the problem will go away when you edit the code. I would suggest at least the following steps:

correct indenting to have an overview what is referenced by with (e.g. after the first with there are no indents - i assume that's why you didn't copy it the first time)
use clear variable names, e.g. from looking at the code it is not clear (at one glance) that processed is a sheet, I suggest using wsProcessed
get rid of all implicit range references - make them all explicit by referencing the according sheet.
use sub routines for repeating code parts with clear names describing the task of the routine and well-named parameters
maybe configuration/mapping arrays could help also to get shorter code - e.g. one for the formatting per column, one for the caption etc.
maybe it would help to have an enum that defines the columns - so that you reference the column by "speaking" enums not by an abstract "A", "B", like this:

' at the top of the module

private enum colSheetXXX
   cXXX_Date1 = 1   '= column A
   cXXX_Date2 = 2   '= column B
   'proceed like this with all columns
   cXXX_BusinessUnit = 
end enum

Then you can address a column like this: ws.Columns(cXXX_BusinessUnit)

And if, at any time, the order of the columns changes or new columns have to be inserted you just have to do add them to the enum or change the order of the enum - without looking into the rest of the code :-)

If the mysterious empty cell still is there after refactoring it should be much easier to find the source of the error.

Hoping this is not too "Oberlehrerhaft" (know-all) as we say in Germany