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?

 

[Michael M]

Well without the workbook to play with, you can keep using the On error line, if that makes it easier.
Just keep in mind, that for future reference, that if the code fails at some point it won't show an error !

 

[dpaton05]

Try this one Michael.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thankyou

 

[Michael M]

Can't do much without some real data.....but you can move the On error line down to where it actually impacts the code rather than at the start

With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
On Error Resume Next
   Service =.Areas(1).Cells(1, 5).Value
End With

 

[dpaton05]

All I needed to do when I was testing was enter any request number for a row or job on a monthly sheet in column C, such as July and enter a date for that job on that sheet in column A, such as ??/07/2021. For that job, I would also enter one of the services in A6:A12 of sheet2 in column E for that job, such as "Transport".

I would then go and enter the same numbers that I just entered into the monthly sheet, into B32 and B34 of the totals sheet. This would identify the job when the code is run.

I then would run the code to see if the date of the identified row was copied to A30 of sheet 2, the service copied to B30. This would then generate a price in H30 which would need to be copied back to column H for the job that was identified using the date and request number.

 

[Michael M]

You must remember though Dave....we don't know what you enter or where you enter it, and we don't know what your workbook is doing, nor do we know how to run the data
Did you relocate the On error line ??

 

[dpaton05]

Ok, look at this file and run the late cancel procedure in module 4.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Michael M]

So did you try the code in post #43

With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
On Error Resume Next
   Service =.Areas(1).Cells(1, 5).Value
End With

 

[dpaton05]

But I thought you said that it is good to fix errors instead of using on error resume next?

 

[dpaton05]

I did this to the code Michael

                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        On Error Resume Next
                                        Service = .Areas(1).Cells(1, 5).Value
                                        On Error GoTo 0
                                    End With

So that ignored the error but it halted further down on this line:

.Areas(1).Cells(1, 8).Value = LCPrice

I have included all the code for ease of reference.

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
                                '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
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        On Error Resume Next
                                        Service = .Areas(1).Cells(1, 5).Value
                                        On Error GoTo 0
                                    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(1, 8).Value = LCPrice
                                        .Areas(1).Cells(1, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(1, 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

The error this time was object variable or with block variable not set.

If I move the on error goto 0 after this line

  .Areas(1).Cells(1, 8).Value = LCPrice

The code halts on the next line and does the same for each line of code within that with block

                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        .Areas(1).Cells(1, 8).Value = LCPrice
                                        .Areas(1).Cells(1, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(1, 10).Formula = "=RC[-1]+RC[-2]"
                                    End With

Obviously, if I leave on error goto 0 out, it works fine, but that is not fixing the issue.

 

[Michael M]

I've moved the on error to here.....I don't have Excel ATM......

If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[a3].CurrentRegion
                           On Error Resume Next

Someone else may be able to further input.