Excel's R1C1 Formula

[Harddisk]

Hi,

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


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

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

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.

[BrianB]

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.

[Harddisk]

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


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.