VBA Number formatting with an extensive list

[Lizzielady]

Hi guys!

Having a problem with one of my bits of code, hope someone can help ?!

I have a list of employee numbers which are either 3 or 4 digits long, the list can be anything from 1 row and up. It depend on what absences have been taken in that department.

I have wrote a macro which basically runs an import sheet, but the system in imports too needs the numbers to be in six digit style, so either 00 or 000 in front of number.

I wrote the below and it works, unless there is only one employee to import, then I get a 'Type Mis-match' error....am I missing something?

Dim a
Dim i As Long

With Range("A2", Range("A" & rows.Count).End(xlUp))
.NumberFormat = "@"
a = .Value
For i = 1 To UBound(a, 1)
a(i, 1) = Right("00000" & a(i, 1), 6)
Next i
.Value = a
End With

Thanks

 

[GTO]

'a' is not an array if there is only one number in A2. You can see this with stepping thru the code and with the Locals window displayed. Does that help?

For the code, you just need to test whether there are any values below the header row first, then, rather than depending upon there being at least two values and thus, an array, just use a For Each...Next:

Option Explicit
    
Sub example()
Dim Cell As Range
    
    If Cells(Rows.Count, 1).End(xlUp).Row >= 2 Then
        With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            .NumberFormat = "@"
            For Each Cell In .Cells
                Cell.Value = Right("000000" & Cell.Value, 6)
            Next
        End With
    End If
End Sub

 

[Lizzielady]

Thank you, works great!

Thank you for your explanation too, I'm still pretty new with coding so I have a lottttt to learn

 

[GTO]

Hi Lizzielady,

You are most welcome and glad that helped. No worries about learning, we all are, and at least for me, even when I think I have a few things down pat, I forget stuff!

Have a great day,

Mark