Excel VBA - Type conversion?

[danne_dash]

Hi,

I'm working on an Excel VBA project where I want to copy the content of a range of cells to another range of cells.
The source cells contain figures (formatted as Text in the Excel sheet) and the destination cells shall have the exact same format. The data is for example: "1.1", "1.10", "2.3" etc.

I use the command
Sheets("Sheet1").Cells(destRow, destCol).Value = Sheets("Sheet1").Cells(sourRow, sourCol).Value
to copy the data, but them the destination cells is filled with data according to:
"1.1" >> "1,1"
"1.10" >> "1,1"
"2.3" >> "2,3"

I think that the reason for this is that the VBA function treats the source data as numbers and I have swedish version of Excel 2010. In Sweden the decimal designator is comma.

I have tried to use Cstr() and Format() to type convert the source data to string, but the destination data is still the same. Displaying all different kinds of Sheets("Sheet1").Cells(sourRow, sourCol).Value with or without conversion in a msgbox shows the data correctly.

Anyone having any idea?
/Dash

 

[Norie]

Why not use Copy?

The code you posted is just setting the values of the destination range.

What exactly do you want to copy?

This will copy values and formatting.

 Sheets("Sheet1").Cells(sourRow, sourCol).Copy Sheets("Sheet1").Cells(destRow, destCol)

 

[danne_dash]

Thanks a lot. It works with your suggestion.

I wasn't aware of the copy function, I'm quite unexperienced with VBA, so I didn't know it existed.

/Dash

 

[doofusboy]

You could also format your destination cells as text before you do your assignment. Something like this:

Sheets("Sheet1").Cells(destRow, destCol).Format = "@"
Sheets("Sheet1").Cells(destRow, destCol).Value = Sheets("Sheet1").Cells(sourRow, sourCol).Value