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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,280
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top