CODEWITHSUNDEEP
excelvbaformatexcel-2010paste
Codo
Codo

Reputation: 271

VBA_Excel takes a date variable and pastes it inside a cell as a number

Ok i have this small code 

If Trim(apriori) <> "" Then
        With Sheets(yolk).Range("1:1")
            Set Rng = .Find(apriori, .Cells(.Cells.Count), xlValues, xlWhole, xlByRows, _
                            xlNext, False)
            yolk = yolk - 1
            'e = 1
            If Not Rng Is Nothing Then
                'Application.Goto Rng, True
                Rng.Offset(26, 0).Copy 'Worksheets("EXTRACTIONS").Range("B2").Offset(, e)
                some_date = Format(apriori, "dd/mmmm/yyyy") 'XX
                MsgBox some_date
                Worksheets("EXTRACTIONS").Range("B2").Offset(, e).Value = some_date 'XX
                Worksheets("EXTRACTIONS").Range("B3").Offset(, e).PasteSpecial xlPasteValues 'EDIT
                e = e + 1
                'activeCell kane offset ekei pou theloume
                'prwta offset theloume to address pou tha kanei to offset
                'timy = ActiveCell.Address
            Else
                MsgBox "Nothing found"
            End If
        End With
End If

The value apriori is a date value in US format and all i want is to convert it to the way dates look at calendars for example:

12/31/2010 to 31/December/2010

so i use

format(apriori, "dd/mmmm/yyyy")

and it does the job and i have the date as i want her to be in the msgbox some_date

However once i try to paste the value to a cell with 

Worksheets("EXTRACTIONS").Range("B2").Offset(, e).Value = some_date

The cell gets populated with numbers(!!!!) like 41,274 or 41,679.....(?)

How can i correct this bug in my VBA code?

Upvotes: 0

Views: 54

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You should change number format of your cell like this:

Dim some_date As Date
some_date = Now

With Worksheets("EXTRACTIONS").Range("B2").Offset(, e)
    .NumberFormat = "dd/mmmm/yyyy"
    .Value = some_date
End With

Upvotes: 2

Related Questions