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

 

[bulba5aur]

Have you tried stepping through the code? My guess is that IsCustom() sub/function is not working like you intend it to, and it's skipping over the code that sets c.NumberFormat = "00000".

 

[maravig]

I should have mentioned I'm pretty new to vba coding, so I'm winging this as best I can.

 

[MARK858]

Possibly try....

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

but obviously the result is text and not a real number.

 

[Norie]

What does your IsCustom function do?

Can you post its code?

 

[maravig]

That was interesting. I ran the code and it was processing. In the background, it actually showed up correct!. Then I noticed it saying "filling cells" in the bottom and it went back to its original state. Man, so close.

 

[maravig]

@Norie - no clue, I just grabbed that code off another site this a.m.

 

[Rick Rothstein]

@Norie - no clue, I just grabbed that code off another site this a.m.

Well surely you can answer Norie's second question then...

 

[maravig]

I removed that code and am using Mark858's and that's the closest I've come to it working.

 

[MARK858]

using Mark858's and that's the closest I've come to it working

In what way is not doing what you want?