Makro, dass Formel kopiert und Werte einfügt

Forumthread: Makro, dass Formel kopiert und Werte einfügt

30.06.2016 08:27:31

Jenny

https://www.herber.de/forum/archiv/1500to1504/t1500623.htm#1500623
der Beitrag ist inzwischen im Archiv verschwunden ohne dass mir jemand helfen konnte, gibt es den keine Lösung?
LG
Jenny

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Makro, dass Formel kopiert und Werte einfügt

30.06.2016 10:30:05

Rudi

Hallo,
teste mal.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngR As Long
If Target.Column = 4 Then
If Target.Count = 1 Then
On Error GoTo ERREXIT
Application.EnableEvents = False
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
Cells(lngR, 6).FormulaR1C1 = Cells(1, 6).FormulaR1C1
Cells(lngR, 8).FormulaR1C1 = Cells(1, 8).FormulaR1C1
Cells(lngR, 9).FormulaR1C1 = Cells(1, 9).FormulaR1C1
Cells(lngR, 10).FormulaR1C1 = Cells(1, 10).FormulaR1C1
Cells(lngR, 11).FormulaR1C1 = Cells(1, 11).FormulaR1C1
Cells(lngR, 12).FormulaR1C1 = Cells(1, 12).FormulaR1C1
Rows(lngR).Copy
Cells(lngR, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End If
ERREXIT:
Application.EnableEvents = True
End Sub

Gruß
Rudi

AW: Makro, dass Formel kopiert und Werte einfügt

30.06.2016 10:47:02

Jenny

Hallo Rudi,
erstmal danke. Aber nun habe ich ein Problem, von dem ich mangels VBA Kenntnissen vorher nichts geahnt habe, kann es evtl. sein, dass das Makro so geschrieben ist, dass es automatisch bei einer Änderung ausgeführt wird?
Habe jedenfalls bereits ein Makro in der Tabelle namens Worksheet_Change
Sorry habe echt nicht geahnt dass es da zu Irritationen kommen kann.
Aber in meinen Augen würde es sogar Sinn machen, die beiden Makros zu verbinden, dass sie bei einer Änderung ausgeführt werden, falls sowas überhaupt möglich ist,
bei einer Änderung an Spalte G beide Makros,
bei einer Änderung an Spalte D nur dein Makro.
Außerdem wenn man es schon so macht wie du vorschlägst, reicht es auch aufgrund der Berechnungszeiten die Formel in die Zeile zu kopieren, in der ich etwas geändert habe und die Werte eizufügen.
Und bevor es da mit noch einem weiteren Makro Probleme gibt, auch das hier soll wenn es eine Lösung gibt noch mit in dieses Tabellenblatt:
https://www.herber.de/forum/messages/1501472.html


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Column = 7 And Target.Count = 1 Then
If Target  "" And Target.Offset(, -6)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -6).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 6) = Target Then
Target.Offset(, -2) = cc.Offset(, 4) '& " " & (cc.Offset(, 4).Address)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -2).Value = "n.v."
End If
End If
End Sub

AW: Makro, dass Formel kopiert und Werte einfügt

30.06.2016 13:27:06

Rudi

Hallo,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target  "" Then
On Error GoTo ERREXIT
Application.EnableEvents = False
Select Case Target.Column
Case 4: Call SpalteD(Target)
Case 7: Call SpalteD(Target): Call SpalteG(Target)
End Select
End If
ERREXIT:
Application.EnableEvents = True
End Sub
Sub SpalteG(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Offset(, -6)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -6).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 6) = Target Then
Target.Offset(, -2) = cc.Offset(, 4) '& " " & (cc.Offset(, 4).Address)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -2).Value = "n.v."
End If
End Sub
Sub SpalteD(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
Cells(lngR, 6).FormulaR1C1 = Cells(1, 6).FormulaR1C1
Cells(lngR, 8).FormulaR1C1 = Cells(1, 8).FormulaR1C1
Cells(lngR, 9).FormulaR1C1 = Cells(1, 9).FormulaR1C1
Cells(lngR, 10).FormulaR1C1 = Cells(1, 10).FormulaR1C1
Cells(lngR, 11).FormulaR1C1 = Cells(1, 11).FormulaR1C1
Cells(lngR, 12).FormulaR1C1 = Cells(1, 12).FormulaR1C1
Rows(lngR).Copy
Cells(lngR, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Gruß
Rudi

AW: Makro, dass Formel kopiert und Werte einfügt

30.06.2016 18:42:47

Jenny

Hallo Rudi,
ja du, vielen Dank, das sieht super aus.
Nur noch eine kleine Bitte, ich würd mir gern dein ursprüngliches Makro aufheben für den Fall dass ich doch mal eine komplette Neuberechnung der Tabelle brauche.
Wie bekomme ich es denn hin dass dieses Makro in der Liste unter Ansicht - Makros erscheint, sodass ich es von Hand ausführen kann statt automatisch?
LG
Jenny

AW: Makro, dass Formel kopiert und Werte einfügt

01.07.2016 09:32:11

Jenny

Hallo Rudi,
würdest du bitte nochmal nach dem Makro schaun, die Tests haben zwar soweit funktioniert, aber beim eigentlichen Arbeiten gibt es doch ein paar Probleme.
du siehst sicherlich, ich habe versucht aus Spalte D die Spalte E zu machen, war ein Fehler von mir in der Problembeschreibung.
Und ich habe in der Zeile

Case 7: Call SpalteG(Target): Call SpalteE(Target)

die Reihenfolge vertauscht, da das Makro Spalte G ja etwas in Spalte E schreibt.
Aber nun zu den beiden Problemen die ich habe,
ich kopiere in den Spalten E und G oft mal Texte in mehrere Zellen gleichzeitig, das meiste waren bislang 12 Zellen, das Makro funktioniert nur, wenn ich den Text jeweils einzeln kopiere.
dass ich einen Text in mehrere Zellen gleichzeitig kopiere, passiert auch in Spalte D, in dem Fall bekomme ich einen Laufzeitfehler 13, Typen unverträglich, und beim Debuggen wird die Zeile

If Target.Count = 1 And Target  "" Then

markiert. Obwohl das Makro doch eigentlich mit Spalte D nichts mehr zu tun haben dürfte.
Außerdem wärs super wenn nach dem Ausführen die Zelle ausgewählt wird in die ich zuvor etwas eingegeben habe, anstatt die ganze Zeile.
Gruß und danke für die erneute Hilfe
Jenny


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target  "" Then
On Error GoTo ERREXIT
Application.EnableEvents = False
Select Case Target.Column
Case 5: Call SpalteE(Target)
Case 7: Call SpalteG(Target): Call SpalteE(Target)
End Select
End If
ERREXIT:
Application.EnableEvents = True
End Sub
Sub SpalteG(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Offset(, -6)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -6).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 6) = Target Then
Target.Offset(, -2) = cc.Offset(, 4) '& " " & (cc.Offset(, 4).Address)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -2).Value = "n.v."
End If
End Sub
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
Cells(lngR, 6).FormulaR1C1 = Cells(1, 6).FormulaR1C1
Cells(lngR, 8).FormulaR1C1 = Cells(1, 8).FormulaR1C1
Cells(lngR, 9).FormulaR1C1 = Cells(1, 9).FormulaR1C1
Cells(lngR, 10).FormulaR1C1 = Cells(1, 10).FormulaR1C1
Cells(lngR, 11).FormulaR1C1 = Cells(1, 11).FormulaR1C1
Cells(lngR, 12).FormulaR1C1 = Cells(1, 12).FormulaR1C1
Rows(lngR).Copy
Cells(lngR, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

AW: Makro, dass Formel kopiert und Werte einfügt

03.07.2016 03:28:06

Michael

Hi Jenny,
Du hast ja schon öfter gepostet hast: nicht nur wie oben zitiert, sondern auch hier: https://www.herber.de/forum/archiv/1496to1500/t1498914.htm
Ich bin etwas unwillig, weil sich Deine Anforderungen schneller ändern als unsereins darauf eingehen kann...
Aber gut:
"nicht die ganze Zeile selektiert" sollte damit erledigt sein: in Sub Worksheet_Change zwischen end select und end if die Zeile: target.select
Da "SpalteG" sowieso NUR da ausgeführt wird, läßt sich das Ganze viel schöner formulieren:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target  "" Then
On Error GoTo ERREXIT
Application.EnableEvents = False
Select Case Target.Column
'      Case 5: Call SpalteE(Target)
'      Case 7: Call SpalteG(Target): Call SpalteE(Target)
Case 5: Call SpalteE(Target)
Case 7: Call SpalteG(Target.Row): Call SpalteE(Target)
End Select
Target.Select        ' neu ********************
End If
ERREXIT:
Application.EnableEvents = True
End Sub
Sub SpalteG(ByVal z&) ' Zeile des Target as long neu **********
Dim r As Range, c As Range, cc As Range, zf&
Dim gefunden As Boolean
If Range("A" & z)  "" Then
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Range("A" & z), _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 6) = Range("G" & z) Then
Range("E" & z) = cc.Offset(, 4)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Range("E" & z).Value = "n.v."
End If
End Sub

So stehen im Code die Spaltenbuchstaben, das ist netter als die vielen .Offset.
Hab's allerdings mangels neuer Datei nicht getestet.
Subs, die einen Wert erwarten, werden in der Makroliste nicht angezeigt. Dafür kannst Du eine extra "Aufruf-Sub" formulieren, z.B. so:

Sub callSpalteE()
Call SpalteE(Range("E8"))
End Sub

Schöne Grüße,
Michael

AW: Makro, dass Formel kopiert und Werte einfügt

08.07.2016 09:50:12

Jenny

Hallo Michael,
ich war leider im Krankenhaus nach Sturz vom Fahrrad, tut mir leid, dass ich mich sooooo spät melde.
Das funktioniert alles bestens.
Danke

Beliebteste Forumthreads (12 Monate)

Makro, dass Formel kopiert und Werte einfügt

Forumthread: Makro, dass Formel kopiert und Werte einfügt

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread