[VBA]Target und mehrere Zellen auswählen

Moderator: ModerationP

[VBA]Target und mehrere Zellen auswählen

Beitragvon AndiS » 14. Sep 2021, 08:46

Hallo zusammen,

bräuchte wieder mal eure Hilfe! :) Ich finde leider nicht das richtige...

Im Worksheet_Change Ereignis versuch ich gerade folgendes:

Wenn in der Spalte B der Inhalt einer Zelle gelöscht wird und in der selben Zeile in Spalte AR was drin steht, sollte er den Inhalt (Spalte AR) ebenfalls löschen.
Das funktioniert auch wenn ich Zeile für Zeile durchgehe...

Sobald ich aber z.b. B8:B25 markiere und lösche, bekomm ich ein Fehler - Laufzeitfehler 13: Typen unverträglich

Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Address = "$C$1" Or Target.Address = "$C$2" Or Target.Address = "$C$3" Then
    Range("K6:AF6").ClearContents
End If

If Target.Row < 8 Then GoTo ende

Dim XMax As Double
Dim YMax As Double
Dim Laenge As Double
Dim Breite As Double

XMax = WorksheetFunction.Max(Range("M" & Target.Row & ":V" & Target.Row))
YMax = WorksheetFunction.Max(Range("W" & Target.Row & ":AF" & Target.Row))
Laenge = Worksheets("Stueckliste").Range("D" & Target.Row)
Breite = Worksheets("Stueckliste").Range("E" & Target.Row)

With Target
    If XMax >= Laenge And Laenge <> "0" Or YMax >= Breite And Breite <> "0" Then
        Rows(.Row).Font.ColorIndex = 3
    Else
        Rows(.Row).Font.ColorIndex = 1
    End If
    If .Value = "" And Worksheets("Stueckliste").Range("AR" & Target.Row).Value <> "" Then
        'MsgBox ("löschen")
        Worksheets("Stueckliste").Range("AR" & Target.Row).ClearContents
    End If
   
End With
ende:
End Sub


der Code bleibt in der Zeile stehen:
Code: Alles auswählen
    If .Value = "" And Worksheets("Stueckliste").Range("AR" & Target.Row).Value <> "" Then


was mach ich hier falsch, bzw. wie gehts richtig? Hoffe es kann mir jemand helfen!

Danke und Beste Grüße
AndiS
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 31
Registriert: 25. Jun 2020, 14:30

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon HKindler » 14. Sep 2021, 09:27

Hi,

wenn du mehrere Zellen gleichzeitig änderst, dann enthält Target alle diese Zellen. Target.Value gibt somit nicht einen einzelnen Wert, sondern ein Array zurück. Und ein Array kannst du nicht mit einem String vergleichen. Was muss man also tun? Jede Zelle von Target einzeln abklappern.

Das geht im Grunde so:
Code: Alles auswählen
Dim Bereich as Range
Dim Zelle as Range
Set Bereich = Intersect(Target, Range("C:C")
If not Bereich is Nothing then
    for Each Zelle in Bereich
        'hier machst du, was immer nötig ist
    Next Zelle
End If

Denk dran, dass du für jede Zelle deine Abmaße berechnen musst.

Und so etwas wie GoTo ende möchte ich nicht mehr sehen! Das ist schlechter Programmierstil und kann hier locker durch If Target.Row >= 8 Then ... End If ersetzt werden (wobei das End If da hin kommt, wo jetzt ende: steht).
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6308
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon AndiS » 14. Sep 2021, 10:00

Hallo und danke für die Antwort.

wie du sicherlich (an meinem Code) erkennst, bin ich blutiger Anfänger und mein programmierstil ist halt mal "schlecht". Deshalb schreib ich auch hier in dem Forum und frag euch/dich nach Hilfe... damit es nicht schlecht bleibt sondern besser wird... für sowas ist doch ein Forum da?

meine Excel mappe wächst auch und auch... und dieses goto ende wüsste ich auch jetzt auf anhieb gar nicht warum ich das eingebaut hatte... wie gesagt es wächst und wächst!

zum eigentlich Thema zurück...

ich hab dein Code Schnipsel eingepflegt...
leider klappt es nicht.... bekomm wieder die Meldung mit typen unverträglich...

und was meinst du mit jeder Zelle die Abmessungen berechnen?
AndiS
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 31
Registriert: 25. Jun 2020, 14:30

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon Ralf A » 14. Sep 2021, 10:17

@HKindler,

Und so etwas wie GoTo ende möchte ich nicht mehr sehen! Das ist schlechter Programmierstil ...


Womit Du selbstvertändlich Recht hast!
On Error Resume Next oder
If Target.Row < 8 Then GoTo ende

sind wirklich grauenvolle Beispiele einer Fehlerbehandlung oder Fallentscheidung! :(

Worauf trotzdem noch mal ingewiesen werden sollte, weil es oft mißverstanden wird.... goto im Rahmen einer Fehlerbehandlung sollte nicht mit einem normalem goto ... verwechselt werden, denn
On Error Goto ... hat absolut nichts mit dem zu Recht verpöhntem goto aus Spagetticode Zeiten zu tun, als es noch keine Unterroutinen gab und via goto wild durch den gesamten Code hin und her gesprungen wurde. On Error goto .... ist in VBA schlicht und ergreifend eine Fehlerbehandlung, die z. Bsp. in java oder C# als Try catch bekannt ist. Und Fehlerbehandlung ist niemals Ausdruck eines schlechten Programmierstils. Das Gegenteil ist der Fall.
Ciao, Ralf
Wer glaubt, für ihn persönlich würde der Bremsweg nicht als Funktion proportional zum QUADRAT der Geschwindigkeit steigen, der ist halt nicht „frei“, sondern ein Narr.
Ralf A
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1120
Registriert: 17. Mär 2018, 11:47

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon HKindler » 14. Sep 2021, 11:59

Hi,

du darfst in der for-each-Schleife natürlich nicht Target verwenden, sondern du musst auf Zelle zugreifen.

Und deine Berechnung lautet im Moment z.B.
Code: Alles auswählen
XMax = WorksheetFunction.Max(Range("M" & Target.Row & ":V" & Target.Row))
Wenn du mehrere Zeilen hast, dann gibt es nicht nur ein Target.Row sondern viele Zelle.Row und dein XMax lautet dann von Zelle zu Zelle anders, Das meinte ich mit neu berechnen.

Was deine Bemerkungen zum Programmierstil anbelangt: genau deshalb habe ich es ja geschrieben, nämlich um dich darauf aufmerksam zu machen. Ralf hat ja auch noch ein wenig ergänzt.

Und noch eine Anmerkung: schreibe ganz oben als Allererstes Option Explicit
Wieso? Google danach!
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6308
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon AndiS » 14. Sep 2021, 13:48

Nochmals vielen vielen Dank! Habs mit DEINER Hilfe hinbekommen....

Und deine Anmerkungen hab ich versucht umzusetzen.... Der Code ist für euch Profis bestimmt immer noch unterste Schublade, aber mei.... wie vorhin schon angesprochen... ich bin hier um zu lernen.... und ganz ehrlich... wenn es klappt dann bin ich froh und geh ein schritt weiter.

aber so texte wie "sowas will ich hier nicht sehen", obwohl es eigentlich nicht mal Bestandteil meiner frage war. bringt mich kein Millimeter weiter.... ich hab mir damals auf youtube Videos zu error handling und sprungmarken angeschaut... und ich dachte eigentlich das stimmt so... aber anscheinend ist es wohl doch nicht korrekt... naja ich habs halt benutzt und es hat das gemacht was ich wollte und fertig... Scheisse ist halt das ich es jetzt falsch gelernt hab! Doof oder...?
Warum ich das on error resume next eingebaut hab weiß ich nicht mehr...

Und ja googeln ist immer toll (und glaub mir, da hab ich sehr viel zeit reingesteckt...), wenn man weiß wo der Fehler liegt, man die begriffe kennt, als Anfänger natürlich viel zeit reinsteckt, etc. etc.
Option explicit steht auch drin (und kannte ich auch schon)... das steht nur ganz oben und da sind paar Makros noch dazwischen....

Wenn ich mal so ein Profi bin wie du, werd ich mich aus dem Forum abmelden, versprochen!

so genug gerechtfertigt.... hier der code, bitte nicht steinigen...

Code: Alles auswählen
'nach eingabe prüfen
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Worksheets("Stueckliste").Unprotect Password:="XXXXX"

'Kundenkopf änderung
If Target.Address = "$C$1" Or Target.Address = "$C$2" Or Target.Address = "$C$3" Then
    Range("K6:AF6").ClearContents
End If

'erst ab Zeile 8 reagieren
If Not Target.Row < 8 Then

'Plausibilitätsprüfung Abmessung/Bohrkoordinaten
    Dim XMax As Double
    Dim YMax As Double
    Dim Laenge As Double
    Dim Breite As Double
   
    XMax = WorksheetFunction.Max(Range("M" & Target.Row & ":V" & Target.Row))
    YMax = WorksheetFunction.Max(Range("W" & Target.Row & ":AF" & Target.Row))
    Laenge = Worksheets("Stueckliste").Range("D" & Target.Row)
    Breite = Worksheets("Stueckliste").Range("E" & Target.Row)
   
    With Target
        If XMax >= Laenge And Laenge <> "0" Or YMax >= Breite And Breite <> "0" Then
            Rows(.Row).Font.ColorIndex = 3
        Else
            Rows(.Row).Font.ColorIndex = 1
        End If
    End With
   
'Löscht den PFAD wenn vorhanden
    Dim Bereich As Range
    Dim Zelle As Range
    Set Bereich = Intersect(Target, Range("B:B"))
   
    If Not Bereich Is Nothing Then
        For Each Zelle In Bereich
            If Zelle.Value = "" And Worksheets("Stueckliste").Range("AR" & Zelle.Row).Value <> "" Then
                Worksheets("Stueckliste").Range("AR" & Zelle.Row).ClearContents
            End If
        Next Zelle
    End If
End If

Worksheets("Stueckliste").Protect Password:="XXXXX", AllowFiltering:=True
Application.EnableEvents = True
End Sub


Und danke nochmal!
AndiS
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 31
Registriert: 25. Jun 2020, 14:30

Re: [VBA]Target und mehrere Zellen auswählen

Beitragvon HKindler » 14. Sep 2021, 17:19

Hi,

nochmals, meine Bemerkung von wegen "will ich nicht mehr sehen" war eigentlich spaßig gemeint. Wäre wohl besser gewesen, ich hätte einen Smiley dahinter gesetzt. :wink:
Ich wollte dich nämlich wirklich nur mit einem Zwinkern darauf hinweisen, dass man das besser machen soll. Es ist wirklich nicht verboten, genauso wie es nicht verboten ist, auf Option Explicit zu verzichten.

Ich wollte dich nur - zugegebener Weise mit dem kompletten Gartenzaun und nicht nur mit dem Zaunpfahl - darauf hinweisen, dass so ein GoTo ganz schnell zu Problemen führen kann. Habe es schon am eigenen Leib erfahren, dass man so etwas programmiert und dann einige Zeit danach noch etwas in die Routine einbaut, hier z.B. irgendwas, das in Zeile 6 passieren soll. Und dann wundert man sich, wieso das, was man erreichen will, nicht tut. Nach stundenlangem debuggen merkt man dann, dass man ja ganz am Anfang bereits ans Ende Spring, wenn die Zeile kleiner 8 ist. :oops: Mit einer If-Kapselung und entsprechender Einrückung der Zeilen passiert einem das halt nicht so schnell.

Und glaub mir, auch ich bin kein Profi-Programmierer. Und was ich hier schreibe ist alles hart erarbeitet - durch Google, Foren und entsprechende Literatur. Und damit andere schneller zu einem guten Ergebnis kommen, gebe ich hier Tipps. Gerne auch ungefragt. So wie oben mit Option Explicit oder dem Hinweis, dass du auch die Berechnungen jeweils neu machen musst.
Da man aber durch selber nachdenken am schnellsten lernt, schreibe ich lieber keinen kompletten Code, sondern gebe "nur" Hinweise was zu tun ist. Da das jedoch aufwendiger ist, als einfach die Routine runter zu schreiben und du das wieder nur falsch verstehen würdest, bekommst du jetzt den Code, so wie ich ihn schreiben würde.
Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)
Dim XMax As Double
Dim YMax As Double
Dim Laenge As Double
Dim Breite As Double
Dim Bereich As Range
Dim Zelle As Range
Dim ZeilenNr As Long
Me.Unprotect Password:="XXXXX"
'Kundenkopf änderung
Set Bereich = Intersect(Target, Range("C1:C3"))
If Not Bereich Is Nothing Then Range("K6:AF6").ClearContents
Set Bereich = Intersect(Target, Range("B8:B" & Rows.Count))
If Not Bereich Is Nothing Then
    For Each Zelle In Bereich
        With Zelle
            ZeilenNr = .Row
            'Plausibilitätsprüfung Abmessung/Bohrkoordinaten
            XMax = WorksheetFunction.Max(Range("M" & ZeilenNr & ":V" & ZeilenNr))
            YMax = WorksheetFunction.Max(Range("W" & ZeilenNr & ":AF" & ZeilenNr))
            Laenge = Range("D" & ZeilenNr)
            Breite = Range("E" & ZeilenNr)
            If (XMax >= Laenge And Laenge <> "0") Or (YMax >= Breite And Breite <> "0") Then
                .EntireRow.Font.ColorIndex = 3
            Else
                .EntireRow.Font.ColorIndex = 1
            End If
            'Löscht den PFAD wenn vorhanden
            If .Value = "" Then
                Range("AR" & ZeilenNr).ClearContents
            End If
        End With
    Next Zelle
End If
Me.Protect Password:="XXXXX", AllowFiltering:=True
End Sub

Ist jetzt mit Sicherheit auch nicht perfekt. Aber ich habe nach meinem bestem Wissen gearbeitet. So habe ich z.B. bewusst Application.EnableEvents weg gelassen, da wenn, dann nur in Spalte AR etwas geändert wird, die sowieso wegen der Beschränkung auf Spalte B und Zellen C1:C3 nicht weiter berücksichtigt wird.
Wenn du dazu sonst noch Fragen oder Anmerkungen hast: nur zu!
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6308
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: AlterDresdner, EbyAS, Excelneuling83 und 18 Gäste