Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Matrixformel in VBA Code
#1
Hallo alle miteinander!
Nach gefühlten 1000 versuchen brauche ich jetzt mal wieder eure Hilfe und wissen!
Ich möchte gerne die Matrixformel die in Tabellenblatt Spiele ab A9 steht in einen VBA code haben.
Zum besseren verständniss: Im Tabellenblatt Startblatt stehen in der Spalte B5-B13 und B16-B21 Namen.
Diese sollen, wenn in C5-C13 und C16-C21 eine 1 steht dann in das Tabellenblatt Spiele ab A9 eingetragen werden. Wie bekommt mann das mit VBA hin?

mfg

Michael


Angehängte Dateien
.xls   Michael_forum1.xls (Größe: 511,5 KB / Downloads: 19)
mfg
Michael
98

WIN 7 Office 2007
to top
#2
Hallo Michael,

mal eine VBA-Lösung ohne Formeleintrag.

Code:
Sub prcMichael()
    Dim rngName As Range
    Dim lngZeile As Long
    
    lngZeile = 9
    Worksheets("Spiele").Range("A9:A23").ClearContents
    For Each rngName In Worksheets("Startblatt").Range("C5:C13,C16:C21")
        If rngName.Value = 1 Then
            Worksheets("Spiele").Cells(lngZeile, 1).Value = rngName.Offset(0, -1).Value
            lngZeile = lngZeile + 1
        End If
    Next rngName
End Sub
Gruß Stefan
Win 7 / Office 2007
to top
#3
Hallo michel34497,

um eine Matrix-Formel in Excel mit VBA zu erstellen, kannst Du die Eigenschaft FormulaArray verwenden.
Beispiel für Deine Zelle A9 im Spiele-Blatt. Die Formel muss dann aber in Englisch sein.
Code:
Public Sub Test()
    
    ThisWorkbook.Worksheets("Spiele").Cells(9, 1).FormulaArray = _
   "=INDEX(Startblatt!$B:$B,SMALL(IF(Startblatt!$C$5:$C$21=1,ROW($5:$21)),ROW()-8))"
    
  End Sub
Gruß
Der größte Aberglaube der Gegenwart ist der Glaube an die Vorfahrt.
Jacques Tatischeff - auch bekannt als Jacques Tati
to top
#4
Hallo!
Vielen Dank für die Hilfe.
Habe alles getestet und funktioniert so wie die Fragestellung auch war.
Doch leider ist mir noch etwas aufgefallen was ICH nicht bedacht habe.
Es gibt bei uns immer nachzügler, heißt sie kommen eine halbe oder Stunde Später und wollen dann an dem Spiel Teilnehmen.
Wenn ich dann die Namen mit dem Code erneut einfüge passt das nicht mehr mit den Spielstand.
Wie kann man dies lösen?
mfg
Michael
98

WIN 7 Office 2007
to top
#5
Hallo Michael,

für welche Lösung gilt jetzt deine Nachfrage?
Gruß Stefan
Win 7 / Office 2007
to top
#6
Hallo Stefan!
Für deinen Code.
Wenn ich mit deinen Code die Namen eintragen lasse und wir angefangen haben zu spielen passt noch alles.
Kommt dann ein sogenannter Nachzügler und ich den Code erneut laufen lasse passen die Punkte hinter den Namen stehen nicht mehr überein.
Es müssten die Punkte die hinter den Namen stehen (Spalten B-G und I-L) nachgeführt werden.
Ist das möglich?
mfg
Michael
98

WIN 7 Office 2007
to top
#7
Hallo Michael,

kommt es häufig vor, das es Nachzügler gibt? Und wie wäre es, wenn Du diesen Nachzügler händisch in die Tabelle einfügst?
Gruß Stefan
Win 7 / Office 2007
to top
#8
Hallo Stefan!
Nicht so oft!
Händisch geht schon, aber es geht dann wieder um die schreibweis dort kann es dann zu problemen führen bei der Übertagung der Beträge.
Was aber noch ein anderse Thema ist. Deswegen möchte ich das gerne über das Startblatt lösen (alle Namen die dort eingetragen sind und noch werden) und bei bedarf den Code neu ausführen.


PS. Wie hat dir das mit Berechnung gefallen wo du nachgefragt hast 19
mfg
Michael
98

WIN 7 Office 2007
to top
#9
Hallo,

Deine Lösung hast Du ja schon, aber ich hätte da mal ne Frage ...
warum muß denn das überhaupt so dringend eine Matrix-Formel sein?

Mit wievielen Leuten rechnest Du eigentlich?

Und das mit bei Bedarf den Code neu ausführen, da fallen mir ohne großartig nachzudenken gleich zwei einfache Möglichkeiten ein.
1. per Button
2. automatisch, wenn beispielsweise ein Eintrag in der nächsten Zeile passiert.
________________________________________________________________________
wer aufgibt, ohne es versucht zu haben, gibt einfach nur auf!

?mage
to top
#10
Hallo Michael,

(10.05.2014, 22:02)michel34497 schrieb: Händisch geht schon, aber es geht dann wieder um die schreibweis dort kann es dann zu problemen führen bei der Übertagung der Beträge.

ich würde hier mit einer bedingten Formatierung arbeiten. Gehe hierzu auf das Tabellenblatt Startblatt, markier den Bereich B5:B22 (es könnte eigentlich auch der Bereich B5:B13 reichen) und benenne ihn zum Beispiel als Startliste. Gehe nun auf das Tabellenblatt Spiele markier den Bereich A9:A20 und mache hier eine bedingte Formatierung (Formel zur Ermittlung....) und gebe als Formel

Code:
=ZÄHLENWENN(Startliste;A9)=0

ein, wähle noch eine andere Schriftfarbe (oder Zellhintergrund) aus. Jetzt sollte, wenn Du anstatt einer Gaby eine Gabi eingibst, die Zelle farbig markiert werden und Du erkennst deinen Schreibfehler.

(10.05.2014, 22:02)michel34497 schrieb: PS. Wie hat dir das mit Berechnung gefallen wo du nachgefragt hast 19

??? Über deinen Code hatte ich im damaligen Thema ja schon was geschrieben und auch mein Code wäre noch zu überarbeiten, was aber an der Funktionalität nichts ändern würde.
Gruß Stefan
Win 7 / Office 2007
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Matrixformel zur Auflistung von Zeilen Hawkeye 8 637 17.04.2016, 12:34
Letzter Beitrag: BoskoBiati
  Code vorübergehend per Code ändern! cysu11 9 1.682 11.12.2014, 19:06
Letzter Beitrag: cysu11
  Matrixformel Sven_1991 4 1.139 09.10.2014, 10:44
Letzter Beitrag: Sven_1991
  Matrixformel per VBA (Excel 2003) Silki 34 10.704 18.04.2014, 20:34
Letzter Beitrag: atilla

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste