BenChod
06-14-2017, 05:48 AM
Hi All -
I have a vlookup running and when a match is not found, the code crashes. The error is: Run-time error '1004' Unable to get the Vlookup property of the worksheetfunction class. This only happens where there is no match. If there is no match, it is suppose to go to the line code where this is no match and enter no match. Hoping someone can take a quick look and see what the issue is. I already set all the variable so they are not copied below.
Set QC = Sheets("BlockList")
Set RefDat = Sheets("QC")
Set WSF = Application.WorksheetFunction
With QC
Set AssToApp = .Columns(4) 'Assigned to App'
Set Status = .Columns(5)
Set Severity = .Columns(6)
Set Blocking = .Columns(7)
Set FixDate = .Columns(8)
'Use last non empty column plus 1
Set TestCol = .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).EntireColumn
'Use bottom non empty cell in "C"
Set QCTable = QC.Range(.Cells(2, "C"), .Cells(Rows.Count, "C").End(xlUp))
End With 'QC
With RefDat
'Use bottom non empty cell in "A:M"
Set RefTable = .Range(.Cells(2, "A"), .Cells(Rows.Count, "M").End(xlUp))
End With 'RefDat
For Each Cel In QCTable
On Error GoTo NotFound
AssToApp.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 2, False)
Status.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 4, False)
Severity.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 5, False)
Blocking.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 6, False)
FixDate.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 13, False)
GoTo Continue 'No errors
NotFound:
Status.Cells(Cel.Row) = "Not Found"
On Error GoTo 0 'Resets error handling
Continue:
Next Cel
'MsgBox "Done"
End Sub
I have a vlookup running and when a match is not found, the code crashes. The error is: Run-time error '1004' Unable to get the Vlookup property of the worksheetfunction class. This only happens where there is no match. If there is no match, it is suppose to go to the line code where this is no match and enter no match. Hoping someone can take a quick look and see what the issue is. I already set all the variable so they are not copied below.
Set QC = Sheets("BlockList")
Set RefDat = Sheets("QC")
Set WSF = Application.WorksheetFunction
With QC
Set AssToApp = .Columns(4) 'Assigned to App'
Set Status = .Columns(5)
Set Severity = .Columns(6)
Set Blocking = .Columns(7)
Set FixDate = .Columns(8)
'Use last non empty column plus 1
Set TestCol = .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).EntireColumn
'Use bottom non empty cell in "C"
Set QCTable = QC.Range(.Cells(2, "C"), .Cells(Rows.Count, "C").End(xlUp))
End With 'QC
With RefDat
'Use bottom non empty cell in "A:M"
Set RefTable = .Range(.Cells(2, "A"), .Cells(Rows.Count, "M").End(xlUp))
End With 'RefDat
For Each Cel In QCTable
On Error GoTo NotFound
AssToApp.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 2, False)
Status.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 4, False)
Severity.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 5, False)
Blocking.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 6, False)
FixDate.Cells(Cel.Row) = WSF.VLookUp(Cel, RefTable, 13, False)
GoTo Continue 'No errors
NotFound:
Status.Cells(Cel.Row) = "Not Found"
On Error GoTo 0 'Resets error handling
Continue:
Next Cel
'MsgBox "Done"
End Sub