Prozedur mit Schleife ???

Forumthread: Prozedur mit Schleife ?

Prozedur mit Schleife ?

marc

hallo zusammen,
in einem Workbook muss ich immer die konstante Zellen (Werte ) loeschen.
Der Code ist sehr lang.
Vielleich kann ich den Code mit eine Schleife verkuerzen.
Weis aber nicht wie ich den Code unschreiben muss.
Gruss marc :o)
Bitte nicht erschrecken !


Sub SheetsValue_delete02()
With Worksheets("M90.1")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M90.1")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M90.2")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M90.2")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M90.3")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M90.3")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M93.1")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M93.1")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M93.2")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M93.2")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M93.3")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M93.3")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("M93.4")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
End With
With Worksheets("M93.4")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
End With
With Worksheets("TL90")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
.Range("X7:X11").Value = ""
.Range("X16:X20").Value = ""
End With
With Worksheets("TL90")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
.Range("X28:X32").Value = ""
.Range("X37:X41").Value = ""
End With
With Worksheets("TL93")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
.Range("X7:X11").Value = ""
.Range("X16:X20").Value = ""
End With
With Worksheets("TL93")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
.Range("X28:X32").Value = ""
.Range("X37:X41").Value = ""
End With
With Worksheets("AL")
.Range("S7:V11").Value = ""
.Range("S16:V20").Value = ""
.Range("V12", "V21").Value = ""
.Range("X7:Y11").Value = ""
Range("X16:Y20").Value = ""
End With
With Worksheets("AL")
.Range("S28:V32").Value = ""
.Range("S37:V41").Value = ""
.Range("V33", "V42").Value = ""
.Range("X28:Y32").Value = ""
.Range("X37:Y41").Value = ""
End With
End Sub
<pre/>

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Prozedur mit Schleife ?

Ramses

Hallo
Also um einiges kürzer würde es ja schon, wenn du die einzelnen Tabellen zusammenfassen würdest ;-))
...
With Worksheets("M90.1")
.Range("S7:V11,S16:V20, V12,V21,S28:V32,S37:V41,V33,V42").Value = ""
End With
With Worksheets("M90.2")
.Range("S7:V11,S16:V20, V12,V21,S28:V32,S37:V41,V33,V42").Value = ""
End With
usw...
Gruss Rainer

AW: Prozedur mit Schleife ?

Nepumuk

Hallo Marc,
so:

Sub SheetsValue_delete02()
    Dim varArray As Variant, intIndex As Integer
    varArray = Array("M90.1", "M90.2", "M90.3", "M93.1", "M93.2", "M93.3", "M93.4", "TL90", "TL93", "AL")
    For intIndex = 0 To 6
        Worksheets(varArray(intIndex)).Range("S7:V11,S16:V20,V12,V21,S28:V32,S37:V41,V33,V42").Value = ""
    Next
    For intIndex = 7 To 9
        Worksheets(varArray(intIndex)).Range("S7:V11,S16:V20,V12,V21,X7:X11,X16:X20,S28:V32,S37:V41,V33,V42,X28:X32,X37:X41").Value = ""
    Next
End Sub

Gruß
Nepumuk

AW: Prozedur mit Schleife ?

hi Marc,
oder so:


Sub SheetsValue_delete03()
Dim strAuswahl As String
Dim intIndex As Integer
For intIndex = 1 To 7
strAuswahl = Choose(intIndex, "M90.1", "M90.2", "M90.3", "M93.1", "M93.2", "M93.3", "M93.4")
Worksheets(strAuswahl).Range("S7:V11,S16:V20,V12,V21,S28:V32,S37:V41,V33,V42").Value = ""
Next
For intIndex = 1 To 3
strAuswahl = Choose(intIndex, "TL90", "TL93", "AL")
Worksheets(strAuswahl).Range("S7:V11,S16:V20,V12,V21,X7:X11,X16:X20,S28:V32,S37:V41,V33,V42,X28:X32,X37:X41").Value = ""
Next
End Sub

cu Micha

AW: Danke Micha.........

marc

hallo Micha,
besten Dank fuer dein schnellen Tipp.
marc :o)

AW: Marc :o) sagt DANKE.

marc

hallo Rainer und Nepumuk.
vielen dank fuer die schnelle Antwort.
Ich werde den Code von meinem ersten Beitrag gleich umschreiben.
-
marc :o)

OT Hallo Nepumuk...

K.Rola

...schaust du bitte nochmal in den thread von gestern.
Gruß K.Rola

Beliebteste Forumthreads (12 Monate)

Forumthread: Prozedur mit Schleife ?

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread