Letter/Number Representation

[sparky3883]

Hello

I am having a wee problem with some coding and was wondeing if anyon
is able to give me a bit of a hand please.

I have a sheet with two columns (B and C) and they both run from row
to row33.

What i am trying to do, is, when you input a letter into any cell i
Column B, a number (which represents the letter) is shown in the cel
next to it in Coulmn C.
The two letters/numbers are:
E = 7.8
L = 8

(Example: If you type the letter E into B26, the number 7.8 is shown i
C26)

I have tried a few codes, mainly using the If/Then procedure, but, as
am rather new to all of this, I seem to be struggling in making thi
work.

Is there anybody out there who could please shed some light on thi
situation

Many Thank

 

[Frank Kabel]

Hi
enter the following in C8:
=IF(B8="E",7.8,IF(B8="L",8,""))
and copy down to row 33

 

[sparky3883]

Hi Frank

Many thanks for your reply.
I was just wondering if there was another way of doing it without usin
a formula. what i would prefer to do is use some VB coding beacuse soo
I will be adding alot more letter/number representations and the res
of my excel document is supported by VB coding instead of formulas.

I appreciate your help and would be ever so grateful if you, or anyon
else, could please help me with some VB coding for this query that
have.

Many thanks agai

 

[Frank Kabel]

Hi
this depends on what you want
- a user defined function which counts / sums your letters
- an automatic replacement of the letters by an event procedure
So please give some more information about your requirements


IMHO I would prefere a formula over VBA as in most cases worksheet
functions are faster and simplier to use -> but that's just a personal
opinion.

 

[Gord Dibben]

sparky

Worksheet Event Code will do if you want VBA

Right-click on sheet tab and "View Code". Copy/paste in there.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("B:B"))
n = Target.Row
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the range
Select Case rng.Value
Case "E": Num = 7.8
Case "L": Num = 8
Case "Q": Num = 8.4
End Select
'Apply the Number
Excel.Range("C" & n).Value = Num
Next rng
End Sub

Gord Dibben Excel MVP

 

[sparky3883]

Hi again Frank,
and thanks again for your reply.

I'm guessing what i really want is
'a user defined function which counts / sums your letters'.

Basically, it is for a rota that i have ceated for work.
The shift that the member of staff is going to be working will be pu
into column B and the amount of hours that they are actually workin
(according to the rules of the company) will be shown in column C.
No specific letter will be placed in a specific cell, so ideally, i a
looking for some coding that will be applicable to all the cells in th
required columns (B and C), and if the letter is removed or replace
from a cell in column B, the number it represents needs to be replace
(or removed) from column C

Many thanks again for all your help, i certainly appreciate it

 

[Frank Kabel]

Hi
put the following UDF function in one of your standard modules:
Public Function ret_character_values(rng As Range)
Dim ret_value
ret_value = 0

If rng.Cells.count > 1 Then
ret_character_values = CVErr(xlErrValue)
Exit Function
End If
Select Case rng.Value
Case ""
ret_value = 0
Case "E"
ret_value = 7.8
Case "L"
ret_value = 8#
'.. further characters
Case Else
ret_value = CVErr(xlErrValue)
End Select
ret_character_values = ret_value
End Function


Now you can put the following formula for example in C1:
=RET_CHARACTER_VALUES(B1)
and copy this down.

You can easily adapt your other character codes in this function (with
additional case statements)