Wee programming query
 

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 a

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

--
Message posted from http://www.ExcelForum.com

Wee programming query
 

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

"sparky3883 " wrote in message
...
Hey All

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

I have a template spreadsheet which contains 10 colums (A - J) and 40
Rows. I have created most of my coding using Visual Basic Editor and
wanted to add one more bit of coding but can't quite figure it out and
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 in
Column C and have that letter represented by a certain number in the
same row but in Coulmn D.

For example;
If i enter the letter 'E' into Column C, Row 33, i would like the
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 if
it is possible to enter some coding where it will be able to do it for
me to save time.

The letters and numbers i need representing a

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

So, if i enter K into a certain Row in column C, it'll automatically be
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 help


---
Message posted from http://www.ExcelForum.com/

Wee programming query
 

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" wrote in message
...
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


<snip

Wee programming query
 

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

"Dana DeLouis" wrote in message
...
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" wrote in message
...
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


<snip