VBA to convert the range B in number format??

[rohitw2]

Hi All,

I need a help where I have the numbers in column B but they are not in the number format. So I want the moment my macro paste the data in column B, it should convert that column in number format. I have tried multiple time but it only paste the data in as usual format.

Kindly advise

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]25-03-17[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]15-02-17[/TD]
[TD]6985[/TD]
[/TR]
[TR]
[TD]20-03-17[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]12-01-17[/TD]
[TD]7321[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ruddles]

Have you tried using the Macro Recorder to generate the VBA you need?

 

[rohitw2]

In the record macro i didnt able to see the exact code for converting into the number.

 

[Rick Rothstein]

Hi All,

I need a help where I have the numbers in column B but they are not in the number format. So I want the moment my macro paste the data in column B, it should convert that column in number format.

Add these two lines of code immediately after the code that puts the numbers in Column B...

Columns("B").NumberFormat = "General"
Columns("B").TextToColumns

 

[rohitw2]

will this make my data in column B in number format as i have to use it for my further calculations.

 

[Rick Rothstein]

will this make my data in column B in number format as i have to use it for my further calculations.

The cells will be formatted as General (the default for cells in Excel) so the code I gave you would result in the cell containing a real number if the text in the cell is a proper representation of a number (just like if you had typed it in manually).

 

[rohitw2]

https://1drv.ms/i/s!ArrD_eUdmgj1jgQcqBO4mpV0uv_z

I haveshare the snap shot link kindly look into it where i need to do the convert all items in range in number format without using the mouse.