How to avoid text from being auto converted to a date 1

[Einstein47]

I am working on a macro that takes input values and compares various columns to a defined translation table to get the output value. The translation table is user defined. One of the input/output values is being automatically converted to a date when it is encountered.

This is the [red]input[/red],[blue]output[/blue] value:
[red]Bicarbonate (As CaCO3)[/red], [blue] 10-13-9[/blue]

How can I keep Excel from converting "10-13-9" to a date? I need to keep it as text.

I have tried putting a single quote (') before the output value, but that doesn't help. This is the line in the macro VBA code that reads the translation table.

If CAS = "" Then
  TempStr = Application.WorksheetFunction.VLookup(Analyte, Sheets("Defs").Range("CASrange"), 2, False)
  Cells(curRow, 13).Value = TempStr
Else
  Cells(curRow, 13).Value = CAS
End If

Where: Analyte="Bicarbonate (As CaCO3)"
And: TempStr="10-13-9"

Any help is greatly appreciated.

Einstein47
“Evil abounds when good men do nothing.“ - Nelson R.
[[]Starbase47.com]

 

[AnotherHiggins]

Try .Text instead of .Value

Or =format(CAS, "@")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Cells(curRow, 13).TEXT = "'" & TempStr


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Einstein47]

Thanks xlbo for that quick fix. I actually tried that right after posting here and it worked, so it is good to be validated.

Sorry that I didn't need to try your suggestions, anotherHiggins. When you find something that works, you go for it.

Einstein47
“Evil abounds when good men do nothing.“ - Nelson R.
[[]Starbase47.com]