Wee programming query

S

sparky3883

Hey All

I am having a wee programming problem and was wondering if anyone coul
please help me.

I have a template spreadsheet which contains 10 colums (A - J) and 4
Rows. I have created most of my coding using Visual Basic Editor an
wanted to add one more bit of coding but can't quite figure it out an
was wondering if someone could please help me.

Here's my prob!

I am wanting to be able to enter a certain letter into any cell i
Column C and have that letter represented by a certain number in th
same row but in Coulmn D.

For example;
If i enter the letter 'E' into Column C, Row 33, i would like th
number 7.8 to be shown in Column D Row 33.

At the moment i have to enter it all manually and was just wondering i
it is possible to enter some coding where it will be able to do it fo
me to save time.

The letters and numbers i need representing are:

E = 7.8
L = 8
K = 4
Q = 6

So, if i enter K into a certain Row in column C, it'll automatically b
shown as 4 in the same Row but in column D.

Is this possible to do as it will save me time.

Many thanks in advance for your hel
 
D

Doug Glancy

For a non-programming alternative you could take a look at the Vlookup
function. Using it you could create a simple lookup table that will do what
you want.

For a VBA solution, paste the following into the code module for the
worksheet (right-click the sheet tab, choose View Code and paste it there):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

If Target.Column = 3 Then
Set r = Target.Offset(0, 1)
Select Case Target
Case "E"
r = 7.8
Case "L"
r = 8
Case "K"
r = 4
Case "Q"
r = 6
End Select
End If

End Sub

hth,

Doug
 
D

Dana DeLouis

I was experimenting with something related. Just something different...

Select Case Target
Case "E", "L", "K", "Q"
rng = (23447120 Mod (Asc(Target) + 10)) / 10
Case Else
' ??

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Doug Glancy said:
For a non-programming alternative you could take a look at the Vlookup
function. Using it you could create a simple lookup table that will do what
you want.

For a VBA solution, paste the following into the code module for the
worksheet (right-click the sheet tab, choose View Code and paste it there):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

If Target.Column = 3 Then
Set r = Target.Offset(0, 1)
Select Case Target
Case "E"
r = 7.8
Case "L"
r = 8
Case "K"
r = 4
Case "Q"
r = 6
End Select
End If

End Sub

hth,

Doug
Click to expand...

<snip>
 
D

Doug Glancy

Hey Dana,

Very cool! I understand the code, but I'm curious to understand the thought
process (backwards reasoning?) that got you there. I don't really see any
relationship between these letters and numbers.

I also tweaked my code a little so that it handles ranges of more than one
cell. Also, if a cell with one of the 4 values is cleared or changed the
corresponding numbers are cleared:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim c As Range

For Each c In Target
If c.Column = 3 Then
Set rng = c.Offset(0, 1)
Select Case c
Case "E", "L", "K", "Q"
rng = (23447120 Mod (Asc(c) + 10)) / 10
Case Else
rng.Clear
End Select
End If
Next c

End Sub

Doug
 
Top