Error 1004: Application-defines or object-defined error

Hello there,

while trying to write a script I get this error.
Here is minimal script which reproduces the error (working with Win XP and Office 2003 Sp3).

Function foo()
On Error GoTo Hell
Dim i As Integer
For i = 1 To 4
Dim r As Integer
r = Application.Caller.Row
Dim c As Integer
c = Application.Caller.Column
Dim s As Integer
s = Application.Caller.Worksheet.Index
MsgBox ("Sheets(" & s & ").Cells(" & r & ", " & c & ")")
Sheets(s).Cells(r + i, c + i) = "bar" ' this line produces the error
'Sheets(1).Cells(1,1) = "bar" produces the same error :(
Next i
foo = i
Exit Function
Hell:
MsgBox "Error-No.: " & Err.Number & vbNewLine & vbNewLine _
& "Description: " & Err.Description _
, vbCritical, "Fehler"
End Function

I tried to ask google, but I could not find any working results.
I really hope anybody here can point me to a solution.

Regards

It looks like you're using this function as a workcell equation. Workcell equations cannot assign values to other cells, change sheet names, and several other things even if you call another function or sub from within the workcell function. You might want to explore using the Worksheet_Change event procedure instead.

Dim r As Integer
Not relevant to your immediate problem, but a variable to receive a row number should be a Long.

Hi,

Kluz - yes you are right, I tried to change values of other than the calling cell. I will try to get it in Worksheet_Change.

shg- thanks for your advice.

Regards