Using Array Index function in a sub

[General]

The following sub is fed a sheet #, a high row, and a low row. It then
searches on that sheet, between the high and low row (in a given
column) and returns the most common word.

However, when I run it I get an "Object required" error pointing at the
getgenre.formulaarray = Worksheet..... line. Can someone help, what am
I doing wrong?

Thanks!

Phil

Function getgenre(shtno, highrow, lowrow)

genreloccol = 2

evalrange = Range(Worksheets(shtno).Cells(highrow, genreloccol),
Worksheets(shtno).Cells(lowrow, genreloccol))

getgenre.FormulaArray = Worksheet.Function.Index(evalrange,
Worksheet.Function.Match(Worksheet.Function.Max(Worksheet.Function.CountIf(evalrange),
Worksheet.Function.CountIf(evalrange), 0)))

End Function

 

[Charles Williams]

FormulaArray is a property of a Range, not a variant. Also it expects a
formula string, not the result of a set of functions.

Either assign the result of your set of functions to a variant,
or create a formulastring and then
either use Application.Evaluate(Formulastring)
or Range.Formulaarray=formulastring

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[General]

Hi Charles,

Thanks for your reply.

I am new to this and I don't really understand what you mean. Would you
mind explicitly telling me what to type? Sorry...

Thanks,
Phil

 

[Tom Ogilvy]

Your formulas wrong anyway, but besides that, array formula behavior is not
supported in Excel. Fortunately, the evaluate function acts as a virtual
cell, so you can feed it a formula like you would place in a cell in the
worksheet and it will evaluate that formula, including array formula
behavior. So this works:

Function getgenre(shtno, highrow, lowrow)
Dim evalRange As Range, s As String, res As Variant
Dim genreloccol As Long
genreloccol = 2

Set evalRange = Range(Worksheets(shtno).Cells _
(highrow, genreloccol), Worksheets(shtno) _
.Cells(lowrow, genreloccol))
s = "'" & Worksheets(shtno).Name & "'!"
e = s & evalRange.Address(0, 0)
sStr = "Index(" & e & ",Match(Max(Countif(" & e & "," _
& e & ")),Countif(" & e & "," & e & "),0),1)"
res = Evaluate(sStr)

getgenre = res

End Function