Selecting a Pattern of Cells
 

What would a formula for a macro look like for selecting a specific pattern
of cells within a column. For example:

I would like to select every 7th cell of Column A. What I mean by that is,
select cell A7, and A14, A21, A28, A35, etc on down the line. Make
Sense???

Then I would like to follow up that selection by taking them all, cutting
them and moving them UP three rows, and Over to the right 4 columns. That
make sense??? I'm not having much luck gettin anywhere on this one.
Thanks!!!

Selecting a Pattern of Cells
 

Sub MoveCellData()
For i = 7 To 70 Step 7
Set r = ActiveSheet.Range("a" & i)
Set s = r.Offset(-3, 4)
s.Value = r.Value
r.Value = ""
Next i
End Sub


--
C01d
------------------------------------------------------------------------
C01d's Profile: http://www.excelforum.com/member.php...o&userid=34422
View this thread: http://www.excelforum.com/showthread...hreadid=549098

Selecting a Pattern of Cells
 

Sub MoveCellData()
For i = 7 To 70 Step 7
Set r = ActiveSheet.Range("a" & i)
Set s = r.Offset(-3, 4)
s.Value = r.Value
r.Value = ""
Next i
End Sub


--
C01d
------------------------------------------------------------------------
C01d's Profile: http://www.excelforum.com/member.php...o&userid=34422
View this thread: http://www.excelforum.com/showthread...hreadid=549098

Selecting a Pattern of Cells
 

If you're really looking to cull items from a list by manually copying
and pasting, versus some advanced filter macro, or some other
aggregating function on another tab, here's my suggestion.

In a column adjacent to your data, create a function that turns your
criteria into a 1 or 0. So in your example, if you were to look for
every 7th item, use the if function, the mod function and the row()
function to look for a remainder of 0. Assign that IF outcome to 1 and
the rest to zero. Then use an autofilter and filter the dataset on the
column you created to only display those items "flagged" with a 1. Now
you can copy and paste that data anywhere and only those rows
corresponding to "1" would get pasted.

bodhisatvaofboogie wrote:
What would a formula for a macro look like for selecting a specific pattern
of cells within a column. For example:

I would like to select every 7th cell of Column A. What I mean by that is,
select cell A7, and A14, A21, A28, A35, etc on down the line. Make
Sense???

Then I would like to follow up that selection by taking them all, cutting
them and moving them UP three rows, and Over to the right 4 columns. That
make sense??? I'm not having much luck gettin anywhere on this one.
Thanks!!!

Selecting a Pattern of Cells
 

To do the selection:

Sub test()
Dim r, r1 As Range
Set r1 = Range("A7")
For i = 14 To 140 Step 7
Set r1 = Union(r1, Cells(i, 1))
Next
MsgBox (r1.Address)



For Each r In r1
'
'
'
'enter your stuff
'
'
'
Next
End Sub

The first part defines the range. The second part allows processing for
each cell in that range.
--
Gary''s Student


"bodhisatvaofboogie" wrote:

What would a formula for a macro look like for selecting a specific pattern
of cells within a column. For example:

I would like to select every 7th cell of Column A. What I mean by that is,
select cell A7, and A14, A21, A28, A35, etc on down the line. Make
Sense???

Then I would like to follow up that selection by taking them all, cutting
them and moving them UP three rows, and Over to the right 4 columns. That
make sense??? I'm not having much luck gettin anywhere on this one.
Thanks!!!