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?

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?

Don't use that nasty selecting

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


--

HTH

Bob

"Doug Robbins - Word MVP" wrote in message
...
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?

This seems a little more straightforward:

Range("a2").Formula = "=CODE(A1)"
Range("a2").Copy Range("b2:e2")


"peter" wrote:

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?
.

Apologies - once again I'm guilty of misreading what you're really trying to
do, and making an idiot of myself in process. But still, I don't see the
advantage of all the RC style reference here. Why not just this?:

For i = 1 To 5
Cells(2, i).Formula = "=CODE(MID(A1, " & i & ", 1))"
Next i


"B Lynn B" wrote:

This seems a little more straightforward:

Range("a2").Formula = "=CODE(A1)"
Range("a2").Copy Range("b2:e2")


"peter" wrote:

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?
.