Escaping for Countif macro with wildcard cell reference

Question

I cant for the life of me get the escapes working correctly for my macro. I am trying to input a Countif function that uses wildcards on either side of a cell reference value, it also finds the last row within a separate tab to help define the range to count in.

I want the target cell to contain the following string:

=COUNTIF('NCR''s'!$B$8:$C$117,"*"&'Design Baseline'!B8&"*")

The following is the code that attempts to do this:

Sub test()

Dim Wild1 As String
Dim Wild2 As String
Wild1 = Chr(34) & Chr(42) & Chr(34) & Chr(38)
Wild2 = Chr(38) & Chr(34) & Chr(42) & Chr(34)

'code for counting instances of parts on each tab
Sheets("NCR's").Select
    Cells.Select
    Selection.EntireRow.Hidden = False
    NCRlastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Sheets("Design Baseline").Select
    Cells.Select
    Selection.EntireRow.Hidden = False
    'Range("AB8").Formula = "=COUNTIF('NCR''s'!$B$8:$C$" & NCRlastRow & "," & Chr(34) & "*" & Chr(34) & "&" & "'Design Baseline'!B29" & "&" & Chr(34) & "*" & Chr(34) & ")"
    Range("AB8").Formula = "=COUNTIF('NCR''s'!$B$8:$C$" & NCRlastRow & "," & Wild1 & "'Design Baseline'!B29" & Wild2 & ")"

End Sub

As you can see in the line beginning with ', I have tried to use the Chr(ref) method to get around this as well as using user defined strings for the ", * and & symbols. I have also attempted the double " escape and even ". Not sure how to get around this o.O

The following line was what I used before realizing I needed wildcards and worked fine, not sure if it would help but I'll put it here regardless:

Range("AC8").Formula = "=COUNTIF('NCR''s'!$B$8:$B$" & NCRlastRow & ",'Design Baseline'!B8)"

Any help would be greatly appreciated!

The line doesnt like it as there are sheet references within it that use ' ('Design Baseline') — C_Harris, Apr 27 '17 at 10:02

score 0 · Accepted Answer · answered Apr 27 '17 at 12:22

0

You need to escape the double quotes in your formula. Something like this:

Dim formula As String
formula = "=COUNTIF('NCR''s'!$A$8:$C$" & NCRlastRow & ",""*"" &'Design Baseline'!B8&""*"")"
Debug.Print formula
Range("AB8").formula = formula

answered Apr 27 '17 at 12:22

Phil

397
2
8

Ahh thank you! this seems to have done the job! Not sure what went wrong with it when I tried escaping the quotes originally but problem solved now :) – C_Harris Apr 27 '17 at 12:45

Escaping for Countif macro with wildcard cell reference

1 Answers1