How to retain cell format while using VBA?

[rizwanulhasan]

Hi all,

Hope all are well.

In the attached file, I am using macro "Analysis" which is not concerned with columns S and U. Despite that, the macro changes the cell format to scientific. Consider these columns in row 13, 14 for example.

Can someone please help with the correction of code? Thanks in advance.

Pass: EUFMDAlerts2021

[BadlySpelledBuoy]

One way would be to format those columns as text in your code.

Sub Analysis()
Dim Data, Dict As Object, Err As String, i As Long, srow As Long, res
Data = Sheets("Links").Cells(1).CurrentRegion
Set Dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Data)
    Err = Data(i, 1) & Data(i, 2) & Data(i, 3)
    Dict.Item(Err) = Array(Data(i, 4), Data(i, 5), Data(i, 6))
Next i
With Sheets("All Data Summary - SouthernEuro")
    .Unprotect "EUFMDAlerts2021"
    .Columns("S:U").NumberFormat = "@"
    With .Cells(1).CurrentRegion
        Data = .Value
        res = Application.Match("Miscellaneous", .Columns(32), 0)
        If IsError(res) Then Exit Sub                                 ' No MISCELLANEOUS so exit sub
        srow = res
        For i = srow To UBound(Data)
            If Data(i, 32) = "Miscellaneous" Then    '  <<<<<<  If all contiguous rows are "Miscellaneuos" then this test is redundant
                Err = Data(i, 12) & Data(i, 38) & Data(i, 42)
                If Dict.exists(Err) Then
                    Data(i, 32) = Dict(Err)(0)
                    Data(i, 33) = Data(i, 1)
                    Data(i, 35) = Dict(Err)(1)
                    Data(i, 37) = Dict(Err)(2)
                End If
            End If
        Next i
        .Value = Data
        .Columns(33).NumberFormat = "dd-mm-yyyy"
    End With
    .Protect "EUFMDAlerts2021"
End With
End Sub

BSB

[rizwanulhasan]

@BadlySpelledBuoy:

It works. Thanks a bunch