Keep source formatting.

[Harshil Mehta]

The below code works completely fine. I just want the code to retain the source formatting. For example: my source data also contains numbers which are stored as text, however the below code coverts them into numbers in the output. I don't want the code to convert them instead retain the source formatting.

Could anyone please help me solve this?

Sub test_1()
       Dim Ary As Variant, Nary As Variant
       Dim r As Long, c As Long, nr As Long
       
       With Sheet7
          c = .Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
          Ary = .Range("A11", .Range("A" & Rows.Count).End(xlUp)).Resize(, c).Value2
       End With
       ReDim Nary(1 To UBound(Ary), 1 To c - 1)
       For r = 1 To UBound(Ary)
          If LCase(Ary(r, 1)) = "fine" Then
             nr = nr + 1
             For c = 2 To UBound(Ary, 2)
                Nary(nr, c - 1) = Ary(r, c)
             Next c
          End If
       Next r
       Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Nary, 2)).Value = Nary
    End Sub

 

[AlphaFrog]

Try this...

    With Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Nary, 2))
        .NumberFormat = "@"    'Text format
        .Value = Nary
    End With

 

[Harshil Mehta]

Try this...

    With Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Nary, 2))
        .NumberFormat = "@"    'Text format
        .Value = Nary
    End With

Works super fine. Thanks for the quick response.