Insert formula in one cell using VBA

0
votes

I know this topic was already asked and I tried to copy on how to insert a formula in one cell, however, I got an error in my vba code.

enter image description here

Here is my code:

ws.Range("C9").Formula = "=CountIf(wsRD.Range(C & Rows.count).End(xlUp).Row, ""Event"")"   'CountIf(wsRD.Range("C" & Rows.count).End(xlUp).Row, "Event") 'count(Search("Event", wsRD.Range("C" & Rows.count).End(xlUp).Row, 1))

I need to insert a formula in ws.Range("C9"), in which, it summarizes the count of the cell that have a value of "Event" in wsRD.Range("C" & Rows.count).End(xlUp).Row. May I know what's the problem in my code? Appreciate your help.

Thank you.

2
vbaexcelexcel-formula
You are combining vba syntax with excel syntax. When you are inside the quotes, use excel syntax - urdearboy
Are you wanting to count every instance of Event from C10 to the last row? - urdearboy
yes, I need to count every instance of Event from C10 to last row - pinkpanther

2 Answers

2
votes

You could get rid of the LRow variable and just drop it in your equation if you wanted to.

Dim LRow as Long
LRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row

ws.Range("C9").Formula = "=COUNTIF(C10:C" & LRow & ", ""Event"")"
1
votes

I'm sure this could be the correct answer

ws.Select
LRow = ws.Range("C" & Rows.Count).End(xlUp).Row

Range("C9").FormulaLocal = "=COUNTIF(C10:C" & LRow & ";""Event"")"

So basically, I used FormulaLocal to write the formula the same way I write it in Excel, then, because the formula must be a big String, I separated it in 2 strings, put the value LRow, and used & & to concatenate