Mismatch error in vba with vlookup match combination?

[tahir9]

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.

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

 

[tahir9]

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]

The variable strsearch is an array so you need to index into it like you are doing with sheetlist.

 

[tahir9]

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]

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

 

[tahir9]

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]

What are you expecting VLookup to return and in what way can that be used as the Cells property's column reference?

 

[tahir9]

Oh haha I see your point.