create a Dynamic CountIf String for cell equation VBA

Question

I have a table where new data is continuously inserted. If the category exists in Column B, data is inputted in a new row under that category from B#:F#. But if it doesn't exist a new row is created for that category title and then data is inserted underneath. Column A includes a Count for each category and is placed in cell left to the first instance a category appears. For example if column b had 6 "orange" occurrences, then a count is to the left of (col A) the first instance of orange.

How can I ensure that if a new category (lets say "hello") which is set to the variable tgt is added in a new row, that the countif statement in the code dynamically changes to the value that is set to tgt?

Dim tgtVal As String
         tgtVal = tgt.Value
         tgt.Offset(0, -1).FormulaR1C1 = "=COUNTIF(C[1], " & tgtVal & ")-1"

Now a count does pop up in the sheet next to the newly inserted category however it is not the correct count. The equation is

"=COUNTIF(B:B, Hello)-1" with a count of -1 in the cell instead of =COUNTIF(B:B, "Hello")-1 with a count of 1

If I add another pair of quotes in the code to attempt to include quotes in the countif statement in the sheet like:

tgt.Offset(0, -1).FormulaR1C1 = "=COUNTIF(C[1], "" & tgtVal & "")-1" then it shows up as =COUNTIF(B:B, " & tgtVal & ")-1 as the equation in the cell

How do I make sure it comes up as "tgtVal" and not tgtVal in the cell's equation?

Unknown Unknown · Accepted Answer · 2016-07-28T05:05:11

When trying to build a string literal, you should output the result to the immediate window. This will help you detect if the any errors.

This is the result of your code snippet "=COUNTIF(C[1], "" & tgtVal & "")-1". Notice that & tgtVal & is actually part of the string.

=COUNTIF(C[1], " & tgtVal & ")-1

Here are two ways to get the correct result =COUNTIF(C[1],"")-1

String literal

"=COUNTIF(C[1],""" & tgtVal & """)-1"

Using the ASCII code for a double quote

"=COUNTIF(C[1]," & Chr(34) & tgtVal & Chr(34) & """)-1"

create a Dynamic CountIf String for cell equation VBA

1 Answers