#Value! Error on Sheet1 in Excel 2003

try here: Excel Programming / VBA / Macros

I have been to the link above and many other places on the internet. I even tried Excel 2010 to see if my old version of Excel was faulty. There is no difference and I can not find information directly related to my problem. I need guidance from someone who has created their own functions and seen this problem. I am sure it is a simple fix.

I know this function does not have any inputs. I simplified the code so that only the necessary parts are in the code to show the problem. I need to be able to run a function or sub from one cell (F6 for example) on Sheet1 and produce a result (as if I typed it directly) to another cell (F4 in this example) on the same Sheet1. I have read all the links you have suggested over the past few weeks and tried multiple ways to run a function from one cell and write to another. All of them work perfectly when you hit the Run option in the Visual Basic Script Editor, but when you go to Sheet1, type or pull down the function into F6 (or any cell), and hit enter, you get the #VALUE! error. I can read from any cell, but I cannot write to any cell except the one the function is in by returning the function value. I should be able to do this from everything I have read so far on the internet. I think there is a simple step that I am missing and cannot seem to find. Sandy, have you tried running my code on a new Excel file on Sheet1?

Thank you for all your help, Sandy. You got me on the right track. You are right. Excel will not allow you to write a function that will write to another cell. I found some information on the web that enabled me to do what I was trying to do. You have to use a workaround. I am enclosing a few URLs that helped me to fix my problem. I am now able to monitor certain cells for modification, call my function, and hardcode the results to a target cell. I settled on the Worksheet_Change event to fix my code (the first URL listed). I was mysteriously losing data sometimes when I changed sheets.

https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel

http://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function

https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

Here is my vba code on sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim KeyCells As Range

' The variable KeyCells contains the cells that will cause an alert when they are changed.

Set KeyCells = Sheet1.Range("J3:J12") 'KeyCells range

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been changed.
' Place your code here.

Sheet1.Range("J15").Value = mdLast(KeyCells.Address) 'Write to destination cell - no code
‘ MsgBox "Cell " & Target.Address & " has changed." ‘Uncomment to show message

End If
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Sub

Glad if it works for you

01repsolv.gif