Displaying the column letter

[Todd Huttenstine]

Below is a code that Tom Olgilvy game me. It puts certain
values in a vertical range starting in cell Y4. What I
would like in addition is for it to put the actual column
letter of the corresponding value in column X startin
gwith cell 4.

Sub Copy9th()
Set rng = Range("AA3")
Set rng1 = Range("Y4")
For i = 0 To 25
rng1.Offset(i, 0).Value = _
rng.Offset(0, i * 9).Value
Next

End Sub

 

[Bob Phillips]

Sub Copy9th()
Set rng = Range("AA3")
Set rng1 = Range("Y4")
For i = 0 To 25
rng1.Offset(i, 0).Value = _
rng.Offset(0, i * 9).Value
rng1.Offset(i, -1).Value = rng.Offset(0, i * 9).Address(False, False)
Next

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Hey thanx but is there anyway to make it just show the
column letter and not the number. I need only the column
not the address.

Thank you

 

[Todd Huttenstine]

Or I could just use a code that will look in that range
and clean up as in deleting only the numbers from the
values. Would this be easier?

 

[Bob Phillips]

As soon as I posted it I knew you would ask for that <vbg>.

So here it is.


Sub Copy9th()
Set rng = Range("AA3")
Set rng1 = Range("Y4")
cLen = Len(rng.Address(False, False)) - 1
For i = 0 To 25
rng1.Offset(i, 0).Value = _
rng.Offset(0, i * 9).Value
rng1.Offset(i, -1).Value = Left(rng.Offset(0, i * 9).Address(False, False),
cLen)
Next

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rick]

Maybe this will do what you want. Modify it to suit with
the text.

Sub Every9thColumn()

Dim i As Integer
Dim rng As Range, rng1 As Range
Set rng = Range("AA3")
Set rng1 = Range("Y4")

For i = 0 To 25 'Do not exceed 25
rng1.Offset(i, 0).Value = _
rng.Offset(0, i * 9).Value & _
" at Column " & _
Mid(rng.Offset(0, i * 9).Address, _
Len(rng.Offset(0, i * 9).Address) - 3, 2)
Next i

End Sub

Rick