Help with Counta and Worksheet_Change Event

zoog25

Active Member
Joined
Nov 21, 2011
Messages
352
Hello All,

Here is my code i'm having issues with:
Rich (BB 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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Try this:

Code:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Count > [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"K:V"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Cells(Target.Row, [COLOR=brown]"A"[/COLOR]) = [COLOR=brown]"PUB"[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]If[/COLOR] WorksheetFunction.CountA(Cells(Target.Row, [COLOR=brown]"K"[/COLOR]).Resize(, [COLOR=crimson]12[/COLOR])) = [COLOR=crimson]12[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                MsgBox ([COLOR=brown]"NOC Items Completed"[/COLOR])
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
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
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
352
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,453
Messages
5,486,993
Members
407,575
Latest member
calc

This Week's Hot Topics

Top