Use

Dim i As Long
For i = 1 To 5
ActiveCell.FormulaR1C1 = "=CODE(MID(R[-1]C1,LEN(R[-1]C1)-(LEN(R[-1]C1)-"
& i & "),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"peter" wrote in message
...
Fellow excel programmers,

I have the following little program which gives me an error:

Sub code()
'
' code Macro
'
' Keyboard Shortcut: Ctrl+o
'
For i = 1 To 5
ActiveCell.FormulaR1C1 =
"=CODE(MID(R[-1]C1,LEN(R[-1]C1)-(LEN(R[-1]C1)-
i),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
Next i
End Sub

The problem is that when I run this code it gives me a "VALUE"
error.
The computer claims that the "i" in this part of the code
(LEN(R[-1]C1)- i),1)) is what causes the value error.

I am just teaching VB to myself so I am not that great. The code above
therefore
might not make much sense. Therefore, let me explain what I wanted to
accomplish.

I want the program to start at cell A2, then look at the string in cell
A1,
then
convert the first character of the string to it's ASCII number, then move
over
to cell B2, repeat the process for the second letter... 5 times. In the
end my
output will be 5 cells immediately below the string lined by from A2 to
E2,
which give me the number values of each of the first five characters of
the
string.

I hope this is clear.

I THOUGHT I could use the counter "i" inside:
,"LEN(R[-1]C1)-(LEN(R[-1]C1)- i", to tell the computer which
letter
to
look at. This code should give me:

i = 1
len(word) - (len(word) - 1) = first letter
mid(first letter)
code(mid(first letter)

loop back

i = 2
len(word) - (len(word) - 2) = second letter
mid(second letter)
code(mid(second letter)

loop back...etc 3 more times.

however...this is not working...

could someone please explain why and suggest how I could fix this?

Please note that since I am not great at programming, the way I make my
code
is
by recording a macro for the first iteration and then surrounding the code
created by my recording with a for loop and, in this case, replacing
len(word) - (Len(word) - 1 with
len(word) - (Len(word) - i

in order to tell excel to automatically look at the next letter in the
word.

So, could anyone offer help?