leere zelle erkennen

Public Sub DatenEingabe() Dim a, b, c With ActiveSheet For a = 8 To 11 For b = 7 To 43 Step 6 For c = 2 To 17 Step 5 If .Cells(b, c) = Tabelle2.Cells(a, 7) Then .Cells(b, c + 2) = Tabelle2.Cells(a, 5) End If Next Next Next End With End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi Ralf,
du hast nicht geschrieben, was bei gleichen Daten passieren soll.
Mit deinem Code steht am Ende der Wert zum letzten Treffer im aktiven Blatt.
Soll es nun der Wert zum ersten Treffer sein?
Oder sollen die Werte zu allen Treffern hinter einander (mit Trennzeichen " / ") ausgegeben werden?
Probier mal diese drei Varianten:


Option Explicit
Public Sub DatenEingabe1()
Dim zz2 As Long, zzA As Long, ssA As Long
With ActiveSheet
For zzA = 7 To 43 Step 6
For ssA = 2 To 17 Step 5
For zz2 = 8 To 11
If .Cells(zzA, ssA) = Tabelle2.Cells(zz2, 7) Then
.Cells(zzA, ssA + 2) = Tabelle2.Cells(zz2, 5)
Exit For
End If
Next zz2
Next ssA
Next zzA
End With
End Sub
Public Sub DatenEingabeL()
Dim zz2 As Long, zzA As Long, ssA As Long
With ActiveSheet
For zzA = 7 To 43 Step 6
For ssA = 2 To 17 Step 5
For zz2 = 8 To 11
If .Cells(zzA, ssA) = Tabelle2.Cells(zz2, 7) Then
.Cells(zzA, ssA + 2) = Tabelle2.Cells(zz2, 5)
End If
Next zz2
Next ssA
Next zzA
End With
End Sub
Public Sub DatenEingabeS()
Dim zz2 As Long, zzA As Long, ssA As Long
With ActiveSheet
For zzA = 7 To 43 Step 6
For ssA = 2 To 17 Step 5
For zz2 = 8 To 11
If .Cells(zzA, ssA) = Tabelle2.Cells(zz2, 7) Then
.Cells(zzA, ssA + 2) = .Cells(zzA, ssA + 2) & _
IIf(IsEmpty(.Cells(zzA, ssA + 2)), "", " / ") & _
Tabelle2.Cells(zz2, 5)
End If
Next zz2
Next ssA
Next zzA
End With
End Sub

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Sub DatenEingabeMR() Dim zz2 As Long, zzA As Long, ssA As Long, spA As Long With ActiveSheet For zzA = 7 To 43 Step 6 For ssA = 2 To 17 Step 5 spA = 1 For zz2 = 8 To 11 If .Cells(zzA, ssA) = Tabelle2.Cells(zz2, 7) Then spA = spA + 1 If spA > 3 Then MsgBox "Achtung - zu viele Treffer" Exit For Else .Cells(zzA, ssA + spA) = Tabelle2.Cells(zz2, 5) End If End If Next zz2 Next ssA Next zzA End With End Sub Sub DatenEingabeMU() Dim zz2 As Long, zzA As Long, ssA As Long, zzP As Long With ActiveSheet For zzA = 7 To 43 Step 6 For ssA = 2 To 17 Step 5 zzP = 0 For zz2 = 8 To 11 If .Cells(zzA, ssA) = Tabelle2.Cells(zz2, 7) Then If zzP > 5 Then MsgBox "Achtung - zu viele Treffer" Exit For Else .Cells(zzA + zzP, ssA + 2) = Tabelle2.Cells(zz2, 5) zzP = zzP + 1 End If End If Next zz2 Next ssA Next zzA End With End Sub

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread