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

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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 expressed precisely :










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
Click to expand...

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 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
Click to expand...

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
 
Howard formulated on Monday :





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
Click to expand...

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

Howard
 
Back
Top