Format Method - how do I stop a cell turning into a date?

Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.

Cells(CrtTaskLn, 9) = Area

I tried this... amoungst other things...

Cells(CrtTaskLn, 9) = Format(Area, "@")

Can't seem to get anything to work, bugging the hell outta me! Appreciate any tips.

EDIT:
Additionally I need a custom number format that works like 0.##, i.e. displays up to 2 decimal places, but I don't want the decimal point visible if the zeroes aren't present - i.e. that currently displays "1" as "1."

Good evening ChrisMattock

Originally Posted by ChrisMattock

Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.

Hmmmm, sure is a pesky problem. The Format function doesn't always do a sterling job in forcing a cells number format - especially if Excel has other ideas

You could try forcing the cell format, then post the number to it, thus :

With Cells(CrtTaskLn, 9)
.NumberFormat = "@"
.Value = area
End With

HTH

DominicB

Wow, I really thought that this would be a simple problem to solve! Obviously not, eventually after much furrowing of the brow I cam up with this, which works but I guess isn't ideal!

Format("'" & Area, "@")

Sadly the below still aren't formatting as they should, I shall try the method you have provided.

Cells(CrtTaskLn, 13) = Format("=" & ItemCost & "*" & Cells(CrtTaskLn, 12).Address(False, False), "$#,##0.00")
Cells(CrtTaskLn, 14) = Format("=" & ItemHours & "*" & Cells(CrtTaskLn, 12).Address(False, False), "0.00")

EDIT:

Thanks Dominicb - problem solved in it's entirety! +rep for you (if I can!)