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]

That seems to do the same thing as having the on error resume next at the top but I guess that it doesn't ignore as much code. However, I really would like to get it working without that line of code. Do you have excel at the moment or can someone else jump in and help me please?

 

[dpaton05]

I think I have found a problem. I have some dummy data that I entered and I tried to change my code a little.

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, Month As String, Service As String, LCPrice As String
        Set wb2 = ThisWorkbook
        QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value
        
        WbPath = ThisWorkbook.Path
        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" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                'On Error Resume Next
                                '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
                                'Add the service to a varaible
                                Service = .Areas(1).Cells(2, 5).Value
                                    'With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                    
                                        'Service = .Areas(1).Cells(1, 5).Value
                                    'End With

                                With Data
                                    .Cells(30, 1) = LCDt
                                    .Cells(30, 2) = Service
                                    .Cells(30, 5) = 3
                                    .Cells(30, 6) = 1
                                End With
                                
                                LCPrice = Data.Cells(30, 8).Value
                                
                                    'With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        .Areas(1).Cells(2, 8).Value = LCPrice
                                        .Areas(1).Cells(2, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(2, 10).Formula = "=RC[-1]+RC[-2]"
                                    'End With
                                
                              
                                .AutoFilter
                        End With
                End If
        Next ws
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True


End Sub

It now halts at this line of code

LCPrice = Data.Cells(30, 8).Value

It does this for any monthly sheet that is encountered that doesn't have anything in column E. I had some dummy data in July sheet:

CSS Work Allocation Sheet.39.xlsm
	A	B	C	D	E	F	G	H	I	J
3	Date	Purchase order #	Req #	Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST
4	15/07/2020		2		Transport			$203.10	$20.31	$223.41
5	20/07/2020		2
July

Cell Formulas
Range	Formula
I4	I4	=IF(E4="Activities",0,H4*0.1)
J4	J4	=I4+H4

I get type mismatch error on this line of code

LCPrice = Data.Cells(30, 8).Value

I think it might be if a sheet is encountered with nothing in the column H?

 

[dpaton05]

Actually, I think I might be if the auto filter returns no records the search needs to move to the next sheet instead of trying to assign everything, can you help me with some code to make the search skip the sheet if no results are found please Michael?

 

[Michael M]

Sorry, can't help at the moment.....maybe someone else can jump in and assist !!