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]

Is it possible to use a simple find function as Michael suggested?

 

[mikerickson]

I think that Michael missed that you were searching for two values.

I don't understand, it works for me.

 

[dpaton05]

.

 

[Michael M]

@mikerickson....agreed....I assumed a single value was required !!

 

[dpaton05]

I now have this code

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
        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" 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
                                '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).Address
                                    '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

but I have decided to include the referenced information in the current file instead of having to open a new file and get it.

When I identify a late cancelled order using the date and request number, this is the table that I want to use to input the variables such as date and service from the cancelled job and it will then give me a price that I can put in the cancelled row.

CSS Work Allocation Sheet.35.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
27	Late Cancel
28
29	Date	Service	Unit Price	Day rate	Hours	Staff Req.	Kms Travelled	Price ex. GST	Rate	Transport $	MaxPay	Column3	Activities	Column1	Column2
30	16/08/2020	Supervised Contact	$109.60	Sun	3	1		$328.80	$109.60	$0.00	$328.80			0	0
Sheet2

Cell Formulas
Range	Formula
C30	C30	=IF([@Service]="Activities",[@Activities],INDEX(Service_Types,MATCH([@Service],Sheet2!$A$5:$A$12,0),MATCH([@[Day rate]],Sheet2!$A$5:$E$5,0)))
D30	D30	=IF(A30="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A30),"Public_holiday",IF(WEEKDAY(A30)=1,"Sun",IF(WEEKDAY(A30)=7,"Sat","Business_day_rate"))))
H30	H30	=IF([@Service]="Activities",ROUNDDOWN([@Activities]+[@[Transport $]],2),IF([@Service]="Carer Respite",[@[Staff Req.]]*[@Rate],ROUNDDOWN(((IF(OR(ISBLANK(A30),ISBLANK(D30),ISBLANK(B30)),0,[@[Transport $]]+[@MaxPay]))*[@[Staff Req.]]),2)))
I30	I30	=INDEX(Sheet2!$A$5:$E$12,MATCH([Service],Sheet2!$A$5:$A$12,0),MATCH([Day rate],Sheet2!$A$5:$E$5,0))
J30	J30	=([@[Kms Travelled]]*1.22)
K30	K30	=[Rate]*[Hours]
N30	N30	=IF(B30="Supervised transport",1,0)
O30	O30	=IF(B30="Supervised transport",1,0)

Named Ranges
Name	Refers To	Cells
Service_Types	=Sheet2!$A$5:$E$12	I30, C30

Cells with Data Validation
Cell	Allow	Criteria
A30	Any value
B30	List	=Service_List

Therefore, I will need to identify the cancelled order using the date and request number from within the workbook. The date is in column A of each sheet, the request number is in column C and the service will be in column E. The 3 sheets that shouldn't be searched for the order is sheet2, Totals and Cancellations.

Once the row is identified, I need to copy data from the row to the Late Cancel feature I have displayed above on sheet2.

• The Date from column A of the identified row will need to be copied to A30 on sheet2
• The service from column E of the identified row will need to be copied to B30 on sheet 2

For a late cancel, the hours will always be 3 and the staff will be 1 and that will generate the price.

This price then needs to be copied back to column H for the row that was identified earlier.

Can one of you help me with the vba code for this please?

 

[dpaton05]

I only replied with this suggestion of using a late cancel feature, as described in my last post, to calculate the jobs that were cancelled too late as that was all I could think of when I couldn't get the suggestion of Mikerickson to work for me.

If it was working for you Mike, there must be something that I am doing wrong. I have tried to provide all the relevant information to my problem in my last post.

I just want to get it working and I don't mind if the prices are included in this spreadsheet instead of needing to open another spreadsheet to obtain them, as per my last post.

Could you try to help me troubleshoot why it isn't working please?

 

[dpaton05]

I put a on error resume next at the start of my procedure so it doesn't stop whenever a sheet doesn't have anything on it. I also tried to play around with the code and now my code:

• appears to filter the row correctly.
• copies the relevant information to the late cancel feature I described in post 25
• This late cancel feature correctly identifies the new price
• The price is then copied back to the source spreadsheet

The only problem is that instead of overwriting the price in column H for the filtered row, I get this result:

CSS Work Allocation Sheet.37.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Date	Purchase order #	Req #	Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by
4	15/07/2020		1		Transport			$167.40	$167.40	$167.40	167.4	167.4	167.4	15/06/1900	167.4	167.4
8								$167.40	$167.40	$167.40	167.4	167.4	167.4	15/06/1900	167.4	167.4
July

This is my code I ran:

Sub LateCancel()
    On Error Resume Next

        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))
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With

                                Data.Cells(30, 1) = LCDt
                                Data.Cells(30, 2) = Service
                                LCPrice = Data.Cells(30, 8).Value
                              
                                .Offset(1, 7).Value = LCPrice
                            
                                .AutoFilter
                        End With
                End If
        Next ws
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True

End Sub

By the way, Data is the code name for my spreadsheet.

What have I done wrong this time?

Thanks guys.

 

[dpaton05]

I stepped through the code and when the extra figures and dates appear in H4:P5, as shown in my last post, this line of code causes it.

.Offset(1, 7).Value = LCPrice

 

[dpaton05]

All I have in my table/range is

CSS Work Allocation Sheet.37.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Date	Purchase order #	Req #	Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by
4	15/07/2020		1		Supervised contact
5	3/07/2020		5	ujyg
6	15/07/2020		2
7	8/07/2020		9
July

I run my procedure one time and I get this

CSS Work Allocation Sheet.37.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Date	Purchase order #	Req #	Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by
4	15/07/2020		1		Supervised contact			$167.40	$167.40	$167.40	167.4	167.4	167.4	15/06/1900	167.4	167.4
5	3/07/2020		5	ujyg
6	15/07/2020		2
7	8/07/2020		9
8								$167.40	$167.40	$167.40	167.4	167.4	167.4	15/06/1900	167.4	167.4
July

In the LCReq variable from the code in post 27, the value is "1" and the value in LCDt is "15/07/2020"

 

[dpaton05]

I also just realised that the figures and the extra date are copied to the next blank row on all the monthly sheets, not just the one where the date and request number matches.