CODEWITHSUNDEEP
vbaexcel
Dranthe
Dranthe

Reputation: 3

VBA Change Cell Value On Insert

I apologize for the confusing title. I've run into a quirk that I don't know exactly how to describe, work around, or fix. I have an item that I pulled by value from another workbook and wish to insert it from the collection I passed back to the original function. I print it out in the debug screen and it is the correct value as follows:

9-06-01

The very next line I insert it into the appropriate cell like this:

Dim item As Variant

For Each item In someCollection
    Debug.Print item
    thisSht.cells(y, x).Value = item
Next item

When I insert it in to the cell the value becomes

37140

The value is not intended to be treated as a date and I thought it was excel trying to auto-format but that doesn't seem to be the case as once I set the correct cell formatting the issue is still there. It works for some other values such as 807-01-01. Any help would be appreciated.

Upvotes: 0

Views: 76

Answers (1)

Rory
Rory

Reputation: 34065

It is being treated as a date (6 Sep 2001). To work around that you can prefix it with ' or format the cell as text:

thisSht.cells(y, x).Value = "'" & item

or

With thisSht.cells(y, x)
.Numberformat = "@"
.Value = item
End With

Upvotes: 2

Related Questions