Error when running VLookup

[p45cal]

I've had a look at your file but there are a few problems;
I got a message that it couldn't load an object because the object was not available on my machine. Closer examination showed a missing reference to Microsoft Office Web Componenets 11.0 which is an Excel 2003 reference and I only have Excel 2010 here. So when I try to run code it balks.
There is commented-out code left right and centre and I'm not sure which code I should be testing (it all refuses to run anyway).

So I'll confine myself to general comments.

When it next errors out, choose Debug and then hover the mouse over the following variable names in the errant (highlighted in yellow) line (by the way, the one with NO quotes around WorksheetName):
WorksheetName
Column
VoltageDropColumn

Their values should pop up in a little box next to the cursor, and check they contain the values you expect, especially the WorksheetName, as this should contain a string with the EXACT (no extra spaces etc.) name of an existing sheet in the workbook.

I see in some place you have:
WorksheetNumber = "3"
where you've assigned a string to the variable, but later you have:
WorksheetNumber = WorksheetNumber + 1
where you're treating it as a number. I expect that Excel will coerce the value to the right type, both in the above line and in the later:
WorksheetName = WorksheetName & WorksheetNumber
but if you have Dimmed the variables to one type and you have an On Error Resume Next line somewhere, the WorksheetName will not contain quite what you expect.

[mcbrocks]

I've changed WorksheetNumber to be without quotes. But it doesn't seem to affect anything. I display the WorksheetName and it displays the correct stuff according to the different options I choose. The VLookup line still doesn't seem to work. The pop up box all shows the correct columns, worksheetname...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.

[p45cal]

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:

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.

[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?