Working with fractions

[kshora]

Some months back I did receive the following code, can someone help me, how to use this code, I am not very good with VB or macros. What I want is to convert decimals to fractions (with denominators of 2, 4, 8, 16 or 32)

Help is highly appreciated

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' note that this implies that in Excel you did Insert | Name | Define...
' and defined a named range «ChangeToFractions» for those cells
' that you want converted from decimals to fractions
const c_strTestRangeName as string = "ChangeToFractions"
Dim rngCell As Range

If Intersect(Target, range(c_strTestRangeName)) Is Nothing Then Exit Sub

application.enableevents = false
for each rngCell in Intersect(Target, range(c_strTestRangeName)).cells
with rngcell
.value = Round(.Value * 32, 0) / 32
end with
next rngcell
application.enableevents = true

End Sub

 

[erik.van.geit]

Hi,

select the range on your sheet where you want to apply the code
in the namebox type
ChangeToFractions

http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

I added a IsNumber-check: else your code will bug when you type a non-number: this would cause that the event wouldn't be triggered anymore during the entire Excelsession

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' note that this implies that in Excel you did Insert | Name | Define...
' and defined a named range «ChangeToFractions» for those cells
' that you want converted from decimals to fractions
Const c_strTestRangeName As String = "ChangeToFractions"
Dim rngCell As Range

If Intersect(Target, Range(c_strTestRangeName)) Is Nothing Then Exit Sub

Application.EnableEvents = False
    For Each rngCell In Intersect(Target, Range(c_strTestRangeName)).Cells
        With rngCell
        If Application.IsNumber(.Value) Then .Value = Round(.Value * 32, 0) / 32
        End With
    Next rngCell
Application.EnableEvents = True

End Sub

kind regards,
Erik

 

[kshora]

I did as you advised, but nothing happens, the decimals did not change to fractions.

What do I do

Thanks

 

[erik.van.geit]

1.
be sure your code is in the correct SHEETmodule

2.
after the line
Dim rngCell As Range
put a testline

MsgBox "change event triggered"

you should get the popup
if not close Excel and open again
(there are other methods, but this will do for sure)
now it should popup

3.
be sure you changed a cell within the named range "ChangeToFractions"

best regards,
Erik

 

[kshora]

I again did as you advised, nothing is happening

I opened a new worksheet, input some fractions and named the range as "ChangeToFractions" in the Name Box. Right clicked the Sheet1, selected view Code and pasted your code in it and also added the new line and then simply closed the VBE window.

Am I doing any mistake.

Thanks and regards

 

[erik.van.geit]

there is a misunderstanding

the code you displayed in your initial post is "event"code
it will work when you put the decimals AFTER you pasted the code

FIRST paste the code
THEN put some values in the range

 

[kshora]

OOOO!! I See, my fault I did not understand. What I want is to convert to fractions in the existing workbook with already tonnes of data. How do I do it?

Thanks and regards

 

[erik.van.geit]

OK, we've learned something
the second option will be the fastest, talking about "tonnes of data"

you will need to choose a way to determine the range where the changes are to be made
as it is now you need to select the range
for the second code it has to be a "rectangular block" of data (technically spoken only one area)

Option Explicit

Sub test()
Dim cell As Range

Application.ScreenUpdating = False

    For Each cell In Selection
    'For Each cell In ActiveSheet.UsedRange
    'For Each cell In Range("whatever")
        With cell
        If Application.IsNumber(.Value) Then .Value = Round(.Value * 32, 0) / 32
        End With
    Next cell

Application.ScreenUpdating = True

End Sub

Sub testquick()
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim j As Integer

Set rng = Selection
'Set rng = ActiveSheet.UsedRange
'Set rng = Range("whatever")

arr = rng

On Error Resume Next
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
        arr(i, j) = Round(arr(i, 1) * 32, 0) / 32
        Next j
    Next i

rng = arr

End Sub

best regards,
Erik

 

[kshora]

Selecting the range will not be very difficuly, I can do that. I need to know how do I implement your code

I did exactly as I did previously, but nothing is happening

Please advise

Thanks and regards

 

[erik.van.geit]

sorry, I forgot to explain

but feel encouraged to test out yourself: don't sit there and wait

you can feel confident by making a copy of your file and then experiment as much as you want ...

you need to paste the code in a normal module
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T