Find all values and store in array (1 Viewer)

[tmyers]

I am trying to "scan" the sheet "Job Info" which uses a sort of template for users to input basic order information and some criteria:

This little block is repeated over and over for as many orders as they have. When they run the report, the user is asked if they wish to run it for specific orders or all of them, if they choose all orders, I need to loop through the sheet and find all order numbers to run from an array I am building (or trying to anyway).

So far I have tried:

Dim wb              As Workbook:        Set wb = ThisWorkbook
Dim info            As Worksheet:       Set info = wb.Sheets("Job Info")
Dim OrderNum        As String
Dim counter         As Long
Dim r               As Range
Dim OrderList       As Variant
Dim x               As Variant

If MsgBox("Run report for all jobs in job info?", vbYesNo) = vbNo Then

    OrderNum = Application.InputBox("Please enter all SE order numbers. Separate all numbers by a comma with no spaces", "Enter SE Order #'s")
Else
 
    counter = 1
    For Each x In info.UsedRange
        ReDim OrderList(1 To counter)
        With info.UsedRange
            Set r = .Find(What:="Order Number", LookIn:=xlValues)
                If r.Offset(0, 1) = "" Then
                    'do nothing
                Else
                    OrderList(counter) = r.Offset(0, 1)
                    counter = counter + 1
                End If
        End With
    Next
End If

This does not seem to work as the IF statement is always false even if the offset cell is empty. I have tried IsEmpty, IsNull and = "" and cant seem to get it to only find entries where there is an order number.

What am I messing up?

Edit:
After looking at the little template block I had, I asked myself why in the world did I not just make it a table, so that block has been converted to a table with the rows shown in the snip as headers.

 

[Minty]

I think (maybe) it's your range.offset (0,1) I think it will only return another range not a value.

Have you tried to debug.print r.Offset(0, 1) to see what it returns?

 

[tmyers]

I think (maybe) it's your range.offset (0,1) I think it will only return another range not a value.

Have you tried to debug.print r.Offset(0, 1) to see what it returns?

It returns the value when it finds one, otherwise it creates another empty entry in the array.
Now that I realized I should have had this as a table all along, I am rethinking my approach a little bit on it. I should be able to just find the last used row in the table, then loop through it storing all values in the array.

 

[Gasman]

I have always used .Value in exactly that situation, and that works????

 

[tmyers]

I got it working Gas . Once I releasing I was being an idiot yet again, I fixed it quickly.