Automatically change text to number

[honkin]

Hi
I have some code which is in the objects are of various workbooks. This code effectively changes fractional odds to decimal, so 7/2 becomes 4.50 and so on.

Here is the code

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("Y")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      If (.Value - Fix(.Value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
      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

That works really well, but I now need to change various text possibilities as they happen. So there will be things like pu, F, ro, ur and so on and each has a corresponding number. So I was after something similar to the above where it is input directly into the worksheet's object area, so if someone types pu in column AA, it automatically changes to 509.

Here is a list of the codes and the corresponding number. The few capital letters are deliberate

500 NR

501 ur

502 co

503 r

504 ro

505 bd

506 su

507 L

508 W

509 pu

512 F

514 D

515 hr

516 rr

517 dnf

I need the code to be changed into the number once it is typed, just as the fractional odds change as soon as you move cell. I hope it is possible

Thanks in advance

 

[gordsky]

Hi
I have some code which is in the objects are of various workbooks. This code effectively changes fractional odds to decimal, so 7/2 becomes 4.50 and so on.

Here is the code

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("Y")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      If (.Value - Fix(.Value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
      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

That works really well, but I now need to change various text possibilities as they happen. So there will be things like pu, F, ro, ur and so on and each has a corresponding number. So I was after something similar to the above where it is input directly into the worksheet's object area, so if someone types pu in column AA, it automatically changes to 509.

Here is a list of the codes and the corresponding number. The few capital letters are deliberate

500 NR

501 ur

502 co

503 r

504 ro

505 bd

506 su

507 L

508 W

509 pu

512 F

514 D

515 hr

516 rr

517 dnf

I need the code to be changed into the number once it is typed, just as the fractional odds change as soon as you move cell. I hope it is possible

Thanks in advance

How have the corresponding numbers been set? Slightly long winded but you could use a case statement on a sheetchange event

 

[honkin]

How have the corresponding numbers been set? Slightly long winded but you could use a case statement on a sheetchange event

Cheers gordsky

I don't really know what you mean by set. The column AA is initially blank and in that column, numbers are generally input which relate to the finishing position of a horse. Sometimes the horse had an unusual event take place and the result will then show one of the above text codes. I need those codes to change into the corresponding number; so as an example, if the person types ur, then that changes to 501, or if they type F, it changes to 512. Does that make more sense?

Yes, the original code is a change event which was supplied by someone here on this forum, so that appears to be the way to do it, but the coding of this is beyond my meagre skills.
The original one I showed as an example, simply changes the format of the number in column Y, so this time the change will be for column AA and will need to change from the text, to the corresppnding number

I hope this helps

cheers