Are you using it as a function in a cell with something like:
=rre_Salary(celladdress,celladdress,##,##)
??
If so, whenever the sheet recalculates,
it's using the Active Cell as a reference location.
If you enter a date and hit "tab", then the active cell is the cell to the right
(unless you've changed your settings)
If you hit a return, the active cell is one below the changed cell.
Then, the macro takes the COLUMN NUMBER of the column
two cells to the left of the active cell, and compares it to the month of the
date entered.
without seeing the sheet, I think this looks suspicious.
Anytime you use a function that relies on the correct cell being "selected" is
undependable.
I would suggest rewriting this as a change event.
check to see if the changed cell is where you expect a hiredate to be entered,
then process
it based on specified locations...
if you want, send me the file and I can look closer.
Paul
________________________________
From: Jacob <app...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, August 20, 2010 10:16:57 AM
Subject: $$Excel-Macros$$ User Defined Function not updating
I have a user defined function that is not updating. The way it works
is based on HireDate. It looks at the hiredate and compares it to the
budget year. IF the budget year is in the same year as the hire date
then it looks at the row which could C:N or Jan-Dec. It works fine
until I actually input a hire date in the current year. It then zeros
everything out. If I go through the cell and hit "F2" that is = or
after the hire date month then it recalculates. I would like for it
to auto recalculate without having to hit "F2" on the cell.
Any Help would be greatly appreciated.
Thanks,
Jacob
Function rre_Salary(HireDate As Variant, Rate As Long, Weeks As
Integer, Allocation As Integer)
Dim BudgetYear As Integer
BudgetYear = Sheets("Primary Info").Range("C11").Value
If Year(HireDate) = BudgetYear Then
If ActiveCell.Column - 2 >= Month(HireDate) Then
rre_Salary = Rate * Allocation * Weeks
Else
rre_Salary = 0
End If
Else
rre_Salary = Rate * Allocation * Weeks
End If
End Function
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
