CODEWITHSUNDEEP
vbaexcel
user6388449
user6388449

Reputation:

Copying data values only in a cell

Anybody knows how to copy the data in a cell, excluding its formats? Like the data itself is the only value that will be copied. I've tried these but is not working.

src.Range("F" & i).Copy Destination:=dest.Range("E" & destRow).PasteSpecial Paste:=xlPasteValues

And I have to parse also the data inside src.Range("F" & i) since it contains date and time values but I have to copy the date only. Any suggestions would be great.

Upvotes: 2

Views: 44

Answers (4)

user4039065
user4039065

Reputation:

The rawest information you can get from a Range object is the Range.Value2 property; this will effectively return a double since your cell contains both date (integers) and time (fractions of an integer, a double). Strip off the decimal portion of the .Value2 by casting it as an integer.

dest.Range("E" & destRow) = Int(src.Range("F" & i).VALUE2)
dest.Range("E" & destRow).numberformat = "mm/dd/yyyy"

Get it back as a date in the displayed format you want with the Range.NumberFormat property.

Note that this is similar to another answer but the time is actually stripped out of the cell value, not left there but not displayed.

Upvotes: 0

Irithyll
Irithyll

Reputation: 90

You can set the NumberFormat of the cell:

Range("E" & destRow).NumberFormat = "m/d/yyyy"

EDIT

The value itself won't change. You could "cut" the value:

Left(Range("E" & destRow).Value, 10)

Upvotes: 0

Mrig
Mrig

Reputation: 11702

You really don't have to copy paste when you only want the data in a cell. All you have to do here is convert data to desired format and assign to the cell.

dest.Range("E" & destRow) = CDate(Format(src.Range("F" & i).Value, "MM/DD/YYYY"))

EDIT:

Try this:

dest.Range("E" & destRow) = src.Range("F" & i)
dest.Range("E" & destRow).NumberFormat = "MM/DD/YYYY"

Upvotes: 2

user3598756
user3598756

Reputation: 29421

dest.Range("E" & destRow) = DateValue(src.Range("F" & i))

Upvotes: 1

Related Questions