Namen vs. Namensfeld
25.12.2004 15:07:55
NE
weiss nicht, obs am code liegt oder an was ganz andrem,
wollte alle mehrfachen Werte in Spalte A zu Namen zusammenfassen,
nur wieso bekomme ich die Dinger nicht im Namensfeld angezeigt?
Danke all denen die mal nen Blick riskieren ;-)
Gruss Nancy
--
Sub doppelte()
Dim cell As Range, myrange As Range, flag As Boolean
Dim i&, n&, r&, f%, x%, s$, c%, a
c = 1: r = Cells(Rows.Count, c).End(xlUp).Row
a = Range(Cells(1, c), Cells(r, c))
ReDim b$(0): b(0) = ""
For i = 1 To r
flag = False
s = a(i, 1)
For x = 0 To f
If b(x) = s Then
flag = True
Exit For
End If
Next
If Not flag Then
b(f) = s
n = 0
For Each cell In Range(Cells(1, c), Cells(r, c))
If cell.Value = s Then
If n = 0 Then Set myrange = cell _
Else Set myrange = Union(myrange, cell)
n = n + 1
End If
Next
If n > 1 Then Names.Add s, ActiveSheet.Name & myrange.Address
f = f + 1
ReDim Preserve b(f)
End If
Next
End
Sub