Using counter "i" in for loop gives error

[peter]

Fellow excel programmers

I have the following little program which gives me an error

Sub code(

' code Macr

' Keyboard Shortcut: Ctrl+

For i = 1 To
ActiveCell.FormulaR1C1 = "=CODE(MID(R[-1]C1,LEN(R[-1]C1)-(LEN(R[-1]C1)
i),1))&quot
ActiveCell.Offset(0, 1).Range("A1&quot.Selec
Next
End Su

The problem is that when I run this code it gives me a "VALUE&quot
error
The computer claims that the "i" in this part of the cod
(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 abov
therefor
might not make much sense. Therefore, let me explain what I wanted t
accomplish

I want the program to start at cell A2, then look at the string in cell A1
the
convert the first character of the string to it's ASCII number, then move ove
to cell B2, repeat the process for the second letter... 5 times. In the end m
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 th
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 lette
t
look at. This code should give me

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

loop bac

i =
len(word) - (len(word) - 2) = second lette
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 cod
i
by recording a macro for the first iteration and then surrounding the cod
created by my recording with a for loop and, in this case, replacin
len(word) - (Len(word) - 1 wit
len(word) - (Len(word) -

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

So, could anyone offer help?

 

[Doug Robbins - Word MVP]

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

 

[Bob Phillips]

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

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

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

 

[B Lynn B]

This seems a little more straightforward:

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

 

[B Lynn B]

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

This seems a little more straightforward:

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

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