Help with Counta and Worksheet_Change Event

zoog25

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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top