convert number in a cell to values in Lacs

Thanks:  0
Likes:  0

# Thread: convert number in a cell to values in Lacs

1. ## convert number in a cell to values in Lacs

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.

2. ## Re: convert number in a cell to values in Lacs

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.

3. ## Re: convert number in a cell to values in Lacs

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

4. ## Re: convert number in a cell to values in Lacs

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

5. ## Re: convert number in a cell to values in Lacs

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.

6. ## Re: convert number in a cell to values in Lacs

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

7. ## Re: convert number in a cell to values in Lacs

Originally Posted by uttamsaxena
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.

8. ## Re: convert number in a cell to values in Lacs

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.

9. ## Re: convert number in a cell to values in Lacs

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.

good luck
kevin

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•