score:0

are you performing any special operation post loading the data into an array? if not then i'd suggest using simple copy and paste routine through vba which should work reliably.

public sub copypastedata()
    dim wksh as worksheet, wksh2 as worksheet
    set wksh = thisworkbook.sheets(1)
    set wksh2 = thisworkbook.sheets(2)
    wksh.range("a2:" & lastrow).copy wksh2.range("a2")
end sub

score:2

first note that "a2:" & lastrow is no valid address as the column letter for the second part is missing. it needs to be something like "a2:a" & lastrow

first option

one option is to loop through the array and test each element if it begins with = and replace it with '= (the apostroph is not shown but ensures that it is handled as text and not as formula). note that this will kill all formulas in that range.

dim arrc as variant
arrc = wksh.range("a2:a" & lastrow).value

dim irow as long
for irow = 1 to ubound(arrc, 1)
    dim icol as long
    for icol = 1 to ubound(arrc, 2)
        if left$(arrc(irow, icol), 1) = "=" then
            arrc(irow, icol) = "'" & arrc(irow, icol) 
        end if
    next icol
next irow

wksh2.range("a2").resize(ubound(arrc), 1).value = arrc

second option

the second option is to set the number format of the destination to text: .numberformat = "@" then paste the values and turn it back to general.

dim arrc as variant
arrc = wksh.range("a2:a" & lastrow).value

with wksh2.range("a2").resize(ubound(arrc), 1)
    .numberformat = "@"
    .value = arrc
    .numberformat = "general"
end with

note that .numberformat = "@" will turn also numbers into text so you need to turn it back to general to ensure that if there were numbers they are turned back to numbers again and you can calculate with them.

this workaround might have some odd effects on dates or other number formats. so this might not be a reliable solution depending on what data you have.

third option

last option is .copy and .pastespecial

wksh.range("a2:a" & lastrow).copy
wksh2.range("a2").pastespecial xlpastevaluesandnumberformats

'or without number formats
wksh2.range("a2").pastespecial xlpastevalues

Related Query

More Query from same tag