Anzeige
Archiv - Navigation
1512to1516
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel kopieren und leere Zellen löschen VBA

Formel kopieren und leere Zellen löschen VBA
09.09.2016 11:10:03
Mike
Hallo,
ich habe folgende Frage. Ich habe eine Datei mit mehreren Arbeitsblätter. Auf einem Arbeitsblatt (Auswahl) ist oben ein Auswahlfeld, mit dem man auswählen kann, auf welches Arbeitsblatt Excel im Hintergrund zugreifen soll(Monate Januar bis Dezember). Jetzt fragt Excel in der Spalte D von Zeile 5 bis 36 bestimmte Werte von dem ausgewählten Monat ab. Es werden von Zeile 5 an immer die nächste Zeile befüllt, wenn die Zahl den eingestellten Wert entspricht. Jetzt sind aber z.B. nur die Zeilen 5 bis 12 mit werten bestück. Bis jetzt muss ich von Hand folgendes machen, die leeren Zeilen in meinem Beispiel von D13 bis D36 markieren und den Inhalt (Formel) löschen, damit die restlichen Berechnungen auf dem Arbeitsblatt funktionieren. Dann kann ich das Arbeitsblatt (Auswahl) ausdrucken und den nächsten Monat über die Auswahl anwählen. Jetzt muss ich aber die Formel von D5 wieder anwählen und bis D36 nach unten ziehen, die benötigten Zeilen werden befüllt und ich lösche wieder die leeren Zeilen.
Jetzt meine Frage, besteht die Möglichkeit, das man über VBA etwas programmiert, das ungefähr folgendes macht. Wenn man auf den Button aktualisieren drückt, startet ein Makro, welches die Formel aus D5 nach unten bis D36 kopiert (zieht, damit Excel auch weiter rechnet mit D5=MonatAuswahl E15; D6=MonatAuswahl E16; usw.), danach schaut in welcher Zeile steht der letzte Wert und dann aus den restlichen Zeilen bis D36 den komplette Inhalt (Formeln) löscht.
Danke,
Mike
PS: Ich kann leider keine Datei davon mit anhängen, da diese auf dem Firmenrechner ist.
PSS: Ich muss die restlichen Zeilen leeren, weil sonst die Berechnungen nicht funktionieren, und da ich den Rest von dieser Auswahlseite nicht selbst programmiert habe, will ich eigentlich auch nicht die Berechnungen ändern, sondern nur meine Arbeit mit einem Makro erleichtern.

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
09.09.2016 11:55:52
UweD
Hallo
ohne Beispielmappe natürlich schwer...
Müsste ohne VBA gehen.
- Habe das Auswahlfeld mal in A1 gelegt
- Daten, Datenüberprüfung, Liste dort dann Januar; Februar; März; usw eintragen
- jetzt kannst du den Monat in A1 auswählen
Diese Formel dann im Blatt Auswahl in D5 schreiben und bis nach D36 kopieren.
=WENN(INDIREKT($A$1&"!E"&ZEILE(A15))"";INDIREKT($A$1&"!E"&ZEILE(A15));"")
fertig !
oder?
AW: Formel kopieren und leere Zellen löschen VBA
09.09.2016 12:11:54
Mike
Hallo UweD,
vielleicht habe ich mich falsch ausgedrückt, die Excel-datei an sich funktioniert super.
Nur immer wenn ich einen neuen Monat auswähle, muss ich die Zelle D5 markieren, die Maus zur untere rechte Ecke der Zelle bewegen und dann mit gedrückter Maustaste bis D36 ziehen, damit die Formel weiter kopiert wird. Jetzt schauen ab welcher Zeile kein Wert mehr enthalten ist, und ab da bis D36 den Inhalt der Zellen löschen.
Deshalb habe ich mir gedacht, es wäre leichter einen Button einzubauen, der ein Makro öffnet, der genau dieses tut, was ich bis jetzt noch von Hand mache.
Trotzdem danke schon mal für Deine Antwort.
Mike
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
09.09.2016 12:34:31
Bernd
Servus,
was gibt denn diese Formel als Leerwert zurück?
Kopiere bitte die Formel aus D5 hier rein.
Grüße, Bernd
AW: Formel kopieren und leere Zellen löschen VBA
09.09.2016 12:55:46
Bernd
Servus,
teste mal folgendes Makro:

Dim i As Integer
Sub kopieren()
With ActiveSheet
For i = 5 To 36
.Cells(i, 4).FormulaLocal = "DeineFormel" 'also z.B. "=SUMME(A:A)" inkl. der Anfü _
hrungszeichen
If .Cells(i, 4).Value = 0 Then ' Anstelle der 0 verwende den Rückgabewert der  _
Formel für "Leerwert"
.Cells(i, 4).ClearContents
Exit Sub
End If
Next i
End With
End Sub
Das musst du dann nur noch einem Button zuweisen...
Grüße
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
11.09.2016 19:40:41
Mike
Hallo,
habe jetzt mal ein bisschen rum gespielt und folgendes programmiert. Es funktioniert auch in meiner Testversion Zuhause. Könnt ihr mal bitte drüber schauen, ob man noch etwas besser machen kann, bevor ich es morgen in die Orginaldatei übertrage.
Sub Aktualisieren()
Dim adr As Range
Dim Anz As Integer
Range("G1").Select
Selection.AutoFill Destination:=Range("G1:G31"), Type:=xlFillDefault
Set adr = Worksheets("Vorlage").Range("G1:G31")
Anz = Application.WorksheetFunction.CountIf(adr, "")
Range(Cells(32 - Anz, 7), Cells(31, 7)).ClearContents
End Sub
Danke,
schönen Sonntag noch,
Mike
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 08:01:57
Mike
Hallo nochmal,
habe am Wochenende Zuhause ja folgendes programmiert.
Sub Aktualisieren()
Dim adr As Range
Dim Anz As Integer
Range("G1").Select
Selection.AutoFill Destination:=Range("G1:G31"), Type:=xlFillDefault
Set adr = Worksheets("Vorlage").Range("G1:G31")
Anz = Application.WorksheetFunction.CountIf(adr, "")
Range(Cells(32 - Anz, 7), Cells(31, 7)).ClearContents
End Sub
und hat in der Testdatei auch funktioniert. Jetzt habe ich es heute auf der Arbeit in der Livedatei versucht und es kommt in der Zeile Anz = App... folgender Fehler: "Laufzeitfehler 438 Objekt unterstützt diese Eigenschaft oder Methode nicht.
Kann mir evtl. jemand sagen, woran das liegt?
Danke schon mal im voraus,
Gruß,
Mike
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 08:11:22
Mike
Hallo,
sorry, hatte aus versehen das alte Makro eingestellt. Hier ist das neue.
Sub Aktualisieren()
Dim adr As Range
Dim anz As Range
Range("AE31:AF31").Select
Selection.AutoFill Destination:=Range("AE31:AF68"), Type:=xlFillDefault
Set adr = Worksheets("DzwZ").Range("AE31:AE68")
anz = Application.WorksheetFunction.countlf(adr, "")
Range(Cells(69 - anz, 26), Cells(68, 27)).ClearContents
End Sub
Gruß,
Mike
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 08:58:55
Bernd
Servus,
die Variable anz ist falsch deklariert. Du willst doch zählen, wenn der Wert "" im Bereich adr steht.
Ergo ist dein Rückgabewert ein Integer (oder Long) und kein Range.
Grüße, Bernd
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 11:09:48
Mike
Hallo Bernd,
da hast Du natürlich recht, das war mein Fehler beim abschreiben. Trotzdem bleibt der Laufzeitfehler 438. Weißt Du evtl. woran das liegen könnte?
Gruß,
Mike
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 11:57:03
Bernd
Servus,
mal anders gefragt; hattest du meinen Vorschlag schon einmal probiert?
Einfach in ein Standart-Modul und dann einem Button in deiner Übersichtstabelle zuweisen:

Dim i As Integer
Sub kopieren()
With ActiveSheet
For i = 5 To 36
.Cells(i, 4).FormulaLocal = "DeineFormel" 'also z.B. "=SUMME(A:A)" inkl. der _
Anführungszeichen
If .Cells(i, 4).Value = 0 Then ' Anstelle der 0 verwende den Rückgabewert der _
Formel für "Leerwert", also ggf. auch doppelte Anführungszeichen ""
.Cells(i, 4).ClearContents
Exit Sub
End If
Next i
End With
End Sub

Das sollte nach meinen überschaubaren VBA-Kenntnissen schon ausreichend sein...
Grüße
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 12:05:25
Bernd
Servus nochmal,
wie ich sehe hat sich dein Bearbeitungsbereich in der Tabelle verändert
 Selection.AutoFill Destination:=Range("AE31:AF68"), Type:=xlFillDefault 

Dann musst du entsprechend auch die Zeilenangaben meiner FOR-Schleife von 5 To 36 auf 31 To 68 anpassen und die Clearcontents erweitern.
Anstelle von
 .Cells(i, 4).ClearContents 

muss dann
 .Range(.Cells(i, "AE"),.Cells(i,"AF")).ClearContents 

verwendet werden.
Grüße, Bernd
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
13.09.2016 06:55:22
Mike
Hallo Bernd,
vielen Dank für Deine Antworten, ich habe Deinen Vorschlag nicht versucht, weil ich nicht wusste, was ich bei "0" angeben sollte, denn die Formel gibt nur eine leere Zelle wieder, in der die Formel steht. Ich habe jetzt mal etwas anders versucht und das klappt bis jetzt ohne Probleme. Trotzdem Danke noch mal für Deine Hilfe. Hier ist mein jetziger Versuch, falls jemand ein ähnliches Problem hat.
Sub Aktualisieren2()
Dim Zelle As Range
Range("AE31:AF31").Select
Selection.AutoFill Destination:=Range("AE31:AF68"), Type:=xlFillDefault
Range("AE31:AF68").Select
If TypeOf Selection Is Range Then
For Each Zelle In Selection
If Zelle.HasFormula Then
If Zelle.Value = "" Then
Zelle.ClearContents
End If
End If
Next
End If
Range("x31").Select
End Sub
Gruß,
Mike
Anzeige
AW: Formel kopieren und leere Zellen löschen VBA
13.09.2016 08:05:03
Bernd
Servus,
auch sehr schön, viele Wege führen nach Rom :-)
Danke für`s Feedback.
Grüße, Bernd
AW: Formel kopieren und leere Zellen löschen VBA
12.09.2016 11:03:21
Mike
Hallo,
sorry, hatte aus versehen das alte Makro eingestellt. Hier ist das neue.
Sub Aktualisieren()
Dim adr As Range
Dim anz As Range
Range("AE31:AF31").Select
Selection.AutoFill Destination:=Range("AE31:AF68"), Type:=xlFillDefault
Set adr = Worksheets("DzwZ").Range("AE31:AE68")
anz = Application.WorksheetFunction.countlf(adr, "")
Range(Cells(69 - anz, 26), Cells(68, 27)).ClearContents
End Sub
Gruß,
Mike

375 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige