unable to get the match property of the worksheet function class

[SBF12345]

I am receiving the error "unable to get the match property of the worksheet function class". I suspect my data as input into the function is not allowing for the smooth operation of the function. Below is the code.

C = TS.Cells(A, "A").value    D = TS.Cells(A, "B").value
    E = TS.Cells(A, "C").value 
    
    Set G = PBK.Range("A1").CurrentRegion
    
    F = Application.WorksheetFunction.Match(D, G, 0)

TS is a workbook.worksheet variable

D is a Date variable

G is a Range variable

 

[mikerickson]

It sounds like D is not in the range G.

Try something like

Dim F as Variant

' ...
F = Application.Match(D, G, 0)

If IsError(F) Then
    MsgBox "D not there"
Else
    MsgBox "D is there"
End If

Application.Match will return an error value, Application.WorksheetFunction.Match will cause a VBA error.

 

[SBF12345]

still having trouble...I changed the D and G variables to variants and rewrote G to

Set G = PBK.Range(Cells(1, "A"), Cells(N, "I"))

D is present in the first column of the array G. What other methods can I use to troubleshoot this error? I am watching the related variables. G has a "count" value (under add watch) that is probably correct given the number of columns and rows. The other variables are giving values too.

 

[mikerickson]

Match works on a single column or row of cells, your G has more than than.

 

[SBF12345]

changed G to read

Set G = PBK.Range(Cells(1, "A"), Cells(N, "A"))

even with the single column in the range the match is giving the same error

 

[SBF12345]

the match line

F = Application.Match(D, G, 0)

includes D as a Variant, G as a Range, and F as a Variant. G is a single column (column "A").

when I run it currently the D variable is established correctly as a date "9/5/2007", but the F variable element in Add Watch window reads Error 2042. I've looked through the range and have not observed any cells with the contents "N/A#" in them. Any ideas why as to how this error is being generated?

 

[SBF12345]

I changed the Range variable to an array variable. It seems to be correctly returning the position value in the array as intended.