Write formula in VBA-'Error 13'

[wouldbeca]

I am trying to make a long code. My everything works smooth except i got stuck on this part of code.

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]

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]

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

[wouldbeca]

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]

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