MACROissue

[PGD15]

I am trying to write a Macro as i have a lot of data, but due to the format it doesn't pick it up as a number only as text, so i have to click into the cell and press enter to turn it into a number.

So i have data in column K and want to say if K2 contains data, click into the cell and press enter, then move onto cell K3 and repeat until reaching a cell which contains no data.

I am trying to do this as i am trying to apply a formula to these cells, but currently it says 0 instead of the correct answer.

(i have tried highlighting all and changing from general to currency but it didn't work).

If there is an easier way to change the data types please suggest away, just think a macro will work

 

[MARK858]

Try...

Select the column then Data - Text to Columns - click Next twice - check it is set on General - click Finish.

Or

Put a 1 in a spare cell, Copy the cell, select the column and then paste special, check the multiply checkbox then OK.

 

[Ashen]

you can either do a
enter 1 in a cell, copy
select all those values you want to convert to a number and paste special and choose multiply

or if you want to loop through the cells, you can put cdbl(CELL VALUE) which will convert to a double (numeric including decimal places)

 

[ask2tsp]

Hello PGD15.

Could you give a sample of the data in column K?

Where is the data coming from? Since it's a lot (how many rows?) probably from import of a csv file or a database query. Maybe adapting that can solve your problem.

 

[PGD15]

The file is a CSV file but I cannot open it in any other format due to the size of it will make my computer crash. :'(

 

[PGD15]

Thanks! sorry for the slow reply!

 

[MARK858]

Hello PGD15.

Could you give a sample of the data in column K?

Where is the data coming from? Since it's a lot (how many rows?) probably from import of a csv file or a database query. Maybe adapting that can solve your problem.

But in the 1st post you already have it open in Excel and so did none of the methods suggested work?

 

[ask2tsp]

MARK858 I totally understand your post #7. You take your time to help other forum members, and they don't reply. It lowers my motivation to continue a bit.

 

[MARK858]

There is no frustration the OP just hasn't stated what happened when you tried the suggestions given in posts #2 and #3.

 

[MARK858]

@ ask2tsp, I mistakenly used a quote from the wrong post, it should have been...

The file is a CSV file but I cannot open it in any other format due to the size of it will make my computer crash. :'(

Sorry for the confusion