VLookup Failing

[Ark68]

Based on this code, can anyone suggest why I am getting an "Unable to get the VLookup property of the WorksheetFunction class" error?

Sub pop_svc(ByRef svcNum As Double)
    Dim rngSvc as range
   
    Set rngSvc = ws_lists.Range("A2:C51")
    With ws_lists
        svcCol = Application.WorksheetFunction.VLookup(svcNum, rngSvc, 3, False)
        svcRow = Application.WorksheetFunction.VLookup(svcNum, rngSvc, 2, False)
    End With
    With ws_front
        rngTemp.Copy Destination:=.Cells(svcRow, svcCol)
    End With
End Sub

Here is rngSvc:

[RANGE=rs:51|cs:3|w:Diamond_Test.xlsm|cls:xl2bb-120|s:lists|tw:144][XR][XH][/XH][XH=w:48]A[/XH][XH=w:48]B[/XH][XH=w:48]C[/XH][/XR][XR][XH]1[/XH][XD=h:l|ch:14.5]SVC #[/XD][XD=h:l]Row[/XD][XD=h:l]Column[/XD][/XR][XR][XH]2[/XH][XD=ch:14.5]1[/XD][XD]3[/XD][XD]10[/XD][/XR][XR][XH]3[/XH][XD=ch:14.5]2[/XD][XD]3[/XD][XD]39[/XD][/XR][XR][XH]4[/XH][XD=ch:14.5]3[/XD][XD]47[/XD][XD]10[/XD][/XR][XR][XH]5[/XH][XD=ch:14.5]4[/XD][XD]47[/XD][XD]39[/XD][/XR][XR][XH]6[/XH][XD=ch:14.5]5[/XD][XD=cls:fx][FORMULA==B4+44]91[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]7[/XH][XD=ch:14.5]6[/XD][XD=cls:fx][FORMULA==B6]91[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]8[/XH][XD=ch:14.5]7[/XD][XD=cls:fx][FORMULA==B6+44]135[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]9[/XH][XD=ch:14.5]8[/XD][XD=cls:fx][FORMULA==B8]135[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]10[/XH][XD=ch:14.5]9[/XD][XD=cls:fx][FORMULA==B8+44]179[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]11[/XH][XD=ch:14.5]10[/XD][XD=cls:fx][FORMULA==B10]179[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]12[/XH][XD=ch:14.5]11[/XD][XD=cls:fx][FORMULA==B10+44]223[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]13[/XH][XD=ch:14.5]12[/XD][XD=cls:fx][FORMULA==B12]223[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]14[/XH][XD=ch:14.5]13[/XD][XD=cls:fx][FORMULA==B12+44]267[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]15[/XH][XD=ch:14.5]14[/XD][XD=cls:fx][FORMULA==B14]267[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]16[/XH][XD=ch:14.5]15[/XD][XD=cls:fx][FORMULA==B14+44]311[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]17[/XH][XD=ch:14.5]16[/XD][XD=cls:fx][FORMULA==B16]311[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]18[/XH][XD=ch:14.5]17[/XD][XD=cls:fx][FORMULA==B16+44]355[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]19[/XH][XD=ch:14.5]18[/XD][XD=cls:fx][FORMULA==B18]355[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]20[/XH][XD=ch:14.5]19[/XD][XD=cls:fx][FORMULA==B18+44]399[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]21[/XH][XD=ch:14.5]20[/XD][XD=cls:fx][FORMULA==B20]399[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]22[/XH][XD=ch:14.5]21[/XD][XD=cls:fx][FORMULA==B20+44]443[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]23[/XH][XD=ch:14.5]22[/XD][XD=cls:fx][FORMULA==B22]443[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]24[/XH][XD=ch:14.5]23[/XD][XD=cls:fx][FORMULA==B22+44]487[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]25[/XH][XD=ch:14.5]24[/XD][XD=cls:fx][FORMULA==B24]487[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]26[/XH][XD=ch:14.5]25[/XD][XD=cls:fx][FORMULA==B24+44]531[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]27[/XH][XD=ch:14.5]26[/XD][XD=cls:fx][FORMULA==B26]531[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]28[/XH][XD=ch:14.5]27[/XD][XD=cls:fx][FORMULA==B26+44]575[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]29[/XH][XD=ch:14.5]28[/XD][XD=cls:fx][FORMULA==B28]575[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]30[/XH][XD=ch:14.5]29[/XD][XD=cls:fx][FORMULA==B28+44]619[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]31[/XH][XD=ch:14.5]30[/XD][XD=cls:fx][FORMULA==B30]619[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]32[/XH][XD=ch:14.5]31[/XD][XD=cls:fx][FORMULA==B30+44]663[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]33[/XH][XD=ch:14.5]32[/XD][XD=cls:fx][FORMULA==B32]663[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]34[/XH][XD=ch:14.5]33[/XD][XD=cls:fx][FORMULA==B32+44]707[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]35[/XH][XD=ch:14.5]34[/XD][XD=cls:fx][FORMULA==B34]707[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]36[/XH][XD=ch:14.5]35[/XD][XD=cls:fx][FORMULA==B34+44]751[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]37[/XH][XD=ch:14.5]36[/XD][XD=cls:fx][FORMULA==B36]751[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]38[/XH][XD=ch:14.5]37[/XD][XD=cls:fx][FORMULA==B36+44]795[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]39[/XH][XD=ch:14.5]38[/XD][XD=cls:fx][FORMULA==B38]795[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]40[/XH][XD=ch:14.5]39[/XD][XD=cls:fx][FORMULA==B38+44]839[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]41[/XH][XD=ch:14.5]40[/XD][XD=cls:fx][FORMULA==B40]839[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]42[/XH][XD=ch:14.5]41[/XD][XD=cls:fx][FORMULA==B40+44]883[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]43[/XH][XD=ch:14.5]42[/XD][XD=cls:fx][FORMULA==B42]883[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]44[/XH][XD=ch:14.5]43[/XD][XD=cls:fx][FORMULA==B42+44]927[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]45[/XH][XD=ch:14.5]44[/XD][XD=cls:fx][FORMULA==B44]927[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]46[/XH][XD=ch:14.5]45[/XD][XD=cls:fx][FORMULA==B44+44]971[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]47[/XH][XD=ch:14.5]46[/XD][XD=cls:fx][FORMULA==B46]971[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]48[/XH][XD=ch:14.5]47[/XD][XD=cls:fx][FORMULA==B46+44]1015[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]49[/XH][XD=ch:14.5]48[/XD][XD=cls:fx][FORMULA==B48]1015[/FORMULA][/XD][XD]39[/XD][/XR][XR][XH]50[/XH][XD=ch:14.5]49[/XD][XD=cls:fx][FORMULA==B48+44]1059[/FORMULA][/XD][XD]10[/XD][/XR][XR][XH]51[/XH][XD=ch:14.5]50[/XD][XD=cls:fx][FORMULA==B50]1059[/FORMULA][/XD][XD]39[/XD][/XR][/RANGE][RANGE=cls:xl2bb-extra-120|t:cf][XR][XD]B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26,B28,B30,B32,B34,B36,B38,B40,B42,B44,B46,B48,B50[/XD][XD=fw:b]B6[/XD][XD]=B4+44[/XD][/XR][XR][XD]B7,B9,B11,B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,B35,B37,B39,B41,B43,B45,B47,B49,B51[/XD][XD=fw:b]B7[/XD][XD]=B6[/XD][/XR][/RANGE]


In testing, svcNum = 1. I would expect then that the VLookups would return values for svcCol and svcRow as 10 and 3 respectively.

 

[Alex Blakenburg]

I know you now have something that allows you to move forward but if you are able to share your workbook via google drive or one of the other sharing platforms we are happy to take a look. If you sanitise the file, just make sure the problem still exists in the file you upload.

 

[Ark68]

HI Alex, I am so appreciative of the effort of folk like yourself to help others. Mr. Excel is one site that truly unselfishly and patiently enjoys helping others. I would certainly share but I think it would take me more work to do so than what the solution would be. I'd have to sanitize it of sensistive information and make adjustments to compensate for than, and the code relies on secondary network files for data so I would have to adapt a whole different structure to accomodate the data in the workbook. I fear you'd be spending more time dodging errors to get to the actual "problem".

 

[Alex Blakenburg]

Thank you for those kind words. Lets leave it for now and you can get back to us if the problem occurs again and want us to have another crack at it.