automatic numeric number change

Question

&nbsp; I am in the process of an old software conversion into excel. How would one format a cell to see a whole number and automatically change it to inches. i.e. 20 typed in would show as 240 in that column?&nbsp;

dkoontz · Answer

instead of trying to format the original feet column, I would instead just create a helper column that converts the feet to inches by just doing the feet column * 12. That way you're not changing any source data and have both to reference if needed

rikkej · Answer

on my sample data, i tried that. the programmer asked to only send column data as such, without any added data columns or functions like you suggested, so i figured i would ask the pros. thanks

hansvogelaar · Answer

RikkeJ&nbsp;
If you really want to do this:
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("B2:B100"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("B2:B100"), Target)
            If rng.Value &lt;&gt; "" Then
                If IsNumeric(rng.Value) Then
                    rng.Value = 12 * rng.Value
                End If
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Change both instances of B2:B100 to the range where you want to multiply numbers with 12.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Warning: undo won't work for entering/editing data in the specified range. Clicking Undo or pressing Ctrl+Z will undo the previous action.

Forum Discussion

automatic numeric number change

3 Replies