VBA - Build a Two-Column Array By Looping Through one Array with a Specific Criteria and Selecting From Another Array

Question

My Excel data table looks like the one below:

Column1  Column 2
3        Blank
5        Blank
Blank    234
0        Blank
2        Blank
8        Blank
9        Blank
Blank    567
Blank    567
0        Blank
5        Blank
3        Blank
4        Blank
Blank    860
6        Blank
Blank    869
0        Blank
6        Blank
7        Blank

The term "blank" designates an empty cell. In the table, "0" is my anchor. I need to compare the last value in Column 1 above "0" with the last number in Column 2 above "0" by going through Column 1. For instance, I want to match "234" with "5" in two different output columns on the same row with respect to the first 0 in Column 1. My final data set should look like:

Column 3   Column 4 
5          234
9          567
6          869

I have some code but it addresses only Column 1, with no blanks. It is not even close. Thanks!

Sub Cat()
   
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Reference the 2nd source cell.
    Dim sCell As Range: Set sCell = ws.Range("V12").Offset(1)
    
    ' Reference the 1st destinatin cell.
    Dim dCell As Range: Set dCell = ws.Range("X12")
    
    Do Until IsEmpty(sCell.Value)
        If sCell.Value = 0 Then
            dCell.Value = sCell.Offset(-1).Value ' ... = previous source cell
            Set dCell = dCell.Offset(1) ' ... = next destination cell
        End If
        Set sCell = sCell.Offset(1) ' ... = next source cell
    Loop
   
End Sub

Answer 1

As already mentioned as comment: Loop over the cells of the source column. You can have 3 cases:

• The cell is empty: Save the value of the cell to the right in a variable - I named that b.
• The cell is not empty and not equal 0: Save this value in a second variable - I name it a
• The cell = 0: Dump the values of a and b to the destination.

Of course, depending on your business case, you should give the variables nicer names.

Because your loop would finish at the first empty cell of the first column, you shouldn't use the cell's state as the end criteria. I use CurrentRegion, which is probably sufficient in your situation.

Instead of hardcoding the addresses into the code, I would use named ranges if I were in your position (I cringe when I see anything like Range("V12") hardcoded - Adding a row or column to the sheet would break the function).