Option
Explicit
Public
Sub
step()
Dim
wks
As
Excel.Worksheet
Dim
rngDaten
As
Excel.Range
Set
wks = Worksheets(
"Tabelle1"
)
Set
rngDaten = wks.Range(
"A1:D4"
)
If
Not
SetMarker(rngDaten, rngDaten.Columns.Count)
Then
If
vbYes = MsgBox(
"Der Endzustand wurde erreicht."
& vbNewLine & _
vbNewLine & _
"Soll zurückgesetzt werden?"
, _
vbQuestion
Or
vbYesNo) _
Then
Call
rngDaten.ClearContents
End
If
End
If
End
Sub
Private
Function
SetMarker(Range
As
Excel.Range,
ByVal
Index
As
Long
,
Optional
Value =
"X"
)
As
Boolean
Dim
rngColumn
As
Excel.Range
Dim
rngMarker
As
Excel.Range
If
Index < 1
Then
Exit
Function
SetMarker =
True
Set
rngColumn = Range.Columns(Index)
Set
rngMarker = rngColumn.Find(Value, LookIn:=xlValues, LookAt:=xlWhole)
If
Not
rngMarker
Is
Nothing
Then
If
rngMarker.Row < rngColumn.Cells(rngColumn.Cells.Count).Row
Then
rngMarker.Offset(1).Value = Value
Else
SetMarker = SetMarker(Range, Index - 1, Value)
If
SetMarker
Then
rngColumn.Cells(1).Value = Value
End
If
If
SetMarker
Then
Call
rngMarker.ClearContents
Else
rngColumn.Cells(1).Value = Value
End
If
End
Function