none
Array with cells in each nth row RRS feed

  • Question

  • Hi guys,

    I need to define anarray beginning in specified cell (let's say A12) and consisting of each 3rd cell until specified row (lets's say A90). So that would be A12, A15, A18 ...

    Is it possible? and if so how should I approach this problem? 

    thanks in advance for help!



    • Edited by dawid_m Wednesday, February 20, 2013 9:58 AM
    Wednesday, February 20, 2013 8:48 AM

All replies

  • Not sure why you need an array - that would have been a good point to make - but here is how to do it:

    Sub TestMacro()
    Dim rngC() As Variant
    Dim lngRow As Long
    Dim i As Integer

    ReDim rngC(1 To 1)

    For lngRow = 12 To 90 Step 3
    rngC(UBound(rngC)) = Cells(lngRow, 1).Value
    ReDim Preserve rngC(1 To UBound(rngC) + 1)
    Next lngRow

    ReDim Preserve rngC(1 To UBound(rngC) - 1)

    For i = LBound(rngC) To UBound(rngC)
    MsgBox "Element #" & i & " is " & rngC(i)
    Next i

    MsgBox "The sum of the array is " & Application.Sum(rngC)
    End Sub

    Wednesday, February 20, 2013 2:39 PM
  • If you need by formula:

    =SUM(IF(MOD((ROW(A12:A30)-12),3)=0,A12:A30,""))

    Where A12:A30 is total cell.This is array formula (press Ctrl+Shift+Enter after pasting) and will show you sum of A12,A15,A18....


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, February 21, 2013 6:12 AM
    Answerer