0

I'm trying to insert the following equation into a sheet:

=COUNTIFS(email_logs_output.csv!$AB$2:$AB$26731, _
                            usersFullOutput.csv!S26, _
                            email_logs_output.csv!$R$2:$R$26731, _
                            "<>"&"", _
                            email_logs_output.csv!$H$2:$H$26731, _
                            "gift*")

I'm getting stuck at the criteria "<>"&"". Here's what I have:

With Worksheets(users_sheet)
    equation_range.FormulaR1C1 = "=COUNTIFS(" & emails_sheet & "!R2C" & email_col & ":R" & rows_email & "C" & email_col & " , RC[" & col_back & "], " _
                                   & emails_sheet & "!R2C18:R" & rows_email & "C18, "" " <> " & "" "")"
End With

When I run it, all I get is TRUE in every cell in equation_range.

How do I get the equation to include "<>"&""?

Community
  • 1
  • 1
DBWeinstein
  • 8,605
  • 31
  • 73
  • 118
  • The escape character for quotation marks in a string is double quotes (2 quotes inside of quotes = 1 quote) so `"<>"""""` http://stackoverflow.com/a/216623/1274820 – user1274820 Apr 03 '15 at 14:41
  • When I run the code with `...C18, " <> """"")"` I get `TRUE` in every cell in the range. What am I doing wrong? – DBWeinstein Apr 03 '15 at 14:49
  • tried this as well: `...C18, """ <> "" & """"")"`, and I still get True in every cell in the range. – DBWeinstein Apr 03 '15 at 14:53

1 Answers1

0

Just tried one formula and this works
[a1].FormulaR1C1 = "=countif(R1C3,""<>"" & ""a"")"
So in your case
...C18,""<>"" & "" "")"

Notes

Basicaly, whenever you have "text" in your formula just put ""text"" and that should do the trick.

Another thing : Beware of the "autospreading" of the code in VBA environment. Check for non wanted spaces added automaticaly where you may not want them.

kolcinx
  • 2,183
  • 1
  • 15
  • 38
  • Using your answer I came up with `...C18, ""<>""&"""")"`. It's what your wrote, but with no spaces. Per your comment, the spaces make a difference. Thanks!!!! – DBWeinstein Apr 03 '15 at 15:17
  • @dwstein Ou, OK. I thought you actually want to check if the cell has a *space*. My bad, but glad it helped. – kolcinx Apr 03 '15 at 15:37