Sub
Flexibler_Als_Sverweis()
Debug.Print Now
Dim
i
As
Long
, Zeile
As
Long
, letzteZeile
As
Long
Dim
Rahmen
As
String
, System
As
String
Dim
Modell
As
String
, Size
As
String
, Zustand
As
String
Dim
Arbeitsmappe
As
Workbook
Dim
Datenbasis
As
Worksheet
Dim
Ziel
As
Worksheet
Dim
ZelleFirma
As
Range
Dim
Bereich
As
Range
Set
Arbeitsmappe = ThisWorkbook
Set
Datenbasis = Arbeitsmappe.Worksheets(
"Montagebericht"
)
Set
Ziel = Arbeitsmappe.Worksheets(
"Lagerbestand"
)
letzteZeile = Datenbasis.Range(
"B"
& Rows.Count).
End
(xlUp).Row
Set
Bereich = Datenbasis.Range(
"A1:A"
& letzteZeile)
For
i = 4
To
Ziel.Range(
"B"
& Rows.Count).
End
(xlUp).Row
Modell = Datenbasis.Range(
"B1"
).Value
Size = Datenbasis.Range(
"E3"
).Value
Zustand = Datenbasis.Range(
"A6"
).Value
With
Databasis
Set
ZelleFirma = Bereich.Find(Modell & Size & Zustand)
If
ZelleFirma
Is
Nothing
Then
Rahmennummer =
""
Systemnummer =
""
Ziel.Range(
"G"
& i).Value = Rahmennummer
Ziel.Range(
"H"
& i).Value = Systemnummer
Else
Zeile = ZelleFirma.Row
Rahmennummer = .Range(
"B4"
).Value
Systemnummer = .Range(
"B5"
).Value
Ziel.Range(
"G"
& i).Value = Rahmennummer
Ziel.Range(
"H"
& i).Value = Systemnummer
Set
ZelleFirma =
Nothing
End
If
End
With
Next
i
Debug.Print Now
End
Sub