VBA Storing Text Value in Array

[svendiamond]

Hello all! I received advice from one of the pros on here, can't remember whether it was Aladdin or Rick, but it was regarding looping through an array stored in VBA rather than writing to a sheet during a loop. This has made things much faster. However I can't seem to store a text variable in my array.

For example this code:

With .Range("A2:H" & lastRow)
        myData = .Value
        For i = 1 To UBound(myData)
                newData(n, 1) = myData(i, 1)
        Next i
        .Cells = newData
End With

The code works great except if the value is a number stored as text such as "000123456789" then the new data transfers over as a number value without the zeroes in front. I've tried "Format(myData(i, 1), "@")" but that doesn't work. Any ideas?

 

[Jeffrey Mahoney]

You need to format the output cells as text.

Please accept my question as trying to help. I'm not sure how that could work correctly. Is there a reason you are transferring the information from one array to another?

Sub CopyTextValues()
  Dim Arr As Variant
  Dim R As Range
  
  Set R = Range("E6:F12")
  Arr = R.Value
  
  Set R = Range("J6:K12")
  R.NumberFormat = "@"
  R.Value = Arr
    
End Sub

 

[Norie]

What are you doing with the array(s)/range(s) in the code?

 

[svendiamond]

Thanks Jeffrey. I only posted an example and wanted to make it easy to see. I think I made it too simple. Basically I have a report with like 10 columns of messy data, and I am trying to get it down to just like four columns with clear information. One of the columns is numbers formatted as text which I would like to keep. I think you're right. I just need to format the cells before applying the array to the sheet. Thank you!