Error when running VLookup

[mcbrocks]

p45cal, it works!!! I didn't know it had to be so specific. But however, I have some cells within the range that contain a 0 value. This throws off the desired result. Is there any line that can be put in to rectify the error? I tried this:

    If Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column)).Value = 0 Then
        Ans = MsgBox("This method does not exist for " & WorksheetName & ".", vbRetryCancel + vbExclamation)
        If Ans = vbRetry Then CableSizingSelection.UndoAction
        If Ans = vbCancel Then Unload CableSizingSelection
    End If

But the first If line is giving trouble.

Is there any other way to avoid the error? There are some columns where only the last few rows contain 0s and if the lookup value can be found within the top few lines, the program would have to work correctly. I realized the retry cancel actually searches for 0 values whether it affects the result or not. Maybe I could shorten the lookup range. Like say have the range from row 10 to the last non-0 cell?