Excel VBA write as text to cell

Vaslo

Board Regular
Joined
Jun 3, 2009
Messages
159
Very simple question I can't find the answer to. I am simply writing a long array to a group of cells:

Code:
For i = 1 To UBound(itemArray)
    Cells(i, 1).Value = itemArray(i)
Next

itemArray is Dimmed as a String array. Some of the values unfortunately are either mixes of numbers and letters or just pure numbers. When I try to write it to the spreadsheet, it converts the ones that look like numbers to numbers. I tried the following to fix, and searched around but could not find an answer:

Code:
For i = 1 To UBound(itemArray)
    Cells(i, 1).Value = CStr(itemArray(i))
Next

Still writing the items that are numbers as numbers. How can I force the array output to be text, whether its a mix of text and numbers or just numbers?

Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Format the cells as Text before putting the values in them.
 
Upvote 0
Format the cells as Text before putting the values in them.

That works but what if the worksheet is created from scratch, meaning that when I run the code, I kill the old worksheet then have the code create a new one.
 
Upvote 0
How would that stop you formatting the cells, using code, to Text before putting the values in them?
 
Upvote 0
How would that stop you formatting the cells, using code, to Text before putting the values in them?

Oh ok, I misunderstood. I thought you meant to physically select the column and set it to text before running the macro. As a VBA novice it had not occurred to me (though I guess it should have) that I could do this programatically. I'm used to thinking in formulas where I might force it to text via: =A1&"" and was trying to think of a way to do that when outputting the array.

So this seems to work but if you have a better approach I would appreciate your recommendation:

Code:
For i = 1 To UBound(itemArray)
    Cells(i, 1).NumberFormat = "@"
    Cells(i, 1).Value = itemArray(i)
Next
 
Upvote 0
Perhaps.
Code:
    With Cells(1, 1).Resize(UBound(itemArray))
        .NumberFormat = "@"
        .Value = Application.Transpose(itemArray)
    End With
 
Upvote 0
Perhaps.
Code:
    With Cells(1, 1).Resize(UBound(itemArray))
        .NumberFormat = "@"
        .Value = Application.Transpose(itemArray)
    End With

Thanks for your time Norie. I need to learn this way of coding, I don't have experience with the "With" statement. Thanks again for your help.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top