Dynamic Listing

[todd Huttenstine]

Hey guys,

Its been a while. Hope everyone is doing well. Anyway
here is what I need. I need a code that will look in cell
AA3, AJ3, AS3, BB1, etc... (this is every other 9th cell
going towards the right until the end of the
spreadsheet). I need the code to look in all these cells
and then list the cell values in a vertical range starting
in cell Y4.


Thank you

Todd Huttenstine

 

[Tom Ogilvy]

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).Address
Next

End Sub

 

[Frank Kabel]

Hi
try
Sub foo()
Dim index As Integer
For index = 0 To 25
Cells(4 + index, "Y").Value = _
Cells(3, 27 + (9 * index)).Value
Next
End Sub

 

[Todd Huttenstine]

Thanx.

-----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).Address
Next

End Sub


--
Regards,
Tom Ogilvy




.

 

[Todd Huttenstine]

hey Tom

What is the significance of 25 in the 0 to 25 part of the
code?

And also I dont unmderstand the (0, i * 9) in the below
part of the code. Can you please tell me how this works?

rng1.Offset(i, 0).Value = _
Rng.Offset(0, i * 9).Value

 

[Tom Ogilvy]

Change Address to value - that was for testing

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

 

[Tom Ogilvy]

0 to 25 takes you from AA3 to IR3 (can't go past that)

you want count by 9 going across the columns, so i*9 does that.

Regards,
Tom Ogilvy