VB array variable type for ascii string

[P0lar]

Hi,

I have a formula which creates a string including a superscript 0,1,2 or 3 - represented by chr(185), 186, 179 and 178:

¹0800-1500¹

I would like to pass this into an array in VB but can only fill the array with the #N/A "error 2042" for some reason, I suspect this may be due to the data type which is set as variant (I tried string data type as well)

 Dim OP(21) As Variant
j = 9 to 27
OP(j - 9) = Sheets("Filter").Range("O20") 'the cell with the above string in it
next j

 

[Debraj]

is this all your code..
No FOR, No SUB..
Only put the same data in all fields of array!!!

 

[SirJB7]

Hi, P0lar!

Give a look at the uploaded file.

Formula for A column:
A1: =CARACTER(ELEGIR(ALEATORIO.ENTRE(1;3);185;186;179))&"0800-1500"&CARACTER(ELEGIR(ALEATORIO.ENTRE(1;3);185;186;179)) -----> in english: =CHAR(CHOOSE(RANDBETWEEN(1,3),185,186,179))&"0800-1500"&CHAR(CHOOSE(RANDBETWEEN(1,3),185,186,179))

This is the code:

Option Explicit

Sub ItWorksDoesntIt()
    Dim OP(21) As Variant
    Dim J As Integer
    With Worksheets("Hoja1")
        For J = 0 To 20
            OP(J) = Worksheets("Hoja1").Cells(J + 1, 1).Value
            Debug.Print J, OP(J)
        Next J
        .Columns(2).ClearContents
        For J = 0 To 20
            Worksheets("Hoja1").Cells(J + 1, 2).Value = OP(J)
        Next J
    End With
End Sub

Check column B properly filled from array.

Regards!