Option
Explicit
Friend
Function
SchreibeEintrag(Gebaude, Standort, Bezeichnung,
Optional
SN,
Optional
ID,
Optional
EQUI)
As
Long
If
Trim$(Gebaude) =
""
Or
Trim$(Standort) =
""
Or
Trim$(Bezeichnung) =
""
Then
Exit
Function
ElseIf
IsMissing(SN)
And
IsMissing(ID)
And
IsMissing(EQUI)
Then
Exit
Function
End
If
Dim
rngGebaude
As
Excel.Range
Dim
rngStandort
As
Excel.Range
Dim
rngBezeichnung
As
Excel.Range
Dim
rngSN
As
Excel.Range
Dim
rngID
As
Excel.Range
Dim
rngEQUI
As
Excel.Range
Set
rngGebaude = Range(
"A2"
, Cells(Rows.Count,
"A"
).
End
(xlUp))
If
rngGebaude.Row >= 2
Then
Dim
rngTreffer
As
Excel.Range
Dim
strTreffer1
As
String
Dim
blnTreffer
As
Boolean
Set
rngTreffer = rngGebaude.Find(Gebaude, , xlValues, xlWhole, xlByColumns, ,
False
)
If
Not
rngTreffer
Is
Nothing
Then
strTreffer1 = rngTreffer.Address
Do
Set
rngStandort = rngTreffer.Offset(0, 1)
Set
rngBezeichnung = rngTreffer.Offset(0, 2)
If
rngStandort.Value = Standort
And
rngBezeichnung.Value = Bezeichnung
Then
blnTreffer =
True
Exit
Do
End
If
Set
rngTreffer = rngGebaude.FindNext(rngTreffer)
Loop
While
rngTreffer.Address <> strTreffer1
End
If
If
blnTreffer =
False
Then
Set
rngTreffer = rngGebaude.Offset(rngGebaude.Rows.Count).Cells(1)
End
If
Else
Set
rngTreffer = Range(
"A2"
)
End
If
Application.EnableEvents =
False
If
blnTreffer =
False
Then
Set
rngGebaude = rngTreffer
Set
rngStandort = rngTreffer.Offset(0, 1)
Set
rngBezeichnung = rngTreffer.Offset(0, 2)
Set
rngSN = rngTreffer.Offset(0, 3)
Set
rngID = rngTreffer.Offset(0, 4)
Set
rngEQUI = rngTreffer.Offset(0, 5)
rngGebaude.Value = Gebaude
rngStandort.Value = Standort
rngBezeichnung.Value = Bezeichnung
Else
Set
rngGebaude = rngTreffer
Set
rngStandort = rngTreffer.Offset(0, 1)
Set
rngBezeichnung = rngTreffer.Offset(0, 2)
Set
rngSN = rngTreffer.Offset(0, 3)
Set
rngID = rngTreffer.Offset(0, 4)
Set
rngEQUI = rngTreffer.Offset(0, 5)
End
If
If
Not
IsMissing(SN)
Then
rngSN.Value = SN
If
Not
IsMissing(ID)
Then
rngID.Value = ID
If
Not
IsMissing(EQUI)
Then
rngEQUI.Value = EQUI
Application.EnableEvents =
True
SchreibeEintrag = rngGebaude.Row
End
Function