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]

Are you on a Mac by any chance?

If so we'll have to get rid of the RegEx check

Private Sub Worksheet_Change(ByVal Target As Range)
 
    On Error GoTo err_handler
 
    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler
 
    If Not Intersect(Target, Columns("P")) Is Nothing Then
        With Target
            .Formula = Evaluate(.Formula & "+1")
        End With
    End If

exit_handler:
    Application.EnableEvents = True
 
Exit Sub

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

 

[honkin]

Are you on a Mac by any chance?

If so we'll have to get rid of the RegEx check

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo err_handler

    Application.EnableEvents = False
    If Target = Empty Then GoTo exit_handler

    If Not Intersect(Target, Columns("P")) Is Nothing Then
        With Target
            .Formula = Evaluate(.Formula & "+1")
        End With
    End If

exit_handler:
    Application.EnableEvents = True

Exit Sub

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

End Sub

 

[honkin]

Nailed it @juddaaa. Apologies for not mentioning it. Yes, it is a mac and that code now works perfectly. When a fraction is input, it converts to a decimal +1

Thanks so much for your patience

 

[Peter_SSs]

Apologies for not mentioning it. Yes, it is a mac

Update your forum Account details then the information will always be available to helpers.

 

[juddaaaa]

Nailed it @juddaaa. Apologies for not mentioning it. Yes, it is a mac and that code now works perfectly. When a fraction is input, it converts to a decimal +1

Thanks so much for your patience

You're welcome

 

[honkin]

You're welcome

Howdy @juddaaaa

After now using the sheet for all of yesterday and this morning, something occurs which may see me have to revert to perhaps creating a macro to work on the cells after the fraction is input.

So when I delete a row or copy and paste anything into other cells, I immediately get a 13: Type Mismatch error message. The VBA code is only addressing the particular column in the code, so not sure why this now pops up. Here it is:-

So there are times I need to delete an entry entirely. This is done by highlighting the row and deleting. This generates the message. Some other cells next to the cell which the code is working on have formulas in them to calculate profit and loss as well as ongoing profit or loss. As the results for the previous day are input, those formulas are copied and pasted down to reflect the day's profit or loss. This also generates the message. The cells with the formulas are formatted as numbers with 2 decimal places, so not sure why any mismatch would occur. If I delete a cell the message comes up and if I do Cmd + Z to undo, the message also comes up. Where it doesn't come up is any of the cells to the left of the column that the code is working on...P

Any thoughts at all? Oh, just started selection for this current day and as I pasted the selection in, the message also popped up. These selections go in to the left of column P. I am at a loss as to why, as the VBA code is only addressing column P

cheers

 

[honkin]

Update your forum Account details then the information will always be available to helpers.

View attachment 6226

cheers Peter_SS. Have done that

Regards

 

[honkin]

Hi @juddaaaa

I am still struggling with this one. I have opted to revert to the file without the .xlsm extension. This brings the file back to where it was before the VBA code. It just seemed to cause too many issues which may have taken too long to resolve.

On the same topic, though, is there a way to do the same thing with a standard macro, so without having to go with a Macro Enabled file? So basically, any cell with a fraction in it can have the macro run on it and the result will be the decimal value of the fraction +1

5/2 would be 3.50
7/2 would be 4.50
11/2 would be 6.50
15/8 would be 2.88

And so on.

The standard formula to do that is =Fraction+1

I have tried recording a macro which takes a a selected cell with a fraction in it and adds = at the start and +1 at the end, but with no result. If I format the cell as Number, the fraction remains OK, but the macro does nothing. If I format the cell as General I get a date etc. So I can't see how to format the cell to hold the fraction correctly and have the macro work.

Oh, just looked at the macro and I can see why it keeps showing the result of 5/2. That was what was already in the cell when I recorded the macro. I thought that by just adding = at the start and +1 at the end, it would record it that way and give the fraction a wildcard or some such

Here is what was written when I recorded the macro

Sub ConvertFraction()
'
' ConvertFraction Macro
'

'
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=5/2+1"
    Range("H3212").Select
End Sub

So I was just playing around with how to do this and obviously simply recording the macro using a cell with 5/2 and adding = to the start and +1 to the end is not right. The actually column will always be P, not H. So the person who will get this sheet will basically input a fraction he finds on the web in a cell in column P. I'd then like him to be able to select all of the cells he has done this to on any given day and run a macro which will convert those fractions to the format explained above.

Is this going to be possible at all without going to a Macro Enabled file?

Thanks in advance

 

[juddaaaa]

OK.

I've fully tested this with everything that you've said is causing errors and have not experienced any errors

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo err_handler
  
  Application.EnableEvents = False
  
  If Target.CountLarge > 1 Then GoTo exit_handler
  If Target = Empty Then GoTo exit_handler
  
  If Not Intersect(Target, Columns("P")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      .NumberFormat = "0.00"
    End With
  End If
  
exit_handler:
  Application.EnableEvents = True
Exit Sub
  
err_handler:
  MsgBox Err.Number & ": " & Err.Description
  Resume exit_handler
End Sub

You can download THIS FILE to see it working

 

[honkin]

OK.

I've fully tested this with everything that you've said is causing errors and have not experienced any errors

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo err_handler
 
  Application.EnableEvents = False
 
  If Target.CountLarge > 1 Then GoTo exit_handler
  If Target = Empty Then GoTo exit_handler
 
  If Not Intersect(Target, Columns("P")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      .NumberFormat = "0.00"
    End With
  End If
 
exit_handler:
  Application.EnableEvents = True
Exit Sub
 
err_handler:
  MsgBox Err.Number & ": " & Err.Description
  Resume exit_handler
End Sub

You can download THIS FILE to see it working

Hey @juddaaaa

That is absolutely brilliant. It appears to work fine.

One tiny thing and it is very small; is there a way for whole numbers to not have 2 decimal places; so 12 rather than 12.00? The person who will eventually be using this file will be inputting fractions, even though some of them will end up being whole numbers; 6/1, 12/1 and so on. The same rule applies where the result would be as if the formula =6/1+1 was typed, but I have not been able to find a format where whole numbers do not have 2 decimal places added.

If I use General, it naturally treats the input as a date, so tried, different cell formats to try and get it to work. It outputs correctly in many of them, but still outputs 2 decimal places for a whole number.

Other than that, it is brilliant

Thanks so much for your time and effort.

Regards