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.

 

[Ark68]

Unsure why I can't get XL2BB to create the capture. Sorry. Tried 3 times.
But here it is.

Diamond_Test.xlsm
	A	B	C
1	SVC #	Row	Column
2	1	3	10
3	2	3	39
4	3	47	10
5	4	47	39
6	5	91	10
7	6	91	39
8	7	135	10
9	8	135	39
10	9	179	10
11	10	179	39
12	11	223	10
13	12	223	39
14	13	267	10
15	14	267	39
16	15	311	10
17	16	311	39
18	17	355	10
19	18	355	39
20	19	399	10
21	20	399	39
22	21	443	10
23	22	443	39
24	23	487	10
25	24	487	39
26	25	531	10
27	26	531	39
28	27	575	10
29	28	575	39
30	29	619	10
31	30	619	39
32	31	663	10
33	32	663	39
34	33	707	10
35	34	707	39
36	35	751	10
37	36	751	39
38	37	795	10
39	38	795	39
40	39	839	10
41	40	839	39
42	41	883	10
43	42	883	39
44	43	927	10
45	44	927	39
46	45	971	10
47	46	971	39
48	47	1015	10
49	48	1015	39
50	49	1059	10
51	50	1059	39
lists

Cell Formulas
Range	Formula
B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26,B28,B30,B32,B34,B36,B38,B40,B42,B44,B46,B48,B50	B6	=B4+44
B7,B9,B11,B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,B35,B37,B39,B41,B43,B45,B47,B49,B51	B7	=B6

 

[rlv01]

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


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

It works that way for me for svcNum = 1. The error is a runtime error coming when it cannot find the value in the lookup range, say for svcNum = 0.

Better to use the Application.VLookup function instead of WorksheetFunction.VLookup. It makes handling the 'not found' case easier.

            svcCol = Application.IfError(Application.VLookup(svcNum, rngSvc, 3, False), "#N/A")
            svcRow = Application.IfError(Application.VLookup(svcNum, rngSvc, 2, False), "#N/A")

svcCol and svcRow must be variants for the above example.

 

[Ark68]

Hi rlv01, thank you for your help. But this avoids the error, but it doesn't solve the problem. There shouldn't be an error, as svrNum 1 is in the vlookup range. Its at row 2 of the data. WHy isn't the vlookup recognizing that?

This works...

        g = Application.WorksheetFunction.Match(svcNum, .Columns(1), 0)
        svcCol = .Cells(g, 3)
        svcRow = .Cells(g, 2

 

[rlv01]

Hi rlv01, thank you for your help. But this avoids the error, but it doesn't solve the problem. There shouldn't be an error, as svrNum 1 is in the vlookup range. Its at row 2 of the data. WHy isn't the vlookup recognizing that?

This works...

        g = Application.WorksheetFunction.Match(svcNum, .Columns(1), 0)
        svcCol = .Cells(g, 3)
        svcRow = .Cells(g, 2

As I mentioned above, the case of svcNum = 1 works fine for me using your code and your data. It returns the expected values and does not produce an error. The only time I get the error you mentioned is if try to look up a value not in the table.

 

[Alex Blakenburg]

Can you try this in the lists spreadsheet: =ISTEXT(A2)
(result should be false)
Alhough we are seeing it as a number via XL2BB perhaps it isn't in its original form.

 

[Alex Blakenburg]

This works...

g = Application.WorksheetFunction.Match(svcNum, .Columns(1), 0)
svcCol = .Cells(g, 3)
svcRow = .Cells(g, 2

If you use rngSvc in your "match" instead of columns(1), does it still work.

 

[Ark68]

Hi Alex, thnak you so much for offering some diagnostics. There was something clearly wrong with rngSvc. I substituted by erroneous lines with the following and it now works.

            svcRow = Application.WorksheetFunction.VLookup(svcNum, ws_lists.Range("A1:C51"), 2, False)
            svcCol = Application.WorksheetFunction.VLookup(svcNum, ws_lists.Range("A1:C51"), 3, False)

 

[Alex Blakenburg]

Still seems very odd. In the spreadsheet you are running the code on and using the test value of 1. Are you sure the 1 is not actually on row 1 ?

 

[Ark68]

Still seems very odd. In the spreadsheet you are running the code on and using the test value of 1. Are you sure the 1 is not actually on row 1 ?

No, my data is exactly as posted in post #2.