0

This is my VBA code:

Sub AutoRunMacro()
    Range("N5").Formula = "=SUM(COUNTIF(F5:M5;""*Pending*"")+COUNTIF(F5:M5;""*Rejected*"")+COUNTIF(F5:M5;""*Expired*"")+COUNTIF(F5:M5;""*Incomplete*"")+COUNTIF(F5:M5;""*Empty*"")) / COUNTIF(F5:M5;""<>*N/A*"")"
End Sub

Every time I open this file, the macro is executed, but this error shows up:

Run-time error '1004' : Method 'Range' of object'_Global' failed

Do you guys know how to solve it? Thanks!

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • You should use `,` instead of `;` if using `Range.Formula` but that error message seems to suggest another line not in your current code is failing. – BigBen Dec 14 '20 at 15:38
  • Well it actually stops in the formula line, i'll try what u said –  Dec 14 '20 at 15:38
  • Wow yes, it worked, by the way, do you know if I can format cells as a percentage in my formula? –  Dec 14 '20 at 15:40
  • Also the outer `SUM` is superfluous. – BigBen Dec 14 '20 at 15:40
  • `Range.NumberFormat = ....`? – BigBen Dec 14 '20 at 15:41
  • `Range("N5").NumberFormat.Formula =` ? It's my first time using VBA sorry –  Dec 14 '20 at 15:43
  • `Range("N5").NumberFormat = "0.00%"` – BigBen Dec 14 '20 at 15:44
  • Actually the first time I open the file doesn't work, it works when I close it and reopen it. Maybe it executes the formula before filling the table with data. –  Dec 14 '20 at 15:55
  • "Doesn't work" doesn't describe a specific problem or error. Seems like you may want to ask a new question with all relevant code, including the problem, error message, or unwanted behavior. – BigBen Dec 14 '20 at 16:00
  • Well, doesn't work, it appears the same error `Run-time error '1004' : Method 'Range' of object'_Global' failed`, but ok, I'll try it in another question thanks. –  Dec 14 '20 at 16:11

0 Answers0