Value of Cell = COUNTIF with changing criteria taken from list - VBA

[CharlieRog]

Looking to do a COUNTIF formula but paste it whilst using concatenation to change the criteria each time e.g

In column B I have a list of place names and in column C I would like it to show the number of times that place name is present in Column Q.

Would need to be setting the value of each cell in column C to be something like "=COUNTIF(Q2:Q1028,"" & MyLocationNameCell

Then it would move down to the next cell in column C and paste again but this time MyLocationNameCell would have a new value of next place name(taken from column B)

(if you were to use MyLocationNameCell as a variable)

 

[steve the fish]

Does this do what you require? May have to change the sheet name and row references.

Dim x As Variant
Dim lr As Long

With Sheets("Sheet1")
    .Columns("C").ClearContents
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    Set x = .Range("B1:B" & lr)
    x = Evaluate("=IF(ROW(1:" & lr & "),COUNTIF('" & .Name & "'!Q2:Q1028,'" & .Name & "'!" & x.Address & "))")
    .Range("C1:C" & lr) = x
End With

 

[CharlieRog]

Does this do what you require? May have to change the sheet name and row references.

Dim x As Variant
Dim lr As Long

With Sheets("Sheet1")
    .Columns("C").ClearContents
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    Set x = .Range("B1:B" & lr)
    x = Evaluate("=IF(ROW(1:" & lr & "),COUNTIF('" & .Name & "'!Q2:Q1028,'" & .Name & "'!" & x.Address & "))")
    .Range("C1:C" & lr) = x
End With

**WORKS GREAT. Thanks!