VBA Sorting Nth Range not working

[beechum1]

I have a values in column C of stuff1, stuff2 and on down.

I have a value in cell A1.

I want to start in C1, and every Nth, where N is the value in cell A1, select and paste into column H.

My problem is that the selected cells don't start in C1, in fact, based on the value in A1, it starts in different cells in column C. AND, if i press the button twice, the returned values are different.

Private Sub CommandButton1_Click()
'Sub SelectEveryNthCell()
    Dim rRange As Range
    Dim rEveryNth As Range
    Dim lRow As Long
     
    With Sheet1
        Set rRange = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
    End With
     
    For lRow = 1 To rRange.Rows.Count Step Range("a1").Value
    
        If lRow = 1 Then
            Set rEveryNth = rRange(lRow, 1)
        Else
            Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
        End If
    Next lRow
     
    Application.Goto rEveryNth
    
    Selection.Copy
    
    ActiveSheet.Range("j1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False


End Sub

Link to source code
i've attached a sample.

[Roger Govier]

Hi

The code runs fine for me.
I would amend it slightly to switch off screen updating, and to clear the contents of column J before copying the new data range to J1.
Amended code shown below

Private Sub CommandButton1_Click()
'Sub SelectEveryNthCell()
    Dim rRange As Range
    Dim rEveryNth As Range
    Dim lRow As Long
    Application.ScreenUpdating = False
    With Sheet1
        Set rRange = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
    End With

    For lRow = 1 To rRange.Rows.Count Step Range("a1").Value

        If lRow = 1 Then
            Set rEveryNth = rRange(lRow, 1)
        Else
            Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
        End If
    Next lRow
    ActiveSheet.Columns("J:J").ClearContents
    Application.Goto rEveryNth

    Selection.Copy

    ActiveSheet.Range("j1").Select

    Selection.PasteSpecial Paste:=xlPasteValues, _
                           Operation:=xlNone, _
                           SkipBlanks:=False, _
                           Transpose:=False

    ActiveSheet.Range("A1").Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    'http://www.ozgrid.com/forum/showthread.php?p=111223
    'http://www.ozgrid.com/forum/showthread.php?t=91759
  
End Sub

[beechum1]

Thanks Roger,

I'm still getting the same results:

i.e.

A1= 1 works
A1= 2 starts at "stuff99" and every 2 on down
A1= 3 starts at "stuff79" and every 3 on down
A1= 4 starts at "stuff53" and every 4 on down
A1= 5 starts at "stuff31" and every 5 on down
A1= 6 starts at "stuff7" and every 6 on down
A1= 7 works

A1= 8 and more, the code works fine for me. I've yet to try it on another machine, but i must use this code on this machine, so i have to figure this out.

[shg]

Roger,

Please edit your post to add code tags.

[beechum1]

the final result, is that i want to have the value in A1 be the # of personnel reporting, and divy up the part to be made based on that value into each persons list of work, so i have to start with C1 as the first part to be made, not part 99 or 73 etc.

[Leith Ross]

Hello Beechum1,

This macro has been added to the attached workbook.

Private Sub CommandButton1_Click()

'Sub SelectEveryNthCell()
  Dim Cell As Range
  Dim R As Long
  Dim rRange As Range
  Dim rEveryNth As Range
  Dim lRow As Long
    
    R = 1
    
    With Sheet1
        Set rRange = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
    End With
     
    For lRow = 1 To rRange.Rows.Count Step Range("a1").Value
        If lRow = 1 Then
            Set rEveryNth = rRange(lRow, 1)
        Else
            Set rEveryNth = Union(rEveryNth, rRange(lRow, 1))
        End If
    Next lRow
    
    For Each Cell In rEveryNth
      Sheets("Sheet1").Cells(R, "J") = Cell
      R = R + 1
    Next Cell
    
End Sub

[beechum1]

So this is what i have figured out so far, but i need to assign some "ifs" based on the value in A1...

Private Sub CommandButton1_Click()

'Sub SelectEveryNthCell()
  Dim Cell As Range
  Dim R As Long
  Dim rRange As Range
  Dim rEveryNth As Range
  Dim lRow As Long
  
  Call Person_1
  Call Person_2
  
  End Sub

[beechum1]

I found this Here

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Address = "$A$1" Then 
        Select  Case Target.Value 
        Case 5 To 10: MyMacro (1) 
        Case Is > 10: MyMacro (2) 
        Case Else ' do nothing
        End Select 
    End If 
End Sub

But i can't figure out how this applies to what i'm doing exactly. I'm sure that i'll have to change the Case statements to:

Case 1
  Call Person_1

Case 2

  Call Person_1
  Call Person_2

Case else do nothing