Mismatch error in vba with vlookup match combination?

tahir9

New Member
Joined
Aug 31, 2014
Messages
45
Essentially the code is suppose to match the names in a column then go to the last call column or last visit column find if they have been contacted in the last 7 days and paste it into the sheet named after the person. So all the jacksons visited in the past 7 days would be copied to the jacksons sheet. Thanks for the help. But I am getting a mismatch error.
Code:
Sub dateofvisit()
Dim lastrow As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
sheetlist = Array("Jackson", "Michael", "Bieber", "Nicole", "Reina", "TimC")
strsearch =Array("Jackson", "Michael", "Bieber", "Nicole", "Reina", "TimC")
For Z = LBound(strsearch) To UBound(strsearch)
    
    For i = LBound(sheetlist) To UBound(sheetlist)
        Sheets("Sheet1").Activate
        lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        dbate = "Date of Last Call"
        ddate = "Date of Last visit"
        
            For x = lastrow To 5 Step -1
                If ActiveSheet.Cells(x, Application.VLookup(strsearch, Range("A5:S3000"), 17, False)) >= DateAdd("d", -7, Now) _
                    Or ActiveSheet.Cells(x, Application.VLookup(strsearch, Range("A5:S3000"), 16, False)) >= DateAdd("d", -7, Now) Then
                        Rows(x).Copy Sheets(sheetlist(i)).Cells(Rows.Count, 1).End(xlUp)(2)
                    End If
                Next x
            Next i
        Next Z
        
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

tahir9

New Member
Joined
Aug 31, 2014
Messages
45
The 17 and 16 in the cells object should replaced with application.match(dbate,Range("A4:S4"),0) and application.match(ddate, Range("A4:S4"),0).
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The variable strsearch is an array so you need to index into it like you are doing with sheetlist.
 

tahir9

New Member
Joined
Aug 31, 2014
Messages
45
Thanks I did index I forgot to include that, but I am still getting the mismatch error. So I changed application to worksheetfunction and then I got the unable to use the vlookup property of the worksheetfunction class. Its not finding the date but I can't point to what it can be.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Why are you using VLookup to set the Cells Property's Column argument?
 

tahir9

New Member
Joined
Aug 31, 2014
Messages
45
Well I am using v lookup because its simpler than having a lot of if statements. So in column b there are the names and columns p and q have the information I am searching for and information that is the basis for my if criteria. I figure vlookup would be the way to go about since it seems to be the more dynamic route. I am open to suggestions if you have an idea on how to make it better or not experience that error. Thanks for responding btw.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What are you expecting VLookup to return and in what way can that be used as the Cells property's column reference?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,603
Members
430,557
Latest member
MK15

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