Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1840to1844
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Schleife fü Code für folgende Spalten

Schleife fü Code für folgende Spalten
09.08.2021 13:55:20
Friedo
Hallo zusammen,
ich hoffe mir kann jemand helfen: Ich bin absolut kein Programmierer oder in der Richtung gut bewandert. Ich habe mit VBA bis jetzt schon meine Tabelle soweit bearbeitet, dass ich meinen Code für eine Spalte funktionsfähig habe (siehe unten). Dabei ist dies leider hart gecodet und auf explizite Zellen gecodet (B16-B32).
Jetzt stehe ich vor dem Problem, dass ich quasi das gleiche Vorgehen auch für Spalten B-G, Leerspalte, I-K, Leerspalte und M-R haben möchte. Ich hatte mich mal versucht mit einer Do While Schleife, aber ich komme mit der Laufvariablen nicht klar, und wie ich die in meiner If-Bedingung dann einbauen muss. Der Code soll also auf alle Spalten angewendet werden, außer die beiden Leerspalten und dann ein Abbruchkriterium nach der Spalte R besitzen (welches wäre egal, was mir jetzt da einfällt: wenn 2 spalten hintereinander leer, oder man ein unsichtbares Wort in die Zelle einbaut "Ende" z.b.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B26:B32").FormatConditions.Delete
Range("B26:B32").Interior.ColorIndex = 0
Dim intCol As Integer
intCol = 2
If Range("B16").Value = "na" And Range("B17").Value = "na" Then
Range("B26:B32").Interior.ColorIndex = 15
ElseIf Range("B16").Value  "na" And Range("B17").Value = "na" And Range("H5").Value  "VN800/VN600 HFO" Then
Set barrange = Range("B26:B27")
Range("B28:B32").Interior.ColorIndex = 15
barrange.FormatConditions.AddDatabar
With barrange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barrange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
ElseIf Range("B16").Value  "na" And Range("B17").Value  "na" Then
Set barrange = Range("B26:B30")
Range("B31:B32").Interior.ColorIndex = 15
barrange.FormatConditions.AddDatabar
With barrange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barrange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
ElseIf Range("B16").Value  "na" And Range("H5").Value = "VN800/VN600 HFO" Then
Range("B26:B30").Interior.ColorIndex = 15
Set barrange = Range("B31:B32")
barrange.FormatConditions.AddDatabar
With barrange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barrange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
End If
Loop
End Sub
Hier mein versuch dder Do While schleife, bzw zumindest der Anfang

Do While Len(Cells(16, intCol).Formula)  ""
intCol = intCol + 1 
Ich hoffe sehr, dass mir jemand helfen kann. Ich komme selber nicht weiter
MfG

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Schleife fü Code für folgende Spalten
09.08.2021 14:16:48
Daniel
HI
könnte so gehen:
1. die FOR-Schleife über die Spalten so außen drum legen:
dim SP
For Each SP in Array(2, 3, 4, 5, 6, 7, 9, 10, 11, 13, 14, 15, 16, 17, 18) 'alle Spaltennummern auflisten:
...
Next
2. innerhalb der Schleife sprichst du dann die Zellen / Ranges konsequent nicht mehr über Range, sondern über Cells an.
geht genauso, nur dass man Zeilen und Spalte getrennt und als Zahl angibt, was die Sache einfacher macht.
aus Range("B16") wird Cells(16, SP) aus Range("B17") wird Cells(17, SP)
die Ranges über mehrere Zellen erstellst du mit Cells+Resize um die Größe anzugeben.
dh aus Range("B26:B32") wird Cells(26, SP).Resize(7, 1)
dh du gibst über Cells mit den Variablen die Linke obere Zelle an und dann mit Resize die Größe des Zellbereichs in Zeilen und Spalten, hier 7 Zeilen und eine Spalte.
aus Range("B31:B32") wird dann Cells(31, SP).Resize(2, 1)
allerdings stellt sich für mich die Frage, ob dieser Code im Change-Event dann richig aufgehoben ist.
Gruß Daniel
Anzeige
AW: Schleife fü Code für folgende Spalten
09.08.2021 14:30:48
Rudi
Hallo,
teste mal:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B26:B32").FormatConditions.Delete
Range("B26:B32").Interior.ColorIndex = 0
Dim intCol As Integer
Dim barRange As Range
For intCol = 2 To 18
Select Case intCol
Case 2 To 7, 10 To 11, 13 To 18
Select Case True
Case Cells(16, intCol) = "na" And Cells(17, intCol) = "na"
Range(Cells(26, intCol), Cells(32, intCol)).Interior.ColorIndex = 15
Case Cells(16, intCol)  "na" And Cells(17, intCol) = "na" _
And Range("H5").Value  "VN800/VN600 HFO"
Set barRange = Range(Cells(26, intCol), Cells(27, intCol))
Range(Cells(28, intCol), Cells(32, intCol)).Interior.ColorIndex = 15
barRange.FormatConditions.AddDatabar
With barRange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barRange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
Case Cells(16, intCol)  "na" And Cells(17, intCol)  "na"
Set barRange = Range(Cells(26, intCol), Cells(30, intCol))
Range(Cells(31, intCol), Cells(32, intCol)).Interior.ColorIndex = 15
barRange.FormatConditions.AddDatabar
With barRange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barRange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
Case Cells(16, intCol)  "na" And Range("H5").Value = "VN800/VN600 HFO"
Range(Cells(26, intCol), Cells(30, intCol)).Interior.ColorIndex = 15
Set barRange = Range(Cells(31, intCol), Cells(32, intCol))
barRange.FormatConditions.AddDatabar
With barRange.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With barRange.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
End Select
End Select
Next intCol
End Sub
Gruß
Rudi
Anzeige
AW: Schleife fü Code für folgende Spalten
09.08.2021 15:05:53
Friedo
Moin,
danke für deine Antwort. So ganz passt es leider noch nicht:
Zunächst habe ich nochmal

Case 2 To 7, 10 To 11, 13 To 18
zu 9 to 11 geändert.
Es passt aber leider irgendwas jetzt noch nicht mit den bars und dem ansonsten grau einfärben. Es sollte wie folgt sein: Es wird in Zeile 16 bzw 17 abgefragt, ob da "na" steht, wenn nicht, sollen die range, die in meine Code waren z.b.Range ("26 und 27) der Balken gesetzt werden und die übrigen bis 32 in grau hinterlegt werden.
Wenn ich den neuen Code teste, klappt das für B auch noch alles, danach kommen allerdings 2 Sachen durcheinander:
Zum einen gibt es auch bei einigen na Spalten noch Balken, teilweise auch in einem Blauton und unter dem Balken befindet sich auch noch die graue interior Farbe. Woran könnte das liegen? Muss evtl. das format delete woanders hin als an den Anfang?
Und hierzu noch eine Frage:

Range(Cells(31, intCol), Cells(32, intCol)).Interior.ColorIndex = 15
In meinem Code wird das mit ":" anstatt des "," geschrieben, da Ich auch die Zellen dazwischen so einfärbe, passt das mit dem neuen Code auch?
Vielen Dank
Anzeige
passt das? ja. owT
09.08.2021 15:39:31
Rudi
AW: passt das? ja. owT
09.08.2021 15:58:44
Friedo
Hattest du was geschrieben? sehe keinen Text @ Rudi^^
AW: passt das? ja. owT
09.08.2021 16:06:48
Daniel
wenn mit dem Betreff alles gesagt, ist, lassen mache den Text im Beitrag weg und kennzeichnen das durch den hinweis "ohne weiteren Text"
generell, bei Range hast du prinzipell zwei Möglichkeiten, den Zellbereich zu definieren:
a) mit einem Parameter. Dieser Wert muss ein Textstring sein, der die Adresse des Zellbereichs enthält
b) mit zwei Parametern. hierbei kennzeichnen die beiden Parameter die Eckzellen des zu definierenden Bereichs, der Bereich besteht dann aus allen Zellen, die zwischen diesen beiden Zellen liegen.
Gruß Daniel
Anzeige
AW: passt das? ja. owT
09.08.2021 17:29:41
Friedo
Ah okay, wusste ich nicht. Verstehe ich dann trotzdem nicht ganz. Hatte ja geschrieben, dass es noch nciht ganz passt mit Erklärung. Werde mir deinen Lösungsvorschlag dann auch nochmal angucken, aber vllt klappt Rudis ja auch irgendwie noch. An sich sah das schon nicht verkehrt aus.
Irgendwas mit den Balken und den Farben passt noch nicht, wie gesagt. Könnte sein, das das Format deleting von den vorherigen Schleifen irgendwoanders eingebaut werden muss, und nicht beim beiginn der Funktion nur.
MfG
AW: passt das? ja. owT
10.08.2021 18:36:43
Yal
Moin!
trotz alle gute, schon vorhandenen Vorschläge habe ich mir den Spass gemacht, den Code auf meinem Gusto anzupassen :-)
Alles, was sich wiederholt, sollte in eine "Sub" abgelagert, diemit Parameter gefüttert wird.
Die "If"-Verschachtelung sind -vielleicht- leichter zu entflechten, wenn diese Baum-artig abgelegt sind.
Kannst Du mit folgenden "ausseinandernehmen" zu recht kommen? Es ist gar nicht kompliziert.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 9, 12
'nichts. Es wird nur als Ausnahme verwendet.
Case 2 To 18
'Default setzen
With Intersect(Target.Column, Range("26:32"))
.FormatConditions.Delete
.Interior.ColorIndex = 0
End With
'Nach Fall setzen
If Cells(16, Target.Column).Value = "na" Then
If Cells(17, Target.Column).Value = "na" Then
InteriorColor Target.Column, 26, 32, 1
End If
Else
If Range("B17").Value = "na" Then
If Range("H5").Value  "VN800/VN600 HFO" Then
InteriorColor Target.Column, 28, 32, 15
BedingteFormat Target.Column, 26, 27
End If
Else
If Range("H5").Value = "VN800/VN600 HFO" Then
InteriorColor Target.Column, 26, 30, 15
BedingteFormat Target.Column, 31, 32
Else
InteriorColor Target.Column, 31, 32, 15
BedingteFormat Target.Column, 26, 30
End If
End If
End If
End Select
End Sub
Private Sub InteriorColor(Spalte As Integer, Erste As Integer, Letzte As Integer, FarbeIndex As Integer)
With Range(Cells(Erste, Spalte), Cells(Letzte, Spalte))
.Interior.ColorIndex = FarbeIndex
End With
End Sub
Private Sub BedingteFormat(Spalte As Integer, Erste As Integer, Letzte As Integer)
With Range(Cells(Erste, Spalte), Cells(Letzte, Spalte))
.FormatConditions.AddDatabar
With .FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
.BarColor.Color = 255
.BarColor.TintAndShade = 0
End With
End With
End Sub
VG
Yal
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige