Filling cell with lowercase "true" and "false" without an apostrophe

[flatman]

What's the equivalent to this in VBA?
=Lower("TRUE")
Copy -> paste_special -> Values

The above gives me the lowercase value "true" in the cell without a leading apostrophe.

I tried a bunch of things in VBA including:
Range("A1").Value = LCase("TRUE")

But each time the result ends up uppercase. Adding an apostrophe is not a solution for me as I the 3rd party tool that reads my resulting excel file does not like uppercase and it does not like the leading apostrophe.

Thanks!

 

[Rick Rothstein]

This seems to work...

Range("A1").NumberFormat = "@"
Range("A1").Value = "true"

 

[WarPigl3t]

I don't know what you mean by leading apostrophe. Try puting the word "TRUE" in a variable and instanciate the variable as a string first.
Dim var as string
var = "TRUE"
Range("A1").value = LCase(var)

 

[Scott Huish]

If you put a leading apostrophe before a value in Excel, it makes it TEXT and the reason you would do that is because TRUE and FALSE are boolean values that Excel displays in uppercase.
WarPiglet, your solution will not work unless you make the cell in text format like Rick did.

 

[Peter Thompson]

Not sure why lcase isn't working for you there are a couple of things you could try. I would try assigning "TRUE" to a string variable and then apply the lcase function to this variable and see if it makes any difference. Alternatively you could get excel to insert a column next to the column containing the values you want to make lowercase and then getting vba to put a formula using the lower function in this column before making this column values and deleting the column with the original upper case entries.

 

[flatman]

This seems to work...

Range("A1").NumberFormat = "@"
Range("A1").Value = "true"

Thanks! Does exactly what I needed.