14

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?

Improve this question
2
  • 1
    locate the line that clears the contents and modify it. or use Range("A1").NumberFormat = "@" and then Range("A1") = "01/01/13 00:00" Commented Dec 16, 2013 at 13:47
  • @mehow that's better than both answers; one of them is mine. Commented Dec 16, 2013 at 13:48

4 Answers 4

Reset to default
32

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 for great minds thinking alike - but you were a bit faster as I was adding the explanation of the [A1] notation.
Text is fine but thank you I will try both just in case.
This doesnt work for me.. it works for some cells where i add some numbers, but not for adding text to some.. it is a protected worksheet but i can enter text in the cells manually.. what could be the issue that the macro fails? i just used Range("$D$2").Value = "'test"
11

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")
Improve this answer

7 Comments

Text is fine because I'm using this as a template to format the timestamps in the SQL table. Thanks.
Beware that the apostrophe will be there as a real character. SQL might not like that.
+ 1 to you and @Bathsheba. The only drawback is what you both accepted. i.e apostrophe being treated as a real character.
@SiddharthRout thanks. I agree - this is why I gave the alternative (which puts the right text in the right place in the right format without additional characters sneaking in).
unfortunately the alternative will not work. Let me explain. Try this. Format cell A1 as text manually. and now run your code. You will notice that you do not get the desired result. One should always change the numberformat first :)
|
6

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"
Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.