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

0 votes

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
End Sub
Mar 24 in Others by narikkadan
• 63,180 points

1 answer to this question.

0 votes

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).

answered Mar 24 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

VBA - Build an Array by Looping Through Another Array with Specific Criteria

Please attempt the next option. It is ...READ MORE

answered Mar 23 in Others by narikkadan
• 63,180 points
0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16 in Others by narikkadan
• 63,180 points
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,180 points
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,220 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21 in Others by Kithuzzz
• 38,010 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP