Formel mit Makro ändern

[Schu]

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.


  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!

[Gardinenhaken]

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.

[BoskoBiati]

Hallo,

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

Arbeitsblatt mit dem Namen 'Testtabelle'
	I
44	57,75

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

Zelle	Formel
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!

[Schu]

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?

[Schu]

@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.

[BoskoBiati]

Hallo,

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).

[Schu]

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.


  test.xlsm (Größe: 36,55 KB / Downloads: 4)

[Kuwer]

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

[Schu]

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:

[Schu]

Kann es vllt. sein, dass mtrans nicht das gleiche ist wie transpose?