vba - Excel Geomean returns #value! sometimes? -

vba - Excel Geomean returns #value! sometimes? -

i've modified vba function below suit needs.

i have many workbooks sheets contain 4500+ rows, , utilize function search 2 given values (as boundaries). then, selects rows range. finally, whatever on range. function:

function geom(a, b) application.volatile dim x integer dim y integer dim rng range x = application.worksheetfunction.match(a, range("b:b"), 0) ' looking in col b y = application.worksheetfunction.match(b, range("b:b"), 0) ' looking in col b set rng = range(cells(x, 18), cells(y, 18)) 'im working on col 18 geom = application.worksheetfunction.geomean(rng) end function

the problem is, code works fine except geomeann. noticed when range of info relatively little (number of info cells) returns value. however, if range larger approx. 126 cells, returns #value!.

i'm stuck , working on solving issue. geomean function limited given number of data?

thanks

there appears 170 character limit on testing before excel versions (i tested in xl03), validated in mr excel thread

(xl10 worked fine on longer dataset)

i tried:

using evaluate using 1d array

failed samples

dim x set rng1 = range("a1:a171") msgbox evaluate("geomean(a1:a171)") x = application.transpose(rng1) msgbox application.worksheetfunction.geomean(x)

to no avail.

so think 2 workarounds either:

inserting formula via vba excel , using result as per mrexcel thread utilize derivation of geomean, ie =exp(average(ln(range)))

suggested approach

msgbox evaluate("exp(average(ln(a1:a171)))")

excel vba excel-formula