Results 1 to 9 of 9

convert number in a cell to values in Lacs

This is a discussion on convert number in a cell to values in Lacs within the Excel Questions forums, part of the Question Forums category; In a sheet, I have a column in which I fill values based on the inputs given to me. The ...

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default 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. #2
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default 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. #3
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default 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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default 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. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default 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. #6
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default 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. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: convert number in a cell to values in Lacs

    Quote 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. #8
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default 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. #9
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default 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.

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com