VBA - custom number formats from arrays - error after certain array number

Question

I am having trouble trying to figure out why my code is not working. Let me paste it first:

Dim n As Integer
Dim List_number As String

Dim arr_number(1 To 26) As Integer
    arr_number(1) = "1"
    arr_number(2) = "2"
    arr_number(3) = "3"
    arr_number(4) = "4"
    arr_number(5) = "5"
    arr_number(6) = "6"
    arr_number(7) = "7"
    arr_number(8) = "8"
    arr_number(9) = "9"
    arr_number(10) = "10"
    arr_number(11) = "11"
    arr_number(12) = "12"
    arr_number(13) = "13"
    arr_number(14) = "14"
    arr_number(15) = "15"
    arr_number(16) = "16"
    arr_number(17) = "17"
    arr_number(18) = "18"
    arr_number(19) = "19"
    arr_number(20) = "20"
    arr_number(21) = "21"
    arr_number(22) = "22"
    arr_number(23) = "23"
    arr_number(24) = "24"
    arr_number(25) = "25"
    arr_number(26) = "26"
    n = 1

List_number = "\" & arr_number(n) & "\._1@"

Dim myformat As String
Dim cell As Range

    For Each cell In Selection
        List_number = "\" & arr_number(n) & "\._1@"
        myformat = List_number
        cell.NumberFormat = myformat

        n = n + 1

    Next cell

What I am trying to achieve with this code is to create some kind of simple list in excel, changing the custom format from "cell.value" to "myformat.cell.value". Please see the screen below:

My code is adding number, dot and space before each cell value. It works great for the first 9 cells but I have no idea why it gives an error at 10th cell: "Run-time error '1004' Unable to set the NumberFormat property of Range Class".

Weird behavior is that when I enter letters into my array it works great, but with numbers, it does not :(

I have tried multiple things (for example trying to split my "10" value from array into two separate pieces like "1" & "0") but after a few hours of no success, I gave up.

I have two questions: 1) Does anyone know why this error is popping up and how can I bypass it to continue with my macro? 2) Is there any way to assign values to arrays without the need to type each line separately? Both for number and for custom characters, like letters or signs?

Thank you in advance for your assistance!

Answer 1

Try this. It uses a different number format: "1." @

Sub Kowalski()
    Dim f
    f = [char(34)&row(1:999)&"."&char(34)&" @"]
    Selection.NumberFormat = f
End Sub

How this works:

Dim f creates a variant variable.

The next line uses Excel's Evaluate method denoted by the square brackets to create a 2d array of 999 strings that will serve as number formats. This line also assigns that array to f.

The last line assigns in one go the array of separate number formats to your current range selection on the worksheet.

The f array looks like this:

f(1,1) = "1." @
f(2,1) = "2." @
f(3,1) = "3." @
f(4,1) = "4." @
f(5,1) = "5." @
.
.
.
f(999,1) = "999." @