Displaying the column letter
 

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

Displaying the column letter
 

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

Displaying the column letter
 

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



.

Displaying the column letter
 

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?

-----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



.

Displaying the column letter
 

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



.

Displaying the column letter
 

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

-----Original 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



.

.