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.


Formel mit Makro ändern
#1
Hi, ich habe ein VBA-Problem, und komme da nicht weiter. Habe es auch schon letzte Woche in einem anderen Forum versucht, aber dort leider keine Hilfe bekommen.
Vorab: In Excel direkt bin ich meiner Meinung nach recht gut, nur in VBA habe ich mir leider noch kein richtiges Wissen aufbauen können. Die meisten Codes verstehe ich halbwegs, aber bei der Anwendung läuft fast nichts.

Ich habe ich einer Excel - Arbeitsmappe ca 150 Sheets wo jeweils unterschiedlich viele Aufträge gespeichert sind. Ich habe zum Auswerten von jedem Auftrag folgende Formel verwendet, um die Überstunden am Wochenende heraus zu suchen.


.xlsm   test.xlsm (Größe: 25,79 KB / Downloads: 8)

Diese Formel befindet sich immer in der Spalte I:I unter jedem Auftrag.

=SUMMENPRODUKT((WOCHENTAG(B11:B40;2)>5)*N11:N40)

In der Spalte B:B sind die Datumsangaben, und in Spalte N:N die Arbeitszeiten.
Ich musste dafür die Spannweite des Auftrags manuell zu jedem Auftrag anlegen. Insgesamt fast 3000 Stück. Nun ist mir aufgefallen, dass ich die Werksferien bei dieser Formel berücksichtigen sollte, und nicht extra auswerten brauche.

Könntet ihr mir helfen, ein Makro zu schreiben, was diese Formeln z.B. per Button alle ergänzt, aber die range unbedingt beibehält?

In einem Sheet habe ich die Werksferien als einzelne freie Tage untereinander eingegeben.

Also das Ziel könnte so in etwa aussehen:

=SUMMENPRODUKT((WOCHENTAG(B11:B40;2)>5)*N11:N40)+summenprodukt(wenn((B11:B40)=verweis(....))(B11:B40)*N11:N40))

nur halt funktionstüchtig. Das kommt dann wenn ich das Makro habe.

Im Prinzip bräuchte ich von euch nur den VBA-Code zum Suchen und Einlesen der Formel aus der Spalte I:I, die Übernahme des Ranges in die Ergänzung der Formel und das zurück in die Zelle schreiben der neuen Formel.
Den Rest kann ich mir danach alá learning by doing zurecht schreiben. Bin bei der Aufgabe leider auf mich alleine gestellt, da in meiner umgebung keiner VBA kann, und ich auch noch nicht wirklich.

Ich wäre euch sehr dankbar, es ist ziemlich dringend!
to top
#2
Hi,

also, wenn es Dir brennt, dann solltest Du versuchen, das was Du in VBA haben willst genauer zu beschreiben.

Beispielsweise hast Du hier geschrieben, daß in der Spalte I diese Formel zu finden ist: =SUMMENPRODUKT((WOCHENTAG(B11:B40;2)>5)*N11:N40)

Ich finde mal gerade:

Die Spalte I ist erst einmal frei von allem, dann ...
in I43 =(O44)/(O43)
in I44 =SUMMENPRODUKT((WOCHENTAG(B11:B40;2)>5)*N11:N40
in I45 =Ueberhang(B11;N40)

Vielleicht siehst Du es ja anders, aber für mich sieht das schon ein wenig anders aus, als ich es Deiner Ankündigung nach erwarten durfte.
Den Rest habe ich jetzt erst mal gar nicht mehr angesehen.

Meine Bitte ... schreibe genauer, was Du erreichen willst. Dann wird das auch was, mit dem VBA.
Gruß
to top
#3
Hallo,

zum einen wäre die richtige Formel diese (FT ist die Liste Deiner arbeitsfreien Tage):

Arbeitsblatt mit dem Namen 'Testtabelle'
 I
4457,75

NameBezug
FT='arbeitsfreie Tage 2011-2013'!$A$1:$A$59

ZelleFormel
I44{=SUMMENPRODUKT((B11:B40=MTRANS(FT))*(REST(B11:B40;7)>1)*(N11:N40))+SUMMENPRODUKT((REST(B11:B40;7)<2)*(N11:N40))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Dann wäre es ein Einfaches, alle Blätter gleichzeitig zu markieren und die Formel einmal in die Zelle einzugeben, vorausgesetzt Du stellst die Auswertung immer in den gleichen Zellbereich!
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#4
Danke ersteinmal für den Hinweis. Ich versuche es nocheinmal besser zu beschreiben.

In der hochgeladenen Testdatei befindet sich ein Fertigungsauftrag (im Original ist für jede Produktart ein Sheet mit allen dazugehörigen fertigen Aufträgen).
Mit VBA soll jetzt in jedem Sheet die Spalte I nach =SUMMENPRODUKT((WOCHENTAG(B11:B40;2)>5)*N11:N40 gesucht werden. Jedoch ist der Bereich bei jeder Formel anderst, da die Aufträge untereinander stehen. Also könnte die nächste zu änderne Formel =SUMMENPRODUKT((WOCHENTAG(B70:B90;2)>5)*N70:N90 heißen.
Was möglich wäre, ist dach dem Zellinhalt der linken Nachbarzelle zu suchen. Der ist immer "Arbeitsstunden nichtWT:".

Wenn eine Formel rechts neben "Arbeitsstunden nichtWT:" gefunden wurde, sollte sie eingelesen werden und hinter die Formel "+....(zugehöriger Bereich)" geschrieben werden.

Falls es nicht zu viel arbeit macht, wäre es super wenn Ihr mir gleich noch bei "+....(zugehöriger Bereich)" helfen könntet.
Aufgabe:
Wenn in dem ausgewählten Bereich in der Spalte B (hier im Beispiel B11:B40) ein Datum aus dem sheet "arbeitsfreie Tage 2011-2013" gefunden wird, muss der Zahlenwert aus Spalte N der dazugehörigen Zeile summiert werden.

Sinn und Zweck der gesamten Formel ist es, die Gesamtsumme der an Wochenenden und freien Tagen gearbeiteten Zeit für jeden Auftrag zu erfassen.

Ich hoffe so ist es verständlicher und ihr könnt mir jetzt helfen? Huh
to top
#5
@Boskobiati:

Die Formel spuckt schonmal das richtige Ergebnis aus. Super!

Nun müssten "nur noch" alle Formeln automatisch in diese Form geändert werden. Die Formel befindet sich immer in Spalte I, unter jedem Auftrag mit einer unterschiedlichen range.
to top
#6
Hallo,

Zitat:Die Formel befindet sich immer in Spalte I, unter jedem Auftrag mit einer unterschiedlichen range.
Das solltest Du eben ändern, entweder die Auswertung über den Auftrag, oder soweit nach unten, dass keine Gefahr besteht, dass die Auswertung überschrieben wird (im Zweifel würde ich jetzt erst einmal die gesamte Auswertung in Zeile 1000 oder so setzen, und bei Bedarf dann die leeren Zellen löschen).
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#7
Das würde die Programmierung hinfällig machen, da ich trotzdem jeden Auftrag "anfassen" muss. Das würde bei ca 3000 Stück den Rahmen sprengen. Deswegen ja VBA.
Um das Problem noch einmal kurz deutlicher zu machen, habe ich die Testdatei nochmals schnell verändert und einen zweiten fiktiven Auftrag vom Produkt A hinzugefügt.


.xlsm   test.xlsm (Größe: 36,55 KB / Downloads: 3)
to top
#8
Hallo Schu,

habe Edgars Formel mal im VBA-Code eingebaut.
Alle Formeln in Spalte I aller Worksheets werden ersetzt.
Code:
Sub FormelnErsetzen()
  Dim oWs As Worksheet
  Dim rngZelle As Range
  Dim strBezug(1 To 2) As String
  On Error Resume Next
  For Each oWs In ActiveWorkbook.Worksheets
    For Each rngZelle In oWs.Columns(9).SpecialCells(xlCellTypeFormulas)
      strBezug(1) = rngZelle.DirectPrecedents.Areas(1).Address(0, 0)
      strBezug(2) = rngZelle.DirectPrecedents.Areas(2).Address(0, 0)
      rngZelle.Formula = "=SUMPRODUCT((" & strBezug(1) & "=TRANSPOSE(FT))*(MOD(" & strBezug(1) & ",7)>1)*(" & strBezug(2) & "))+SUMPRODUCT((MOD(" & strBezug(1) & ",7)<2)*(" & strBezug(2) & "))"
    Next rngZelle
  Next oWs
End Sub

Gruß Uwe
to top
#9
Danke Uwe!!!!
Das ist zu 95% das was ich wollte. Ich habe es durchlaufen lassen und es hat top funktioniert. Es wurden alle Formeln in Spalte I nach dem gewünschten Schema abgeändert.
Nur müssten noch 2 Kleinigkeiten in der Programmierung abgeändert werden, die ich wieder einmal nicht hinbekomme.

In BoskoBiatis Formel, die du verwendet hast, kommt als Platzhalter "FT" für das Worksheet "='arbeitsfreie Tage 2011-2013'!$A$1:$A$59" vor. Wenn ich das mit reinnehme, kommen immer unterschiedliche Fehlermeldungen. Könnte mir bitte jemand mit der Syntax helfen?

Code:
Sub FormelnErsetzen()

Dim oWs As Worksheet
Dim rngZelle As Range
Dim strBezug(1 To 2) As String
    
    On Error Resume Next
    For Each oWs In ActiveWorkbook.Worksheets
        For Each rngZelle In oWs.Columns(9).SpecialCells(xlCellTypeFormulas)
            strBezug(1) = rngZelle.DirectPrecedents.Areas(1).Address(0, 0)
            strBezug(2) = rngZelle.DirectPrecedents.Areas(2).Address(0, 0)
            rngZelle.Formula = "=SUMPRODUCT((" & strBezug(1) & "=TRANSPOSE(=arbeitsfreie Tage 2011-2013!$A$1:$A$59)*(MOD(" & strBezug(1) & ",7)>1)*(" & strBezug(2) & "))+SUMPRODUCT((MOD(" & strBezug(1) & ",7)<2)*(" & strBezug(2) & "))"
        Next rngZelle
    Next oWs

End Sub


2. gewünschte Abänderung:
Falls möglich, wäre es von Vorteil, wenn nicht jede Formel in Spalte I geändert wird, sonder nur

"=SUMMENPRODUKT((WOCHENTAG(Bxx:Bxx;2)>5)*Nxx:Nxx)"

oder wenn einfacher zu schreiben:
Wenn in Spalte H "Arbeitsstunden nichtWT:" steht, ist in dieser Zeile in Spalte I (also rechts daneben) die zu ändernde Formel.

Wenn die 2 Änderungen noch möglich wären, dann funktioniert alles mehr als TOP! 18
to top
#10
Kann es vllt. sein, dass mtrans nicht das gleiche ist wie transpose?
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Formel zerstört Makro michi89287 3 45 Gestern, 20:02
Letzter Beitrag: WillWissen
  Makro: Wenn Zelle Wert ≠ 0, dann Wert speichern und Formel löschen TsuBasti1 6 230 01.10.2016, 06:27
Letzter Beitrag: IchBinIch
  Makro Formel nach unten ziehen chrjh 4 283 28.09.2016, 08:32
Letzter Beitrag: Rabe
  Makro: Dynamische Formel in Zelle einfügen chrjh 8 285 22.09.2016, 14:09
Letzter Beitrag: chrjh
  Berechnung über Wenn-Formel oder Makro (Office 2013) Elfori 14 768 03.08.2016, 10:27
Letzter Beitrag: Ego
  Tabellenblattname in Formel ändern sandraS 11 606 29.04.2016, 11:11
Letzter Beitrag: sandraS
  vorhandenes Makro anpassen, Abfragebereich ändern Dietmar65 5 540 24.04.2016, 19:25
Letzter Beitrag: Steffl
  Mit Makro über Optionbuttons Zellen einfügen und Namen ändern Steini 6 508 12.04.2016, 20:44
Letzter Beitrag: schauan
  Blattname per Makro ändern? DieSchabe 1 400 14.01.2016, 02:34
Letzter Beitrag: atilla
  Formel / Makro ActiveX Steuerelemente JaJe 2 401 25.10.2015, 14:00
Letzter Beitrag: Rabe

Gehe zu:


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