JE McGimpsey;

Brilliant workaround idea ! addressing the cells directly without Selecting
or Activating anything outside the w/s where the Function is used!

Your version of the Function's code works perfectly! I simply added OPTION
BASE 1 at the top of the module, and replaced:
......NumOfRows = .Range("C14").Value (compile error) by
......NumOfRows = .Range("C14").Value

Thank you kindly for your help. Greatly appreciated.



"JE McGimpsey" wrote:

Worksheet functions, including User Defined Functions called from the
worksheet, can only return values to their calling cells. They can't
change values in other cells, nor can they activate anything.

You can address the ranges in your tables directly, but you can't select
them. This might give you a start (untested):

Public Function GEL_Reg_2(mySheetIndex, _
arg1, arg2, arg3, arg4, arg5, arg6)
Dim myParm As Variant
Dim mySheet As Worksheet
Dim myReg As Range
Dim NumOfRows As Long
Dim i As Long
Dim j As Long
Dim mySumR As Double
Dim mySum As Double

myParm = Array(arg1, arg2, arg3, arg4, arg5, arg6)
Set mySheet = Worksheets(mySheetIndex)
With mySheet
NumOfRows = .Range("C14").Value
For i = 1 To NumOfRows
Set myReg = .Cells(21 + i - 1, 3).Resize(1, 7)
mySumR = 1
For j = 1 To 6
mySumR = mySumR * myParm(j) ^ myReg(1, j + 1)
Next j
mySum = mySum + myReg(1, 1) * mySumR
Next i
End With
GEL_Reg_2 = mySum
End Function


Note that, by default, Array() returns a zero based array unless you
have Option Base 1 at the top of your module.





In article ,
"monir" wrote:

The following simple custom function returns #Value!
The function does not appear to activate mySheet, nor assigns a value to
NumOfRows.