Help with Counta and Worksheet_Change Event
Results 1 to 6 of 6

Thread: Help with Counta and Worksheet_Change Event
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Location
    Bakersfield, CA
    Posts
    317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Counta and Worksheet_Change Event

    Hello All,

    Here is my code i'm having issues with:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Count > 1 Then Exit Sub
    
    
    Set R = Intersect(Target, Range("K:Y"))
    If Not R Is Nothing Then
        For Each Cell In R
            If Cell.Value <> "" Then
                If Cell(Target, "A") = "PUB" Then
                    If Cell.CountA("K:V") = 12 Then
                        MsgBox ("NOC Items Completed")
                    End If
                End If
            End If
        Next Cell
    End If
    
    
    
    End Sub
    The issues i'm having is with Cell(Target, "A") and then Cell.CountA. With this situation with any changes made between column K and V. It first checks to see if column A says "PUB", if it does then it counts all the filled cells between K and V. If the count comes to 12 then the message box appears. Otherwise nothing happens. I have been debugging and the red is what is giving me errors. How do i fix it so it check column A and then counts cells between K and V. Thank you for any assistance that you can provide.

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,446
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counta and Worksheet_Change Event

    Where you have
    If Cell(Target, "A")...
    Where you have "Target" - Excel expects a numeric value(row number) . Target is a Range, not a numeric value...

    You might try. If Cell(Target.row, "A")...
    Hth,
    Jim
    Last edited by jim may; Apr 2nd, 2019 at 07:54 PM.

  3. #3
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,753
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with Counta and Worksheet_Change Event

    Try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
        
        If Not Intersect(Target, Range("K:V")) Is Nothing Then
            If Cells(Target.Row, "A") = "PUB" Then
                If WorksheetFunction.CountA(Cells(Target.Row, "K").Resize(, 12)) = 12 Then
                    MsgBox ("NOC Items Completed")
                End If
            End If
        End If
    
    End Sub
    

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,230
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help with Counta and Worksheet_Change Event

    Try:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        
        If Not Intersect(Target, Range("K:V")) Is Nothing Then
            If Cells(Target.Row, "A") = "PUB" And WorksheetFunction.CountA(Range("K" & Target.Row & ":V" & Target.Row)) = 12 Then
                MsgBox ("NOC Items Completed")
            End If
        End If
    End Sub
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Nov 2011
    Location
    Bakersfield, CA
    Posts
    317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counta and Worksheet_Change Event

    Thanks guys, the above items worked. It's taken me a week to figure it out, so thank you for helping me narrow the info done.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,230
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help with Counta and Worksheet_Change Event

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •