Error when running VLookup

Quote:

---

Originally Posted by mcbrocks

all except for the first cells property: Cells(10, Column). It's showing the correct column, but the value of this cell, is from my active sheet and not the referenced sheet, meaning: It shows: Worksheets(DataPVC2) <--in the pop up box. BUT the cell displays the value from Worksheets(DataThermo2) <-- or whatever worksheet is active.

---

I think you've nailed it - and I should have seen this - the range is not properly qualified; the Cells(…) parts need to be qualified:

Code:

---

With Worksheets(WorksheetName)
  VoltageDrop = WorksheetFunction.VLookup(Val(LoadCurrentText.Text), .Range(.Cells(10, Column), .Cells(26, VoltageDropColumn)), ReturnColumn)
End With

---

paying especial attention to the dots in the code. The With… End With consruct could probably span more than one line.

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:

Code:

---

    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?

Here's the updated copy of the worksheet when I switch to using index match instead of vlookup.
Attachment 1510

I've posted this question over here as well Error with index match function - Page 2