Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

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

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

    Code:
    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
    Last edited by steve the fish; Sep 13th, 2019 at 09:47 AM.
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Value of Cell = COUNTIF with changing criteria taken from list - VBA

    Quote Originally Posted by steve the fish View Post
    Does this do what you require? May have to change the sheet name and row references.

    Code:
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •