convert number in a cell to values in Lacs

uttamsaxena

Board Regular
Joined
Apr 22, 2003
Messages
182
In a sheet, I have a column in which I fill values based on the inputs given to me. The values, which I get, are in absolute numbers e.g. 456231, 5689456, 456387 etc. but I have to fill value in lacs of these nos. I mean I have to fill 4.5, 56.9 and 4.6 against the received numbers. I mean I want that on filling any value in the cell, it should get converted to Lacs and rounded to 1 decimal place.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I think I have not explained the problem properly. I have always to put a decimal at the Lacs place manually and then I fill the value in the cell. e.g. if I get a value of 56982153 to fill I convert it 569.8 manually and the fill 569.8 in the cell. It is very tedious for a lot of data. So I want that I should fill 56982153 in a cell and it should be converted to 569.8 automatically in that cell.
 
Upvote 0
try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Round((Target.Value / 100000), 1)
Application.EnableEvents = True
End Sub

place in your worksheet module for the sheet you are entering the data into. modify as needed

hth
kevin
 
Upvote 0
Go to the Tools | Options.... Edit tab, check "Fixed decimal places", and set to 5. On the Calculation tab check "Precision as displayed". Format your cells as...

0.0
 
Upvote 0
Thanks for the help. But both the above methods are applying to every cell on the sheet while I have do this on some selected columns on sheet not every where on the sheet e.g. I want to do it on every cell of column D, H and K.
 
Upvote 0
modified:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 And Target.Column <> 8 And Target.Column <> 11 Then Exit Sub
Application.EnableEvents = False
Target.Value = Round((Target.Value / 100000), 1)
Application.EnableEvents = True
End Sub

kevin
 
Upvote 0
uttamsaxena said:
Thanks for the help. But both the above methods are applying to every cell on the sheet while I have do this on some selected columns on sheet not every where on the sheet e.g. I want to do it on every cell of column D, H and K.

My recommendation...

Go to the Tools | Options.... Edit tab, check "Fixed decimal places", and set to 5. On the Calculation tab check "Precision as displayed". Format your cells as...

0.0

...can be done and then undone as needed for your data entry puposes.
 
Upvote 0
Hi, sorry for bringing back this post...........

Kevin, I tried the gcode suggested by you. but I face some problems in that.

1. If i fill a number value in a blank cell and enter it, and again delete that value, the cell dont become blank but always shows a zero in that cell.
2. undo do not function after the data is entered.
3. If by mistake one enters a string value in these cells, the error msg box opens to either stop or debug the code.

Pl. help.

Also tell me where to look for understanding the code you have given, I mean where to learn about meanings of ''Worksheet_Change(ByVal Target As Range)'' etc keywords on the net. Thanks.
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 And Target.Column <> 8 And Target.Column <> 11 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Round((Target.Value / 100000), 1)
Application.EnableEvents = True
End Sub

i have modified the code so that it will allow you to completely clear a cell instead of converting the value to zero on delete. i also changed it to allow a text string entry without triggering an error message. this can be modified to do whatever you want to happen when a user enters text, but right now it simply exits the macro and allows the text entry. as far as allowing for an undo after a macro has been run, i don't know how to do this, but i am pretty sure it has to be done programmatically with code, and if you search this forum there are probably other posts asking the same question as yours that may have an answer.

to learn more about this code, i would start out by looking up the key words in this code in visual basic online help in your Excel program by opening the visual basic editor (ALT+F11) and pressing F1 for help. on the internet, i would say this site is probably the best place to learn more about VBA for Excel.

good luck
kevin
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top