in den 6 Tabellen werden in Spalte A A5:A36 willkürlich Zahlen eingetragen.
Es darf keine Zahl zweimal vorkommen. Ein Makro sollte dafür sorgen.
Gruß Adi
https://www.herber.de/bbs/user/58785.xls
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myTab As Worksheet
Dim Kontrolle As Boolean
Dim Wert As Variant
Kontrolle = False
Wert = Target.Value
With Application
.EnableEvents = False
For Each myTab In ThisWorkbook.Worksheets
If myTab.Name <> Sh.Name And Left(Sh.Name, 3) = "Bel" Then
If IsNumeric(.Match(Target.Value, Sh.Columns(1), 0)) Then
Kontrolle = True
Exit For
End If
End If
Next myTab
If Kontrolle Then
Target.Value = ""
MsgBox "Der Wert " & Wert & " ist schon vergeben"
End If
.EnableEvents = True
End With
End Sub
Gruß Tino
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myTab As Worksheet
Dim Kontrolle As Boolean
Dim Wert As Variant
Dim iCounter As Integer
Kontrolle = False
Wert = Target.Value
With Application
.EnableEvents = False
With Application.WorksheetFunction
For Each myTab In ThisWorkbook.Worksheets
If Left(Sh.Name, 3) = "Bel" Then
If .CountIf(myTab.Columns(1), Target) > 0 Then
iCounter = iCounter + .CountIf(myTab.Columns(1), Target)
If iCounter > 1 Then
Kontrolle = True
Exit For
End If
End If
End If
Next myTab
End With
If Kontrolle Then
Target.Value = ""
MsgBox "Der Wert " & Wert & " ist schon vergeben"
End If
.EnableEvents = True
End With
End Sub
Gruß Tino
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myTab As Worksheet
Dim Kontrolle As Boolean
Dim Wert As Variant
Dim iCounter As Integer
Kontrolle = False
Wert = Target.Value
With Application
.EnableEvents = False
With Application.WorksheetFunction
For Each myTab In ThisWorkbook.Worksheets
If Left(Sh.Name, 3) = "Bel" Then
If .CountIf(myTab.Range("A5:A36"), Target) > 0 Then
iCounter = iCounter + .CountIf(myTab.Columns(1), Target)
If iCounter > 1 Then
Kontrolle = True
Exit For
End If
End If
End If
Next myTab
End With
If Kontrolle Then
Target.Value = ""
MsgBox "Der Wert " & Wert & " ist schon vergeben"
End If
.EnableEvents = True
End With
End Sub
PS: ich heiße Tino, nicht Timo (so viel Zeit muss sein ;-))
Gruß Tino