My code is not converting some columns to number

[maravig]

Hello again,
I'm ready to pull my hair out. I have a user form where an end user can select a CSV file and then pick a tab from a drop down list and have that file appended to that tab. It works just fine. I have 3 columns that need to be 5 digits so 3 needs to be 00003. I have tried so many different combinations and nothing works. Here is my latest attempt which found elsewhere:

For Each c In Range("B:B")
    If c = "" Then GoTo nextc
    If IsCustom(c) Then
        c.Value = c.Value * 1
        c.NumberFormat = "00000"
    End If
    
nextc:
Next c

I'm willing to try anything.

Jeff

 

[maravig]

well, the number 3 still shows up as 3. I need all the characters in column B (and d and F for that matter) to display 5 numbers with leading zeros.

 

[MARK858]

The number 3 changes to 00003 for me (both if it is a real number and a text value). What is different about that cell?

 

[maravig]

got me. these cells are formatted as special, but that shouldn't matter, should it?

 

[maravig]

here is the user form code so you can see the whole thing and where your code fits in:

Option Explicit

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim strCSV, shtName As String
Dim csvWkbk, wkbk As Workbook
Dim lastRow As Long

'This sets a link to your macro workbook (that you run this from)
Set wkbk = ActiveWorkbook
    
'This gets the CSV file name out of the textbox you stored it in from your OpenButton_Click macro
strCSV = TextBox2.Value
shtName = Me.ComboBox1.Value 'This might need to change, I am not sure if you have to reference the user form first
    
'This opens the CSV workbook
Workbooks.Open strCSV
    
'This sets a link to the CSV workbook
Set csvWkbk = ActiveWorkbook
    
'Find the last row of the sheet you want in your wkbk assuming column A is always populated
lastRow = wkbk.Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row
    
'This copies the data from the CSV workbook (all cells that are filled in) to the first blank row on the tab in your macro workbook
wkbk.Sheets(shtName).Range(csvWkbk.Sheets(1).UsedRange.Address(0, 0)).Offset(lastRow, 0).Value = csvWkbk.Sheets(1).UsedRange.Value
    
'This closes the csv workbook
csvWkbk.Close
Unload Me
End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
  .Clear
  .AddItem "Combined"
  .AddItem "Count"
  .AddItem "Student Count"
  .AddItem "Finance"
  .AddItem "Accountability"
  .AddItem "EdEval"
  .AddItem "Staffing"
  .ListIndex = 0
End With
End Sub

Private Sub OpenButton_Click()
Dim FileToOpen As Variant
'added to start in the wanted folder
ChDrive "C:\"
ChDir "C:\Dashboard\"
FileToOpen = Application.GetOpenFilename()
If FileToOpen = False Then Exit Sub
TextBox2.Value = FileToOpen
End Sub

Sub FiveDigits()
    Dim arr, x As Long
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .NumberFormat = "@"
        arr = .Value
        For x = 1 To UBound(arr, 1)
            arr(x, 1) = Right("0000" & arr(x, 1), 5)
        Next x
        .Value = arr
    End With
End Sub

 

[MARK858]

these cells are formatted as special, but that shouldn't matter, should it?

Can't answer that one as in the UK there are no options under special when using the UK format and so we don't use it but why are they formatted as special?

The Special format is only normally used if you have zip codes, telephone numbers, social security numbers etc.

I thought we were dealing with normal numbers.

 

[maravig]

That was just a sample file I was messing around with. The regular format is General. I tried a normal data file and got the same result. Did you glance at the code being used?

 

[MARK858]

Yes. I did look at the rest of the code and can't see how it would affect the code I posted.
As I said the 3 converts on mine whether a real number or text.
The only thing I can think of is if you have hidden characters in the cell which might affect the outcome.

 

[maravig]

I'm just curious why you have 4 zeros instead of 5 in your code?