PDA

View Full Version : Write formula in VBA-'Error 13'



wouldbeca
08-12-2014, 04:25 AM
I am trying to make a long code. My everything works smooth except i got stuck on this part of code.:banghead:




Sheets("SCB_Rejection_Working").Select
Range("A5").Formula = "=IF(ROW($A1:A1)>COUNTIF(SCB_Rejection!A:A," * ")-1,"",SCB_Rejection!M2)"


Your help is highly appreciable.

GTO
08-12-2014, 04:44 AM
Greetings wouldbeca,

There is no reason to select the sheet first, but disregarding that for the moment, you need to 'double-up' the quote marks within the string, to have it write the formula to the sheet correctly.


Sheets("SCB_Rejection_Working").Select
Range("A5").Formula = "=IF(ROW($A1:A1)>COUNTIF(SCB_Rejection!A:A,""*"")-1,"""",SCB_Rejection!M2)"


I hope that helps,

Mark

westconn1
08-12-2014, 04:48 AM
what is it supposed to do?
row($a1:a1) will always have to return 1

wouldbeca
08-12-2014, 05:03 AM
Actually, This formula I am going to auto fill o cells below in next step which I had not mentioned here.



what is it supposed to do?
row($a1:a1) will always have to return 1

wouldbeca
08-12-2014, 05:04 AM
Thanks mate.. Cheers!!!


Greetings wouldbeca,

There is no reason to select the sheet first, but disregarding that for the moment, you need to 'double-up' the quote marks within the string, to have it write the formula to the sheet correctly.


Sheets("SCB_Rejection_Working").Select
Range("A5").Formula = "=IF(ROW($A1:A1)>COUNTIF(SCB_Rejection!A:A,""*"")-1,"""",SCB_Rejection!M2)"


I hope that helps,

Mark