Array to range with values that look like formula

[gousssam]

Hello. I'm having a problem where excel will throw a 1004 error "application-defined or object-defined error".

The issue is that I have an array of values with strings that look like "=?DAFqwr".

As a minimal example of this:

Sub testArr()

dim arr(1 to 1, 1 to 1) as variant

arr(1,1) = "=x2j?1"
range("A1").value2 = arr(1,1) ' error occurs here

end sub

Does anyone know of any workarounds for this?

 

[gousssam]

Hello. I'm having a problem where excel will throw a 1004 error "application-defined or object-defined error".

The issue is that I have an array of values with strings that look like "=?DAFqwr".

As a minimal example of this:

Sub testArr()

dim arr(1 to 1, 1 to 1) as variant

arr(1,1) = "=x2j?1"
range("A1").value2 = arr(1,1) ' error occurs here

end sub

Does anyone know of any workarounds for this?

Sorry, that code works, the issue is when the string starts with =? e.g. "=?anyletters"
So this will throw the error as described:

Sub testArr()

dim arr(1 to 1, 1 to 1) as variant

arr(1,1) = "=?x2j?1"
range("A1").value2 = arr(1,1) ' error occurs here

end sub

 

[Snakehips]

@gousssam Does this help?

Sub testArr()

Dim arr(1 To 1, 1 To 1) As Variant

arr(1, 1) = "=?x2j?1"
With Range("A1")
.NumberFormat = "@"
.Value2 = arr(1, 1) ' error occurs here
End With
End Sub

 

[gousssam]

Yes that did work, thank you very much Snakehips