WorksheetFunction.Match trouble

[KG Old Wolf]

All,

The MAX function works fine but the MATCH does not. I am trying to obtain a column number. MATCH works with hard code but I need to use a variable. As soon as I attempt to make the range a variable, it fails

Dim myRange As Range
myRange = Worksheets("tbl_Allocations").Range("E6:P6")
'
ary_Max_Value = WorksheetFunction.Max(ary_Work_Area)
ary_c_count = WorksheetFunction.Match(ary_Max_Value, Range("E6:P6"), 0)

It fails if if I use:
ary_c_count = WorksheetFunction.Match(ary_Max_Value, Range("myRange"), 0)


I cannot see what I am doing wrong... any ideas?

Thanks

 

[MrKowz]

What did you dim ary_c_count as? Also, where exactly is it erroring, and what error are you receiving?

 

[Scott Huish]

Why are you using the worksheet function MATCH as opposed to the VBA function FIND?

Also RANGE is an object and therefore requires the SET keyword:

Set myRange = Worksheets("tbl_Allocations").Range("E6:P6")

and then refer to this range as a range

The MAX function shouldn't work because you haven't assigned a range to ary_Work_Area

 

[KG Old Wolf]

the "set" was made earlier in the code.

I solved the problem... it is an array I was inspecting....

I tried many variations including going right at the array but that didn't work (which didn't make any sense but I moved on). HOWEVER, the following works because I placed () after the array name.
It is odd that the "MAX" function didn't require the parentheses....


ary_Max_Value = WorksheetFunction.Max(ary_Work_Area)
'
ary_c_count = WorksheetFunction.Match(ary_Max_Value, ary_Work_Area(), 0)


Thanks for your prompt responses... and I hope this helps others