Write formula in VBA-'Error 13'

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

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.

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.

Code:

---

  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

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

Actually, This formula I am going to auto fill o cells below in next step which I had not mentioned here.

Quote:

---

Originally Posted by westconn1

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

---

Thanks mate.. Cheers!!!

Quote:

---

Originally Posted by 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.

Code:

---

  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

---