Using Array Index function in a sub

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

--
Regards,
Tom Ogilvy


"General" <[email protected]> wrote in message
news:[email protected]...
> 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
>