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

--
Tomsk
-----------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...fo&userid=2682
View this thread: http://www.excelforum.com/showthread.php?threadid=50253

Tom,
Try:

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

"Tomski" wrote:


Hi,

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

Once all the copying is done I need to make sure that all numbers in a
certain column are stored as numbers. I tried the following, however
the line,
MyCell.Value = CInt(MyCell.Value) caused an error. Is this the way to
do this, i.e type casting, and if so why does this not work. Or is
there a better way, maybe something that works on the whole range as
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,

Tom


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=502530

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

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


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=502530