Assigning to a variable

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv.

VBA Code:
        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
VBA Code:
 Serv = .Offset(1, 5).Value

Can someone help tell me why please?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,353
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
You will need something before the .Offset .....for example
VBA Code:
Serv = Range("A3").Offset(1, 5).Value
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Try placing that statement after End With?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You will need something before the .Offset .....for example
VBA Code:
Serv = Range("A3").Offset(1, 5).Value
I tried your example but that didn't print the Serv variable to the immediate window. You have provided an example, how should I work out what it should be?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Try placing that statement after End With?
I tried moving therm outside of the with block and that didn't do anything
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is my whole procedure code
VBA 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
        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
                                Serv = sh.Range("A3").Offset(1, 5).Value
                                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

I was just trying to get the serv variable working first by putting in the debug.print before I went on to use the variable but nothing is appearing in the immediate window. This is meant to be the value in column 5 of the filtered row.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,353
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Well, you are filtering 2 columns, which column is the required value going to be in
AND will there only be 1 occurence of the value available.
AND
if this is the ultimate goal, why Autofilter at all, just do a Find !
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,353
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How is this relative to the Autofilter ??
VBA Code:
Serv = sh.Range("A3").Offset(1, 5).Value
You are extracting data from a different sheet to create the variable??
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,017
If you want the fifth colum of the first filtered row

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I want to identify the job in a work allocation sheet with 1 sheet for each month. The date and request number is how I will identify the row, with the date in column A and the request number in column C. I am trying to make a feature for if there is a late cancel of the job, the price is a flat 3 hour charge. I have another spreadsheet that called my quoting tool which calculates the price but it also has all of the prices for the different services at different times. I did not want to replicate all that information in the work allocation sheet as I wanted it in one central location. You only need to update things in one place and it updates everything. I only used the auto filer feature as that is what I found from searching the net for code to use.

As the job is cancelled, it will be a 3 hour duration for the cost of it but with all the other variables, I was going to open the quoting tool, enter the variables into a Late Cancel table that I have inserted and this would reference all the data that the spreadsheet to calculate the price.

This is from work allocation sheets workbook. This is the format used for every monthly sheet. There is sheet for every month of the year, a sheet for Totals and a Cancellations sheet.

CSS Work Allocation Sheet.32.xlsm
ABCDEFGHIJKLMNO
3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GSTAllocated toDate report receivedDate report sentAllocated byReport sent by
4
June


I want jobs found using the date and request number and when that entry is found, I need the date, request number and service all put into variables.

Once this is done, then I want to open my quoting tool and go enter those variables into A30 for the date, Service in B30 and it will be a flat 3 in the E30 and a flat 1 in F30.

CSS_quoting_tool_29.5.xlsm
ABCDEFGHIJKLMNO
27Late Cancel
28
29DateServiceUnit PriceDay rateHoursStaff Req.Kms TravelledPrice ex. GSTRateTransport $MaxPayColumn3ActivitiesColumn1Column2
3007/07/2020Supervised Transport$55.80Business_day_rate31$167.40$55.80$0.00$167.4011
Sheet2
Cell Formulas
RangeFormula
C30C30=IF([@Service]="Activities",[@Activities],INDEX(Service_Types,MATCH([@Service],Sheet2!$A$5:$A$12,0),MATCH([@[Day rate]],Sheet2!$A$5:$E$5,0)))
D30D30=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"))))
H30H30=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)))
I30I30=INDEX(Sheet2!$A$5:$E$12,MATCH([Service],Sheet2!$A$5:$A$12,0),MATCH([Day rate],Sheet2!$A$5:$E$5,0))
J30J30=([@[Kms Travelled]]*1.22)
K30K30=[Rate]*[Hours]
N30N30=IF(B30="Supervised transport",1,0)
O30O30=IF(B30="Supervised transport",1,0)
Named Ranges
NameRefers ToCells
Service_Types=Sheet2!$A$5:$E$12I30, C30


This will generate a price in H30 on my quoting tool that I want to copy back to H30 in the row that was identified using the date and request number in the work allocation sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,873
Messages
5,655,737
Members
418,234
Latest member
jdorfma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top