Assigning to a variable

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589
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?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Michael M

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589
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
1,589
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
19,090
Office Version
2013
Platform
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
19,090
Office Version
2013
Platform
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
23,769
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
1,589
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,186
Messages
5,509,697
Members
408,750
Latest member
vdspharma

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top