How to create a loop to read a range of cells and determine which have values and what is to the right of each

Question

I'm trying to have a program that can read a range of cells which consist of 12 cells (let's say: P79, R79, T79, V79, X79, Z79, AB79, AD79, AF79, AH79, AJ79, AL79) and under those cells there are 6 cells (let's say: V81, X81, Z81, AB81, AD81, AF81), the program is looking for whether or not there are values typed in the cells within the described range.

The program should be able to read the cells from left to right on the top row and loop down to the bottom row and read that from right to left.

If all the cells in the top row have values in them, then the program breaks and doesn't read the values in the bottom row.

As the program reads the values from each cell it should create a table consisting of three columns (let's say: M88, N88, O88), the leftmost column should have the cell number (in order of cell as read by the program (whichever cell has a value first in the loop is given the number 1 and then the next cell to have a value is given number 2 etc.). The middle column should have whatever value is written in it's corresponding cell read from the range. The right column should have the value of whatever is to the right of each cell containing a value.

The first value to be read with a value should give the value "Left End" and the last value to read (whether or not it is the 12th cell to have a value in the top row or the leftmost cell to have a value in the bottom row) should give the value "Right end".

An example of what a row from the table could look like:

Cell #   Cell Value   Position/Left/Right

 1         First            Left End

This is the code I have so far:

Sub Code()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    Dim i As Integer, j As Integer, k As Integer

' First loop to compare a car to the rest after it
    For i = 1 To 12
        For j = i + 1 To 12
        If Not IsEmpty(ws.Range("Cell_" & i)) And Not IsEmpty(ws.Range("Cell_" & j)) Then
                ws.Range("B82").Offset(i).Value = j
                Exit For
            End If
        Next j
    Next i

' Loop backwards to find "Right End"

For k = 12 To 2 Step -1 '24 To 2
        If Not IsEmpty(ws.Range("Cell_12")) Then
        ws.Range("B82").Offset(12).Value = "Right End"
        Exit For


' Has the "Right End" Follow when cars are left blank for lower row

    ElseIf IsEmpty(ws.Range("Cell_" & k)) And Not IsEmpty(ws.Range("Cell_" & k - 1)) Then
        ws.Range("B82").Offset(k - 1).Value = "Right End"
        Exit For


        End If
Next k

What I have here merely inserts a count into a cell range, what I'm trying to do is have my code actually read the cells in the range in the order I described and one at a time look at which cells have values written in them and look at which cells (with values in them) are to the right of any cell with a value and produce the table described above.

Perhaps read minimal reproducible example - not sure I follow what you're doing. A screenshot might help too. — SJR

joopert joopert · Accepted Answer · 2018-08-15T14:20:29

After reading your explanation, which was quite challenging I tried to recreate what you are asking. I used cells A1:L1 with numbers 1 to 12. in the row below that A2:L2, some numbers have been added. with an if value <> "" you can see which cells contain a value. In the second worksheet the table is made:

Sub test()
Dim a As Integer
Dim i As Integer
Dim name As String

ActiveWorkbook.Sheets(1).Activate
a = 1

For i = 1 To endcel
    If Sheets(1).Range("a1").Offset(a, i - 1).Value <> "" Then
       name = Sheets(1).Range("A1").Offset(a, i - 1).Value
       Sheets(2).Activate
       Sheets(2).Range("b2").Offset(i).Value = name
    End If
Next i
End Sub

Does this help? You can adapt it a bit to your problem. Good luck!

How to create a loop to read a range of cells and determine which have values and what is to the right of each

1 Answers