Code to correct errors in cell values

[RAJESH SHAH]

Hello everyone
I have this code which is to convert certain columns into the format required. As the cells contain errors maybe that is why I am not able to convert the columns into the required format. Can anyone please suggest what to do and how to get the expected result with the help of a code.

[TMS]

The values in column K are Text so you won't be able to apply a numeric format.

You need to reformat the cells as General and then Copy and Paste Special | Values to convert to numbers.

[RAJESH SHAH]

TMS. I am selecting the first cell and then the whole column, In the side there is a box where I select convert to number and get the format. But that is how it is done manually. Is it not possible to do the same with the help of a code.?

[RAJESH SHAH]

As the Rate% in another sheet is 18 and when I use a formula in the code to join Rate% then the vlookup function will display #NA as this sheet is showing 18.00. The amounts too will show an error when I use a sumifs formula to match the amounts/

[RAJESH SHAH]

If i type, in an empty column in the sheet as =NUMBERVALUE and give the cell reference and enter I will get the number format. For that I will have to create a temp file for each row and then replace the column with the temp file. How to write the code, you people are the experts.

[Marc L]

According to your initial post a single VBA codeline as a beginner starter :

Sheet59.Range("K5", Sheet59.[K4].End(xlDown)).NumberFormat = "General" …

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[RAJESH SHAH]

Marc L. After running your code, the display is the same as it was before.. I need to get the number without decimal and without any error and decimal of column K. All other columns with 2 decimals without any errors.

[Marc L]

( removed )

[Marc L]

'Cause you forgot to well read & follow post #2 !
So according to your attachment a VBA demonstration as a very beginner starter :

Sub Demo4Noob()
    With Sheet59.Range("K5", Sheet59.[K4].End(xlDown))
        .NumberFormat = "General"
        .Formula = .Value
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[RAJESH SHAH]

Yes. This is what I wanted. Thanks Marc L.
Is it possible to add the other columns in this code itself to get the number format for all required columns or should I create individual code for each column.

[Marc L]

For all consecutive text columns to convert to General number format just obviously change the starting cell address of the End property.

As a reminder such VBA code is totally useless if rather than importing data with foot an appropriate import protocol is used …

[RAJESH SHAH]

Got all the columns as expected.
I have replaced the code with the sheet name as I have to do the same with another 9 sheets with the same columns and formats in the same workbook.

Please Login or Register  to view this content.

Thanks Marc L. You people are the best.

[Marc L]

Thanks for the rep' !

You have made an error when modifying the cell address as I very not wrote the cells addresses …

[RAJESH SHAH]

What error..? Oh Yeah. column K I needed in general format. But, Finally I got the result as expected. I will try and correct it.

[Marc L]

As I wrote « the starting cell address » so obviously an unique cell address and not cells addresses …

[RAJESH SHAH]

Your code gave me the idea to write the cell addrsses. Thanks man.