Adding text to a cell in Excel using VBA

14
votes

I've been working with SQL and Excel Macros, but I don't know how to add text to a cell.

I wish to add the text "01/01/13 00:00" to cell A1. I can't just write it in the cell because the macro clears the contents of the sheet first and adds the information afterwards.

How do I do that in VBA?

4
vbaexcel
locate the line that clears the contents and modify it. or use Range("A1").NumberFormat = "@" and then Range("A1") = "01/01/13 00:00" – user2140173
@mehow that's better than both answers; one of them is mine. – Bathsheba

4 Answers

29
votes

Range("$A$1").Value = "'01/01/13 00:00" will do it.

Note the single quote; this will defeat automatic conversion to a number type. But is that what you really want? An alternative would be to format the cell to take a date-time value. Then drop the single quote from the string.

10
votes

You could do

[A1].Value = "'O1/01/13 00:00"

if you really mean to add it as text (note the apostrophe as the first character).

The [A1].Value is VBA shorthand for Range("A1").Value.

If you want to enter a date, you could instead do (edited order with thanks to @SiddharthRout):

[A1].NumberFormat = "mm/dd/yyyy hh:mm;@"
[A1].Value = DateValue("01/01/2013 00:00")
5
votes

You need to use Range and Value functions.
Range would be the cell where you want the text you want
Value would be the text that you want in that Cell 

Range("A1").Value="whatever text"
0
votes

You can also use the cell property. 

Cells(1, 1).Value = "Hey, what's up?"

Make sure to use a . before Cells(1,1).Value as in .Cells(1,1).Value, if you are using it within With function. If you are selecting some sheet.