excel - Trying to use offset function on a filtered range

Question

I keep getting an error when I try to use the offset function. I can do this fine using formulas, but I'd like to use code instead. The table is already filtered. All my code works fine. I just need to get the offset part of the code to work properly.

I am trying to return the values in column A. The for loop is looping through the values in column C. This has been tested in formulas and can be seen in column F.

Sub DoughnutPricing()
'Initialize this Dashboard Worksheet and Worksheet
Dim BakeryWorkbook As Workbook
Set BakeryWorkbook = ThisWorkbook

Dim Bakery2Worksheet As Worksheet
Set Bakery2Worksheet = Worksheets("Bakery 2")
Dim Row As Integer
Row = 2

With Bakery2Worksheet
        
                'Loop until to the last row.
                Dim LastRowDonutList As Long
                LastRowDonutList = .Range("A" & Rows.Count).End(xlUp).Row

                'Now we need to set a value to the relevant range. This is the range
                'before the data gets sorted
                Dim rRange As Range
                Set rRange = .Range("A2:C" & LastRowDonutList)

                'After sorting, we will end up with several data rows for
                'which we will need to sort from. Now that we have a filtered table,
                'we would then need to created a variable for our new filtered range
                Dim DonutTablefltrdRng As Range
                Set DonutTablefltrdRng = rRange.SpecialCells(xlCellTypeVisible)

                'Now that we have all these sorted, we will then need to
                'verify whether the target finish date is within the start and end date

                Dim cel As Range
                Dim WorkOrderRange As Range
                Dim DeliverableDateRange As Range
                Dim DeliverableDate As Date


                For Each cel In DonutTablefltrdRng
                
                'Since our data is already sorted we want to write the doughnuts that only cost $1 into Cell E2
            
                    
                        'Start by establishing the last rows for the work orders and and the deliverables

                         Dim DonutTypeLastRow As Long
                         DonutTypeLastRow = .Range("A" & Rows.Count).End(xlUp).Row

                         Dim DonutType As Variant
                         DonutType = Application.WorksheetFunction.Offset(cel, 0, -2)
                         Debug.Print DonutType
                          
                Next cel
     End With
                          
End Sub

question from:https://stackoverflow.com/questions/65838652/trying-to-use-offset-function-on-a-filtered-range

Answer 1

Waitting for answers