Daten zusammmenführen

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Daten_zusammmenführen() For t = 1 To 2 If t = 1 Then s = 2 Else s = 1 Sheets(t).Select For i = 1 To Cells(65536, 1).End(xlUp).Row KundenNr = Cells(i, 1) With Sheets(s).Columns(1) Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) If B Is Nothing Then With Sheets(3) r = .Cells(65536, 1).End(xlUp).Row + 1 .Cells(r, 1) = KundenNr .Cells(r, 2) = Cells(i, 2) End With Else Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) Umsatz = .Cells(B, 2) With Sheets(3).Columns(1) Set C = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) If C Is Nothing Then r = .Cells(65536, 1).End(xlUp).Row + 1 .Cells(r, 1) = KundenNr .Cells(r, 2) = Cells(i, 2) + Umsatz End If End With End If End With Next i Next t End Sub

Hallo,
ich habe das Makro mit Excel 97 und Excel 2000 getestet und es funktionierte,
ich noch drei Variablen gesetzt und eine Korrektur gemacht, an welcher Stelle meldet er den Fehler.
Gruss Beni


Sub Daten_zusammmenführen()
Dim t As Long
Dim i As Long
Dim Lz As Integer
For t = 1 To 2
If t = 1 Then s = 2 Else s = 1
Sheets(t).Select
Lz = Cells(65536, 1).End(xlUp).Row
For i = 1 To Lz
KundenNr = Sheets(t).Cells(i, 1).Value
With Sheets(s).Columns(1)
Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole)
If B Is Nothing Then
With Sheets(3)
r = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(r, 1) = KundenNr
.Cells(r, 2) = Cells(i, 2)
End With
Else
Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole)
Umsatz = .Cells(B, 2)
With Sheets(3).Columns(1)
Set C = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole)
If C Is Nothing Then
r = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(r, 1) = KundenNr
.Cells(r, 2) = Cells(i, 2) + Umsatz
End If
End With
End If
End With
Next i
Next t
End Sub

Sub Daten_zusammmenführen() Dim t As Long Dim i As Long Dim Lz As Integer For t = 1 To 2 If t = 1 Then s = 2 Else s = 1 Sheets(t).Select Lz = Cells(65536, 1).End(xlUp).Row For i = 1 To Lz KundenNr = Sheets(t).Cells(i, 1).Value With Sheets(s).Columns(1) Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) If B Is Nothing Then With Sheets(3) r = .Cells(65536, 1).End(xlUp).Row + 1 .Cells(r, 1) = KundenNr .Cells(r, 2) = ActiveSheet.Cells(i, 2) End With Else Set B = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) Umsatz = .Cells(B, 2) With Sheets(3).Columns(1) Set C = .Find(KundenNr, LookIn:=xlValues, LookAt:=xlWhole) If C Is Nothing Then r = .Cells(65536, 1).End(xlUp).Row + 1 .Cells(r, 1) = KundenNr .Cells(r, 2) = Cells(i, 2) + Umsatz End If End With End If End With Next i Next t End Sub

Beliebteste Forumthreads (12 Monate)

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread