Trying to use Countif in a formula

Question

How to use countif in a formula

I did some research on google and other forums but did not find a solution. it's not a syntax error so it's not my double quotes that is the issue. the error is run time 1004, application defined or object defined error.

Cells(a, 1).Formula = "=Countif(Range(cells(" & a & ",7),cells(" & a & ",21))),""a"""

In the first case, a is 5, so i want it to scan columns g through u in row 5, count any occurrences of the letter 'a'

Expected a number counting all the occurences of "a" in a row, instead im getting a 1004 error

just a note to the moderators, on my last post, i was demoted i guess for asking a dumb question. how can i get that fixed as the thread is closed.

You are aware that you are using Range and Cells in a worksheet formula? Are you not looking to use Application.WorksheetFunction.CountIf? Or are you trying to populate a cell with a formula? — JvdV
– JvdV, Commented Jul 24, 2019 at 11:27

JvdV · Accepted Answer · 2019-07-24 11:39:11Z

1

So based on your response in the comments section I think you are looking to use:

Sub PutFormulaInCell()

Dim a As Long
a = 5

With ThisWorkbook.Sheets("Sheet1")
    .Cells(a, 1).Formula = "=Countif(G" & a & ":U" & a & "," & """a"")"
End With

End Sub

Right now you have mixed up VBA and Excel syntax's resulting in a 1004 error.

answered Jul 24, 2019 at 11:39

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DanM Over a year ago

yup, that did the trick. and so simple. and i see where i made the error, won't happen again.

JvdV Over a year ago

Glad it helped @DanM, please accept as answer. Much appreciated

Collectives™ on Stack Overflow

Trying to use Countif in a formula

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related