Excel's R1C1 Formula

Hi,

I have this dummy cell, which I assign a custom sub to it using this way, for eg:

Code:

---

Range("A3").FormulaR1C1Local = "=Count(R[-2]C)"

---

Based on the formula, I passed in Cell "A1" into sub Count as parameter. Hence, when I key in anything in Cell "A1", it will execute Count sub.

The problem is, in my Count sub, I have something like this

Code:

---

Public Sub Count(iInvalue as Integer)
        Range("A2").Value = iInvalue * 3
End Sub

---

Upon executing the line in Count sub, it will exit the Sub with an error

Code:

---

Run-time error '1004':

Application-defined or object-defined error

---

I was able to retrieve value from cell on the sheet though, such as
sValue = Range("A4").Value
or display a message box
msgbox "Not working"

I just can't alter value on the Cell. The worksheet is newly opened, which is NOT protected or anyhow.

Does anyone has any idea what happened?

Please advise.

Thank you.

Cannot really see what you are doing.

There is already a worksheet function called COUNT so perhaps that is where your problem lies.

If you want something to run a macro from a cell it would probably be better to make a user defined function.

What I've written earlier was just an example of course. In actual, it would be something like this instead

Code:

---

Public Sub Count(iInvalue as Integer)
        Range("A2").Value = iInvalue * 3
        Range("A3").Value = Range("A2").Value * 3
        Range("A4").Value = 5 * 3
        Range("A5").Value = Range("A4").Value * iInvalue
End Sub

---

Hope you get what I was able to do here.

And yes, it's a User Defined Macro, not a built-in function. Like I said earlier, this was just an example.

Thanks.