kiltro
03-27-2018, 08:40 AM
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...
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...