See if value exists multiple times in column

[Hatye]

Hi,

I have a dropdown list in cell B11 in sheet1 where I can select values from column A in sheet2.
Everything works okay, but I would like to have a quality routine that alerts me if the selected value in B11 exists more than once in column A in sheet2. That is, because this value is used several places in this sheet with lookup-function, and it only returns the first values content.

So, this is my code:

Public Sub Worksheet_Change2(ByVal Target As Range, Cancel As Boolean)Application.ScreenUpdating = False


    Dim KeyCells As Range


    Set KeyCells = Range("B11:D11")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        MsgBox "Det eksisterer minst to punkt med navnet" & Target.Address & " . Vennligst kontroller punktlisten!"
       
    End If
    
End Sub

Does anyone got a suggestion on code to use to search through column A in sheet 2 to see if the selected value exists more than once?

 

[MickG]

Try this code in sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, R As Range
If Target.Address(0, 0) = "B11" Then
With Sheets("Sheet2")
    Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
MsgBox "Count of value " & Target.Value & " in sht2 = " & Application.CountIf(Rng, Target)
End If
End Sub

 

[Hatye]

Hi and thank you for your reply,

Unfortunately, nothing happens when I run this one. I have configured it to my needs, but it doesnt work.
And: I would only like to get a notification if the value in cell B11 exists more than once in the column A in the sheet "Kumkort_Import". Not a message every time the value changes (as it seems like the code is doing as for now?).

Here is my modified code pasted in the sheet where I change the value in cell B11 (called "Kumkort").

Private Sub Worksheet_Change3(ByVal Target As Range)

Dim Rng As Range, R As Range


If Target.Address(0, 0) = "B11" Then
    With Sheets("Kumkort_Import")
        Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
    End With


    MsgBox "Count of value " & Target.Value & " in sht2 = " & Application.CountIf(Rng, Target)


End If


End Sub

 

[Joe4]

It looks like you are trying to use Event Procedure VBA code (i.e. Worksheet_Change), which is triggered automatically.
Note that in order for this to work, you CANNOT change the name of it! It has to start out like:

Private Sub Worksheet_Change(ByVal Target As Range)

The minute you add a number to the end of the Procedure name (like 2 or 3), it is no longer an automatically called Event Procedure code, and will not run automatically.

 

[Hatye]

Oh, I see.
I guess that it is not possible to have multiple events with the same name (Worksheet_Change)? Then I need to add this code into my other Worksheet_Change code?

 

[Joe4]

I guess that it is not possible to have multiple events with the same name (Worksheet_Change)? Then I need to add this code into my other Worksheet_Change code?

That is correct. You can only have one Worksheet_Change Event Procedure code per worksheet. You would just put all the code into the one Worksheet_Change event Procedure (or you could have multiple procedures that are called by the single Worksheet_Change Event Procedure).

 

[Hatye]

Ah, thank you so much
I just emedded everything into my already excisting code