Assigning to a variable

[dpaton05]

I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv.

        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                'Autofilter the late cancel date enter in B34 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                'The service in column 5 is assigned to the Serv variable for use with calculating the late cancel price
                                Serv = .Offset(1, 5).Value
                                Debug.Print Serv
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws

This line is highlighted saying type mismatch

 Serv = .Offset(1, 5).Value

Can someone help tell me why please?

 

[dpaton05]

If you want the fifth colum of the first filtered row

With Range("A3").CurrentRegion
    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1,0))
        serv = .Areas(1).Cells(1, 6).Value
    End With
End With

I tried to enter this in Mike but I get an error object variable or with block variable not set. I press debug and it highlights Serv = .Areas(1).Cells(1, 6).Value

Sub LateCancel()

        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String
        Set wb2 = ThisWorkbook
        QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")
        'Set sht = Sheets("Cancellations")
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value
        WbPath = ThisWorkbook.Path
        'CurDir ".."
        'CurDir ".."
        'QTPath = Left(WbPath, InStrRev(WbPath, "\") - 2)
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        
        If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
    
        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                'Autofilter the late cancel date enter in B34 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                'The service in column 5 is assigned to the Serv variable for use with calculating the late cancel price
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        Serv = .Areas(1).Cells(1, 6).Value
                                    End With
                                Debug.Print Serv
                                .AutoFilter
                        End With
                        Serv = Range("A3").Offset(1, 5).Value
                        'Debug.Print "Serv"
                End If
                
        Next ws
        
        
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True

End Sub

 

[dpaton05]

I have been doing some reading about Find and as you said, it looks like I could use it. What code would I use for it?

 

[dpaton05]

I can find how to use find but how would i use it to look in every sheet, excluding the totals and calculations sheets?

 

[mikerickson]

In my testing it returned the value from the column F cell of the row that was visibile from the filtering. If the filter made all the rows invisible, that could cause that error.

 

[dpaton05]

Did I have any errors in my code?

 

[dpaton05]

Or did you use just your example code without my extra code?

 

[dpaton05]

I tried running this and it didn't print anything.

Sub ab()
Dim Serv As String
                        With Sheets("june").Range("A3").CurrentRegion
                                'Autofilter the late cancel date enter in B34 with dates in column 1
                                '.AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                '.AutoFilter 3, LCReq
                                'The service in column 5 is assigned to the Serv variable for use with calculating the late cancel price
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        Serv = .Areas(1).Cells(1, 6).Value
                                    End With
                                Debug.Print Serv
                                .AutoFilter
                        End With
End Sub

 

[mikerickson]

Have you tried this testing version

With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
    Serv = .Areas(1).Cells(1, 6).Address
End With
Debug.Print Serv

 

[dpaton05]

If I put it in a sub on its own, I get an error "invalid or unqualified reference" and if I substitute it into the sub LateCancel, I get the error object variable or with block variable not set, with this line highlighted.

Serv = .Areas(1).Cells(1, 6).Address

 

[mikerickson]

I meant alterning the code from post 17 to match those lines.