Changing text to number format using type casting

[Tomski]

Hi,

I have made a macro that copies data from a number of sheets into
main one. One of the columns in the main sheet contains numbers to b
used in a vlookup. The problem is that when some of the numbers ar
copied across to the main sheet they remain in text format, as the
were stored in this format on the source sheet.

Once all the copying is done I need to make sure that all numbers in
certain column are stored as numbers. I tried the following, howeve
the line,
MyCell.Value = CInt(MyCell.Value) caused an error. Is this the way t
do this, i.e type casting, and if so why does this not work. Or i
there a better way, maybe something that works on the whole range a
opposed to each cell individually?

Set MyCell = ActiveSheet.Range("C2")
Do While MyCell.Value <> ""
MyCell.Value = CInt(MyCell.Value)
Set MyCell = MyCell.Offset(1, 0)
Loop

Cheers,

To

 

[Guest]

Tom,
Try:

MyCell.Value = CLng(DateValue(MyCell.Value))

 

[Yngve]

Hi Tomski

Insted of copy you can "copy" like this:

'wher you copy from
Dim n As Double
n = Val(Sheets("Ark2").Range("A1"))
'wher you copy to
Sheets("Ark1").Range("A1").NumberFormat = "General"
Sheets("Ark1").Range("A1") = n

Regards Yngve

 

[Tomski]

Excellent, that NumberFormat property of a range works a treat, cheers.

It would be good if there was a list of object properties somewhere as
there are so many that are so useful.

Cheers,

Tom