ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

Why won't subject line increment A B C D etc. in code. Should increment the number of times as entered in InputBox but instead returns all A's below A3.

Option Explicit

Sub MakeRanger()
Dim CNum As String
Dim i As Long

CNum = InputBox(" Enter a number.", _
"a Number")

i = CNum

Range("A3").Select
For i = 1 To i - 1
ActiveCell.Offset(0, i) = i
ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62)
Next
End Sub

Thanks,
Howard

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

Howard expressed precisely :
Why won't subject line increment A B C D etc. in code. Should increment the
number of times as entered in InputBox but instead returns all A's below A3.

Option Explicit

Sub MakeRanger()
Dim CNum As String
Dim i As Long

CNum = InputBox(" Enter a number.", _
"a Number")

i = CNum

Range("A3").Select
For i = 1 To i - 1
ActiveCell.Offset(0, i) = i
ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62)
Next
End Sub

Thanks,
Howard

Howard,
Your cell of origin remains constant and so is always the ref for each
iteration of your loop. IOW, ActiveCell.Row+62 doesn't change the
character returned by Chr()! Thus every row will contain the same
character. You'll have to match the row offset so Chr() increments with
each row...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

On Monday, December 24, 2012 6:57:06 PM UTC-8, GS wrote:
Howard expressed precisely :

Why won't subject line increment A B C D etc. in code. Should increment the

number of times as entered in InputBox but instead returns all A's below A3.



Option Explicit



Sub MakeRanger()

Dim CNum As String

Dim i As Long



CNum = InputBox(" Enter a number.", _

"a Number")



i = CNum



Range("A3").Select

For i = 1 To i - 1

ActiveCell.Offset(0, i) = i

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62)

Next

End Sub



Thanks,

Howard



Howard,

Your cell of origin remains constant and so is always the ref for each

iteration of your loop. IOW, ActiveCell.Row+62 doesn't change the

character returned by Chr()! Thus every row will contain the same

character. You'll have to match the row offset so Chr() increments with

each row...



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Thanks Gary, I could have sworn I tried the before but I guess not.
This works:

ActiveCell.Offset(i, 0) = Chr(64 + i)

Merry Christmas and thanks again.

Howard

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

Howard formulated on Monday :
Thanks Gary, I could have sworn I tried the before but I guess not.
This works:

ActiveCell.Offset(i, 0) = Chr(64 + i)

Merry Christmas and thanks again.

Howard

Howard, Merry Christmas to you too!

I actually found this to work...

Sub MakeRanger2()
Dim lCount&, i&
ActiveSheet.UsedRange.ClearContents

lCount = InputBox(" Enter a number.", _
"a Number")

With Range("A3")
For i = .Row To .Row + lCount - 1
Cells(i, 1).Offset(0, 1) = i
Cells(i, 1) = Chr(Rows(i).Row + 62)
Next
End With
End Sub

...which returns the following results which I understand is what you
want...

A 3
B 4
C 5
D 6
E 7

...where the number input was 5.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

On Monday, December 24, 2012 7:32:37 PM UTC-8, GS wrote:
Howard formulated on Monday :

Thanks Gary, I could have sworn I tried the before but I guess not.

This works:



ActiveCell.Offset(i, 0) = Chr(64 + i)



Merry Christmas and thanks again.



Howard



Howard, Merry Christmas to you too!



I actually found this to work...



Sub MakeRanger2()

Dim lCount&, i&

ActiveSheet.UsedRange.ClearContents



lCount = InputBox(" Enter a number.", _

"a Number")



With Range("A3")

For i = .Row To .Row + lCount - 1

Cells(i, 1).Offset(0, 1) = i

Cells(i, 1) = Chr(Rows(i).Row + 62)

Next

End With

End Sub



..which returns the following results which I understand is what you

want...



A 3

B 4

C 5

D 6

E 7



..where the number input was 5.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Thanks, I will play with that, looks pretty good.

Howard

ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's
 

You're welcome. Always glad to help when/where I can...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion