Microsoft Excel

Herbers Excel/VBA-Archiv

For-to-Loop + Select auf anderem Arbeitsblatt


Betrifft: For-to-Loop + Select auf anderem Arbeitsblatt
von: Max F.
Geschrieben am: 28.11.2018 12:02:36

Hallo zusammen.
Da die Suche leider kein Ergebnis erbracht hat, nun hier:

Ich sitze an der Änderung eines bestehenden Formulars meines Vorgängers.
hierbei habe ich folgendes Problem:

Das erste Tabellenblatt fungiert als Formular, das befüllt wird, es folgen einige (ausgeblendete) mit Datensätzen und Berechnungen im Hintergrund, sowie ein weiteres, auf dem die Ausstattung der einzelnen Kollegen aus den zuvorigen Eintragungen generiert wird. Hier erfolgt nur noch eine Dropdownauswahl in einem einzigen Punkt.
Und hier liegt die Krux...

Es ist notwendig, auf dem ersten Tabellenblatt eine Gruppe auszuwählen (dies habe ich von meinem Vorgänger übernommen, welcher dies über Kontrollkästchen gelöst hat - nicht optimal, aber naja...)
In Abhängigkeit dieser Auswahl müssen auf dem letzten Tabellenblatt eine Menge Zellen mit einer Formel versehen werden (kein Problem)
und (und hier liegt mein Problem) die Dropdownauswahl angepasst werden.

Dies habe ich über die Makroaufzeichnung laufen lassen, und folgendes Ergebnis erhalten:


Sheets("Ausstattung").Range("B6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Datenerfassung!$C$403:$C$447"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With    

Nun ist es aber notwendig, diese Anpassung in mehreren Zellen in wiederkehrendem Muster _
durchzuführen - naheliegendster Schluss: For-To-Step-Schleife, mit folgendem Ergebnis (Ergänzt _ um die Eintragung der Formeln):


 For i = 6 To 84 Step 2

    Sheets("Ausstattung").Range("B" & i ).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Datenerfassung!$C$403:$C$447"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True

    Sheets("Ausstattung").Range("C" & i).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1], _
Datenerfassung!R342C2:R399C6,4,FALSE))"
    Sheets("Ausstattung").Range("D" & i).FormulaR1C1 = "=IF(RC[-2]="""","""",VLOOKUP(RC[-2], _
Datenerfassung!R342C2:R399C6,5,FALSE))"
    Sheets("Ausstattung").Range("E" & i).FormulaR1C1 = "=IF(RC[-3]="""","""",VLOOKUP(RC[-3], _
Datenerfassung!R342C2:R399C6,3,FALSE))"
    
    Next i

Soweit so gut - zumindest so lange ich das Makro vom Arbeitsblatt "Ausstattung" aus laufen lasse.
Wie gesagt sind aber die Eintragungen auf dem ersten Blatt nötig.

Integriere ich nun diesen Code in das Makro der Kontrolllkästchen, welche vom ersten Blatt aus laufen, erhalte ich den Laufzeitfehler 1004 -_-

Das Zerlegen des Codes und die entsprechende Fehlersuche hat ergeben, dass ich im Rahmen der Schleife keine Zelle auf einem anderen Arbeitsblatt .Select-en kann.

Kennt jemand das Problem, und hat ggf eine Lösung parat?

PS: Ein Ersatz der Range-Funktion durch Cells hat keine Änderung gebracht.

  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Torsten
Geschrieben am: 28.11.2018 12:09:25

Lass das Select weg (eh unnoetig) und wende With direkt auf die Range an.

For i = 6 To 84 Step 2

    With Sheets("Ausstattung").Range("B" & i )
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Datenerfassung!$C$403:$C$447"
        .IgnoreBlank = True
        .InCellDropdown = True
usw....
Gruss Torsten


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Max F.
Geschrieben am: 28.11.2018 12:57:04

Merci, Torsten - das ging ja schnell.

Das Select-Problem ist damit beiseite geschafft.
Jetzt bringt er allerdings einen Laufzeitfehler 438 bei .Add Type usw. ... *freu*

Und ich habe leider keine Ahnung, wo ich jetzt mit der Fehlersuche anfangen soll...
Ich habe das Makro auf ein leeres Tabellenblatt umgeleitet, das hat keine Änderung gebracht.

Na mal schauen, ob ich was finde...


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Daniel
Geschrieben am: 28.11.2018 13:01:46

Hi
das selection kannst du weglassen, dass Validation aber nicht!

With Sheets("Ausstattung").Range("B" & i ).Validation
Gruß Daniel


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Torsten
Geschrieben am: 28.11.2018 13:07:30

oh yes, Daniel. Danke


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Max F.
Geschrieben am: 28.11.2018 13:08:27

Danke, Daniel!

Das war es - jetzt klappt es!


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Torsten
Geschrieben am: 28.11.2018 13:06:49

mit Add Type willst du doch nur die Formel in die Zelle schreiben, richtig?


  

Betrifft: AW: For-to-Loop + Select auf anderem Arbeitsblatt
von: Max F.
Geschrieben am: 28.11.2018 13:51:14

...einen Dropdown, der sich aus dem Datenerfassungsblatt speist, erstellen, ja...

Daniel hatte das Problem identifiziert: in deinem Vorschlag hat nur .Validation nach der Range gefehlt.
Jetzt klappt es auf jeden Fall!

Danke euch beiden =)