Im trying to shift every element down one spot in my array when a new element is added to the array in a non-empty spot

Question

                   For i = LBound(arr, 1) To UBound(arr, 1)

                        For j = LBound(arr, 2) To UBound(arr, 2)                                 

                            If arr(i, j) <> "" And arr(i, j) <> ActiveCell.Value Then

                                NewVal = ActiveCell.Value
                                ElemId = k
                                For k = UBound(arr) To ElemId + 1 Step -1
                                arr(k, j) = arr(k-1, j)
                                Next k
                                arr(ElemId) = NewVal                                         

                            End If

Basically what the code does is check if a row in the array has a different value than the row in a sheet's column I am iterating through. If so, I want to replace the value of the cell in the array's row with the new value from the sheet's row and then move the original value of the array's row down one spot along with every other value under that row.

`arr(i, j) = ActiveCell.Value` this appears to set every element of your array to the value of the active cell.... is that really what you want, or do you just want the array to contain what's in the sheet? — Absinthe, Mar 09 '21 at 16:17
@absinthe this line was actually working fine. It simply added the one cell from the row in the sheet into the one "cell" of the array. However, when this happens, it has been taking the place of another value that was already in that "cell" in the array, which is good, but I want that original value that was in the array to move down one spot along with all the other values below it. — coder101, Mar 09 '21 at 16:26
How are you changing the active cell? Also, it's best avoided in favour of direct cell references e.g. `Cells(1, 2)` or `Range("A2")` — Absinthe, Mar 09 '21 at 16:26
FYI you can read a whole range of cells into an array in one go: `arr = Range("A1:A10").value` — Absinthe, Mar 09 '21 at 16:29
@Absinthe I am aware of this, however, I need to do it this way. — coder101, Mar 09 '21 at 16:35
`i` loops to `UBound(arr, 1)` but inside your loop you reference (eg) `arr(i + 4, j)` which is going to give you an out-of-bounds error. — Tim Williams, Mar 09 '21 at 17:01
@timWilliams in other part of my code that is not shown. I made the array 50 rows and 50 columns. So no it does not go out of bounds — coder101, Mar 09 '21 at 17:20
So you exit your loop before `i` reaches `UBound(arr, 1)` ? Maybe it would be useful to show a little more of your code so we can get some context. — Tim Williams, Mar 09 '21 at 17:27
I am just trying to find the algorithm to fix the specific issue I am having, which is to move the other rows of the array down one spot. We already know that my array is big enough — coder101, Mar 09 '21 at 17:48
I am just trying to build an answer. Do I understand correctly 1.) that you start off with an array in memory (not in the sheet) and you want to manipulate it in memory in regard to the value of "ActiveCell" which does not change during the runtime of your program and 2.) that you want to move down all values in one column (j) if a certain value (i,j) does not match the value in ActiveCell? If not, you might want to add a sample screenshot or listing with before - after states. — Wolfgang Jacques, Mar 09 '21 at 19:20
@WolfgangJacques Yes so the array is in memory, not on a sheet. There is already a column full of data (about 30 rows are filled). Basically I iterate through a sheet's column that should have the exact same number of rows with the exact same data as my array BUT if I find a value in one of the rows in the sheet's column that has a different value than the same row in the array (For example: row 5), then I want to add the value from the 5th row of the sheet's column onto row 5 of the array and then the values 5 to 30 that were originally in the array, must drop down one row — coder101, Mar 09 '21 at 23:35

Wolfgang Jacques · Answer 1 · 2021-03-10T09:40:16.573

It is not completely clear to me what you want to achieve but this solution works for these test values and the second column:

In your code I missed moving the selection in the sheet which I included here but it would be better to refer to cells in the sheet by the range rather than selecting cells anyway.

In the code I also included a method to print out the array after each step so you can check.

    Option Explicit
    Sub test()
    Dim arr As Variant, _
        mycell As Range, _
        i, j, k As Long

        arr = Range("F1:I8").Value
        Debug.Print "Starting Point"
        PrintArray arr
        Do
            For i = 2 To 2 'Just column 2 for now! normal: LBound(arr, 2) To UBound(arr, 2)
                Range("A1").Select
                For j = LBound(arr, 1) To UBound(arr, 1) 'Rows in that column
                    If arr(j, i) <> ActiveCell.Value Then
                        'shift down current column in array
                        For k = UBound(arr, 1) To j + 1 Step -1
                            arr(k, i) = arr(k - 1, i)
                            PrintArray arr
                        Next k
                        arr(j, i) = ActiveCell.Value
                        PrintArray arr
                    End If
                    ActiveCell.Offset(1, 0).Select
                    If ActiveCell = "" Then Exit For
                Next j
            Next i
        Loop While ActiveCell <> ""
    End Sub

    Sub PrintArray(a As Variant)
    Dim i, j As Long, _
        line As String

        For i = LBound(a, 1) To UBound(a, 1)
            line = ""
            For j = LBound(a, 2) To UBound(a, 2)
                If a(i, j) = "" Then
                    line = line + "--- "
                Else
                    line = line + Format(a(i, j), "000") + " "
                End If
            Next j
            Debug.Print line
        Next i
        Debug.Print
    End Sub

Output:

Starting Point
002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 012 016 --- 
010 015 020 --- 
012 018 024 --- 
--- --- --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 012 016 --- 
010 015 020 --- 
012 018 024 --- 
--- --- --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 012 016 --- 
010 015 020 --- 
012 018 024 --- 
--- 018 --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 012 016 --- 
010 015 020 --- 
012 015 024 --- 
--- 018 --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 012 016 --- 
010 012 020 --- 
012 015 024 --- 
--- 018 --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 009 012 --- 
008 009 016 --- 
010 012 020 --- 
012 015 024 --- 
--- 018 --- --- 
--- --- --- --- 

002 003 004 --- 
004 006 008 --- 
006 777 012 --- 
008 009 016 --- 
010 012 020 --- 
012 015 024 --- 
--- 018 --- --- 
--- --- --- ---

Thanks for your attempt but it did not work. I made my question more clear in another post I just made. https://stackoverflow.com/questions/66572216/my-array-fails-to-replace-and-offset-values — coder101, Mar 10 '21 at 20:15

Im trying to shift every element down one spot in my array when a new element is added to the array in a non-empty spot

1 Answers1