issue with numbers and dot in cell

[michlimes]

I have some vba macro, which should insert month and year with mask mmm.yyyy (i.e. 008.2014).

When macro is trying to put value "008.2014" into cell, excel recognizes it as a number and change value into number 8.2014. What's more, regional settings in windows has other decimal place -> comma.

How can I stop recognizing such a string as a number in Excel? I've tried str() in macro, but it hasn't helped...

 

[skywriter]

Can you share the code?
If I had the code I could experiment and see if I can help.

 

[gorem]

There are many solutions, but a couple that come to mind:
1. You could change the format of the destination column to Text before the insert takes place. Depending how you are inserting, the strings will remain strings.
2. You could probably make use of a formula like =TEXT(A2,"mm.YYYY") in your macro

 

[michlimes]

Can you share the code?
If I had the code I could experiment and see if I can help.

Sub test()
ActiveCell.Value = "008.2014"
End Sub

 

[skywriter]

Sub StringDate()

Dim StrDate As String

StrDate = "008.2014"
StrDate = Mid(StrDate, 2, 2) & "/" & Right(StrDate, 4)
StrDate = CDate(StrDate)

'The result in the cell will be Aug.2014
ActiveCell.Value = Format(StrDate, "mmm.yyyy")

End Sub

 

[FormR]

Try:

Sub test()
ActiveCell.NumberFormat = "@"
ActiveCell.Value = "008.2014"
End Sub