Displaying the column letter

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

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
 
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)
 
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
 
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?
 
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)
 
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
 
Back
Top