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)

"Todd Huttenstine" wrote in message
...
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
-----Original Message-----
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"
wrote in message
...
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



.