getting #N/D on function.transpose

[kiltro]

I've a macro to ad 0s in front of number, columns are set as text.
Basically I need every string to be composed by 5 numbers, so if for example a cell contains three digit I've to ass "00" in front of it

Sub CorreggiCAP()

CAP = Worksheets("Foglio4").Range("E1:E85071").Value
Dim str As String
Dim num As Integer
Dim corr(1 To 85071) As String


For j = 1 To 85071
    str = CAP(j, 1)
    num = Len(str)
    If num = 5 Then
        corr(j) = CAP(j, 1)
        GoTo EndLoop
        Else
            If num = 4 Then
            corr(j) = "0" & CAP(j, 1)
            Else
                If num = 3 Then
                corr(j) = "00" & CAP(j, 1)
                Else
                    If num = 2 Then
                    corr(j) = "000" & CAP(j, 1)
                    End If
                End If
            End If
        End If
EndLoop: Next j


Worksheets("Foglio4").Range("F1:F85071") = WorksheetFunction.Transpose(corr)


End Sub

All works fine till cell F19536 where it starts to place #N/D in every cell.
In the local variable windows the values for variable corr are fine thought...

[Bob Phillips]

Try this alternative

Sub CorreggiCAP()

    With Worksheets("Foglio4")
    
        .Columns("G").Insert
        
        With .Range("F1:F85071")
        
            .Offset(0, 1).Formula = "=RIGHT(""00000""&F1,5)"
            .Value = .Offset(0, 1).Value
        End With
        
        .Columns("G").Delete
    End With
End Sub

[kiltro]

Doesn't work... I get this

Schermata 2018-03-28 alle 08.25.12.png

[Bob Phillips]

Can you post your workbook?

[Bob Phillips]

I missed that you were adding a column, so try this which doesn't add and remove a helper column, and may address your formula issue

Sub CorreggiCAP()

    Application.ScreenUpdating = False
    
    With Worksheets("Foglio4")
        
        With .Range("F1:F85071")
        
            .NumberFormat = "General"
            .Formula = "=IF(E1="""","""",RIGHT(""00000""&E1,5))"
        End With
    End With

    Application.ScreenUpdating = True
End Sub

[kiltro]

I give you dropbox link because it's greater than 1MB

https://www.dropbox.com/s/9cfu8gl411wrlpe/CAP.xlsm?dl=0

[Bob Phillips]

I can't get at it, I keep getting a message that Dropbox is not responding.

Did you try my follow-up attempt?

[snb]

application.Transpose is limited.

Use

Sub M_snb()
   sheets("Foglio4").Range("E1:E85071").numberformat="00000"
End Sub

[kiltro]

I can't get at it, I keep getting a message that Dropbox is not responding.

Did you try my follow-up attempt?

Sure, it works
Thanks!!