Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A count on a repeating pattern every 14 cells | Excel Discussion (Misc queries) | |||
Copy cells in a specific pattern | Excel Worksheet Functions | |||
how do i add a pattern to cells | Excel Discussion (Misc queries) | |||
autofill cells without a pattern? | Excel Discussion (Misc queries) | |||
Pattern Matching with cells function | Excel Programming |