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

21937

[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!!