Automatically Calculate Cell

[honkin]

Hi
I am trying to put together a racing results sheet and need some assistance with one particular task.

Price are available often as a fractional number or a decimal eg 5/2 In decimal this would be expressed as 3.50, as you are also adding back in the 1 stake amount.

So if it was a calculation for 5/2, it would be =5/2+1 That is just a cumbersome way to have to input and I am wanting to hand the sheet over to someone else and would love to have the result appear in the cell when simply 5/2, or any fractional number is typed. I tried just formatting the column as number with 2 decimals, but it naturally doesn't know to add the 1 to the result

Is there a way to automate this, bearing in mind that new selections are going in daily? So each day, selections for the day are pasted in from another Excel sheet and the following day the results are added. I just want the sheet to constantly calculate a single column each time a fractional number is typed.

I looked at conditional formatting as well, but that way seems to hard code whatever fraction I put in the formula. I simply want it to take 15/8 and make it the result of =15/8+1, which is 2.88

Thanks so much in advance

 

[juddaaaa]

If you want the result in the same cell you'll need to do it in VBA

Here's an example. Paste this in the relevant Worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim re As Object
    
    On Error GoTo err_handler
    
    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=[0-9]+\/[0-9]+$"
    
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        With Target
            If re.test(.Formula) Then
                .Formula = .Formula & "+1"
            End If
        End With
    End If

exit_handler:
    Application.EnableEvents = True
    
Exit Sub

err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_handler
    
End Sub

This assumes that the column you are entering the formulas in is Column B

If this is different for you change this line

If Not Intersect(Target, Columns("B")) Is Nothing Then

to the column you are using

 

[honkin]

If you want the result in the same cell you'll need to do it in VBA

Here's an example. Paste this in the relevant Worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim re As Object
   
    On Error GoTo err_handler
   
    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler
   
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=[0-9]+\/[0-9]+$"
   
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        With Target
            If re.test(.Formula) Then
                .Formula = .Formula & "+1"
            End If
        End With
    End If

exit_handler:
    Application.EnableEvents = True
   
Exit Sub

err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_handler
   
End Sub

This assumes that the column you are entering the formulas in is Column B

If this is different for you change this line

If Not Intersect(Target, Columns("B")) Is Nothing Then

to the column you are using

Thanks so much for your reply juddaaaa
I created a new module for the worksheet and copied the code in and changed the column to P. It was saved as a macro enabled file and when I reopened it and typed 5/2 into a cell in column P, the answer was still 2.50, rather than 3.50, so not sure what is amiss.

Any suggestions? Here is a screen shot of the project showing the module

Here is the code copied straight from the module

Private Sub Starting_Price(ByVal Target As Range)

    Dim re As Object
   
    On Error GoTo err_handler
   
    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler
   
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=[0-9]+\/[0-9]+$"
   
    If Not Intersect(Target, Columns("P")) Is Nothing Then
        With Target
            If re.test(.Formula) Then
                .Formula = .Formula & "+1"
            End If
        End With
    End If

exit_handler:
    Application.EnableEvents = True
   
Exit Sub

err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_handler
   
End Sub

Thanks in advance

 

[juddaaaa]

Did you get a yellow banner at the top to Enable Content and did you click it?

Oh and you don't want it in a module. You want it in the relevant Worksheet Module(s)

One (or more) of these

 

[honkin]

Did you get a yellow banner at the top to Enable Content and did you click it?

A dialogue box opens as per normal asking to Enable Macros or Disable Macros and I selected Enabe. It simply outputs the standard decimal format of the fraction, rather than the result of the formula =5/2+1

cheers

 

[juddaaaa]

A dialogue box opens as per normal asking to Enable Macros or Disable Macros and I selected Enabe. It simply outputs the standard decimal format of the fraction, rather than the result of the formula =5/2+1

cheers

Whichever Worksheet you want this code to work in, you need to double-click that Sheet as per my image and paste the code in there.

 

[juddaaaa]

You will also need to change the name back to Worksheet_Change

 

[honkin]

Whichever Worksheet you want this code to work in, you need to double-click that Sheet as per my image and paste the code in there.

OK, I initially created it as a New Module, my apologies. The file in question has 10 worksheets in it and that code has now been pasted into each, as I need it to work in . The file has been saved as a macro enabled file and when it is opened, Enable Macros is selected. It now generates an error message

You will also need to change the name back to Worksheet_Change

The name was already corrected in all of them. When I created it as a New Module, I named it Starting_Price, but in all the worksheets, it is what you initially had. Each worksheet was double clicked and the entire code pasted and saved. Not sure what the Option Explicit showing above your code in the video is, as I don't see that at all when I do it.

When I look at the video, this procedure is not going to work for what it is I want. I actually do not wish to type the formula =5/2, nor any formula. I wish only to have to type the fraction, 5/2, as indicated in my original post. I mean, it saves but a fraction of a second to type =5/2 over =5/2+1 so I would prefer to look at something else that works when just the fraction is typed, rather than the formula. This is going to be given to a non-English speaker and I don't wish t have to explain formulas to them. They will be copying the fraction from a website, so that will be simple for them to emulate, if that makes sense

Is it at all possible for Excel to do what I want with just a fraction?

Thanks so much for your patience

 

[juddaaaa]

OK. Try this. You will need to format all of column P as Text so that Excel doesn't convert to dates or numbers.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim re As Object
  
    On Error GoTo err_handler
  
    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler
  
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^[0-9]+\/[0-9]+$"
  
    If Not Intersect(Target, Columns("P")) Is Nothing Then
        With Target
            If re.test(.Formula) Then
                .Formula = Evaluate(.Formula & "+1")
            End If
        End With
    End If

exit_handler:
    Application.EnableEvents = True
  
Exit Sub

err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_handler
  
End Sub

 

[honkin]

OK. Try this. You will need to format all of column P as Text so that Excel doesn't convert to dates or numbers.

Thanks for your reply juddaaa but now when I type 5/2, 5/2 is what is in the cell and the active X error box pops up again.

cheers