[Solved] Countif written in macro returns zero value

[sanjyot]

I have written a formula in the macro for spreadsheet to calculate as below . This works fine when i paste it in the cell but returns zero when I put it in the macro. Can anybody please explain what is going wrong?
oSheetKeys.getCellByPosition(10,i+2) ="=countif($C$3:C3;C3)"

[RusselB]

Welcome to the forums.
While I admit that my knowledge of macros is almost nil, I have to wonder about the fact that you have the countif formula inside quotation marks.
Have you tried oSheetKeys.getCellByPosition(10,i+2) = countif($C$3:C3;C3)

[FJCC]

The object returned by

Code: Select all

oSheetKeys.getCellByPosition(10,i+2)

refers to the whole cell, all of its properties and methods. If you want to set the Formula of the cell, do this

Code: Select all

oSheetKeys.getCellByPosition(10,i+2).Formula ="=countif($C$3:C3;C3)"

Judging from the i in your code, you are doing this in a loop. The result will be to set every cell to have the exact same formula. Is that what you want?

[sanjyot]

Thanks FJCC. oSheetKeys.getCellByPosition(10,i+2).Formula ="=countif($C$3:C3;C3)" This worked. I was putting. value instead of Formula.

[sanjyot]

Yes..its a loop I need to have the formula incremented except $C$3. I have used this. oSheetKeys.getCellByPosition(10,i+2).Formula ="=countif(Keys.$C$2" + ":Keys.C" + (i+2) + ";Keys.C" + (i+2+1) + ")". Although it works if you have an easier way and simpler please do suggest. I am new to macros