Help with Counta and Worksheet_Change Event

[zoog25]

Hello All,

Here is my code i'm having issues with:

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.

 

[jim may]

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

 

[Akuini]

Try this:

[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]

Try:

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]

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]

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