User Defined Function used in Macro returns #VALUE
 

So I wrote a function in excel 97 that works fine. when i select it an
copy it down the row, it works fine. But when I have a macro copy it
the cells with my function return the #VALUE error. If i click in th
formula bar and press enter, it recalculates fine. I don't want to d
this 100 times, though and asking the macro to re calculate or pressin
the 'calculate now' button does not help.
This is the code for my function but, as I said, it works fine until
macro tries to copy it.

Function FindEndOfDrawDown(Peak As Range, Valley As Range, Rng A
Range)
Dim myCell As Range
Dim Past As Boolean
Past = False
For Each myCell In Rng
If Past = True Then
If myCell.Value Peak.Value Then
FindEndOfDrawDown = myCell.Value
Exit Function
End If
Else
If myCell.Value = Valley.Value Then
Past = True
End If
End If
Next myCell
End Function

Please let me know if you have any idea what's going on. I have th
feeling it's excel 97 that's screwing up but i cant find any solutio
on the microsoft support website.
thanks

Nic

--
Message posted from http://www.ExcelForum.com

User Defined Function used in Macro returns #VALUE
 

xl2k (I think) added an application.calculatefull.

Maybe you could use this:

Application.SendKeys "%^{F9}"

And if that didn't work, how about recording a macro when you:
Select the copied range
Edit|Replace
what: = (equal sign)
with: = (equal sign)
replace all.

And add that recorded code to your existing macro.

"hodler <" wrote:

So I wrote a function in excel 97 that works fine. when i select it and
copy it down the row, it works fine. But when I have a macro copy it,
the cells with my function return the #VALUE error. If i click in the
formula bar and press enter, it recalculates fine. I don't want to do
this 100 times, though and asking the macro to re calculate or pressing
the 'calculate now' button does not help.
This is the code for my function but, as I said, it works fine until a
macro tries to copy it.

Function FindEndOfDrawDown(Peak As Range, Valley As Range, Rng As
Range)
Dim myCell As Range
Dim Past As Boolean
Past = False
For Each myCell In Rng
If Past = True Then
If myCell.Value Peak.Value Then
FindEndOfDrawDown = myCell.Value
Exit Function
End If
Else
If myCell.Value = Valley.Value Then
Past = True
End If
End If
Next myCell
End Function

Please let me know if you have any idea what's going on. I have the
feeling it's excel 97 that's screwing up but i cant find any solution
on the microsoft support website.
thanks

Nick

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson