Berechnetes Feld durch Wert ersetzen und neues berechnetes Feld aufbauen

[Hardbopper]

Hallo André,

Du übersiehst, dass Du es mit einem VBA-Anfänger zu tun hast. Entsprechend meiner 2. Frage ist das "Verknüpfen"-Makro aus einer Aufzeichnung entstanden, d.h. die zu verknüpfende Zelle ist fix (Range("D46").Select).

Ich wäre Dir dankbar, wenn Du daraus eine Variable machen könntest, die an das Ergebnis des Makro1 anknüpft. Ich bekomme es jedenfalls nicht hin.

Gruß

Hardbopper

[Hardbopper]

Aktueller Code:

---

Sub Verknuepfung()  

'  
' Verknuepfung Makro  
'  
  
'  
    Range("D46").Select  
    Application.CutCopyMode = False  
    ActiveCell.FormulaR1C1 = "=R[-21]C[4]"  
End Sub

[schauan]

Hallöchen,

also, hier mal auf Basis des vorhin geposteten codes, der Formeleintrag.

Sub Makro1()
Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).FormulaR1C1 = "=R25C8"
End Sub

Wichtig ist hier z.B. auch bei der Aufzeichnung, dass Du absolute Adressierung verwendest, d.h. bei H25 die $ hinzufügst. Ohne wäre ja relativ und wenn Du die Formel eine Zeile runter ziehen würdest oder der Code den Wert dort findet, dann stände dort H26 … (würde mit "=R[-21]C[4]" passieren)

Rest kommt noch, bin immer mal abwesend ...

[schauan]

Hallöchen,

meinst Du so? WorkRng erhält zuerst die gefundene Zelle und wird dann für die Inputbox verwendet.

Code:

Sub DisplayedToActual()
'Updateby20131126
Dim Rng As Range
Dim WorkRng As Range
'On Error Resume Next
  Set WorkRng = Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
  WorkRng.FormulaR1C1 = "=R25C8"
  xTitleId = "KutoolsforExcel"
  'Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
  Application.ScreenUpdating = False
  For Each Rng In WorkRng
    Rng.Value = Rng.Text
  Next
  Application.ScreenUpdating = True
End Sub

[Hardbopper]

Hallo André,

lass mal "Andrés Macro" laufen, es funktioniert noch nicht (Tabelle ist beigefügt) . Dein Macro1 war ja auch absolut in Ordnung. Nur mein Macro "Verknuepfung" hat den Mangel dass durch Range("D46").Select der Verknüpfungspunkt auf D46 festgelegt ist, das bedeutet, dass für den Fall, dass sich durch den Zeitstempel die Positionen verschieben, das Makro immer noch eine Verknüpfung zwischen H25 und D46 herstellt, obwohl z.B. inzwischen eine nachfolgende Zelle richtig wäre.

Beispiel:

G13 hat jetzt das Datum 15.01.2020 und G25 das Datum 31.01.2020. Das bedeutet, dass Dein Macro1 (was es ja auch tut), die Verknüpfung zwischen H25 und D46 durch Einfügen des Werts auflöst.

Das Macro Verknuepfung müsste nun eine Verknüpfung zwischen H25 und D47 herstellen, was es aber nicht tut, weil es auf D46 festgelegt ist (siehe oben).

Meine Idee war deshalb, die beiden Macros so ineinander zu fügen, dass sich, um bei meinem Beispiel zu bleiben, aus der Wertzuweisung für Zelle D46 automatisch eine Verknüpfung für D47 ergibt.

Übrigens: Wenn beim Testen von Andrés Macro der Zellinhalt von D45 zerschossen wird: Der richtige Wert lautet: 4830,174.

Einen schönen Sonntag wünscht Dir

Hardbopper


  Ersetzen eines berechneten Feldes durch einen Wert.xlsm (Größe: 136,09 KB / Downloads: 7)

[schauan]

Hallöchen,

so?

Code:

Sub DisplayedToActual()
'Updateby20131126
Dim Rng As Range
Dim WorkRng As Range
Dim WorkRng1 As Range
'On Error Resume Next
  Set WorkRng = Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
  WorkRng.FormulaR1C1 = "=R25C8"
  xTitleId = "KutoolsforExcel"
  'Set WorkRng = Application.Selection
  Set WorkRng1 = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
  Application.ScreenUpdating = False
  For Each Rng In WorkRng1
    Rng.Value = Rng.Text
  Next
  WorkRng.Offset(1, 0).FormulaR1C1 = "=R25C8"
  Application.ScreenUpdating = True
End Sub

Neu ist darin diese Zeile:
WorkRng.Offset(1, 0).FormulaR1C1 = "=R25C8"
Die fügt eine Zeile darunter nochmal die gleiche Formel ein.

Allerdings kann das irgendwie nicht richtig sein, dadurch entsteht ein Zirkelbezug und anschließend unsinnige Werte, wenn ich in der Inputbox $D$45 lasse. …

[Hardbopper]

Hallo André,

könnte es sein, das eben nicht exakt die gleiche Formel ein zweites Mal eingesetzt werden darf, sondern diese so modifiziert werden muss, dass die Zelle, die durch die neu eingefügte Zeile angesprochen wird, nicht D45, sondern D46 ist ? Ich kann da leider nur laienhafte Empfehlungen aussprechen, weil VBA nicht so mein Ding ist. Außerdem darf das Makro wohl nicht starr nur die Zellen D45 und D46 ansprechen, sondern muss sich an dem orientieren, was ich im Beitrag #25 zu erklären versucht habe. Vielleicht liest Du das noch einmal durch.

Hardbopper

[schauan]

Hallöchen,
Der Code fügt keine Zeile ein sondern eine Formel.
Die Zelle D45 ist nirgends programmiert sondern sie ergibt sich aus dem Treffer vom gesuchten Wert. Wenn beim Test immer nach dem gleichen Wert gesucht und zuvor der Ausgangszustand wiederhergestellt wurde dann kommt natürlich immer D45.
Gesucht wird das, was in H13 steht.

[Hardbopper]

Hallo André,

es tut mir leid, Dich so in Anspruch zu nehmen, aber ich denke, mit diesem Code kommen wir nicht weiter. Lass uns doch wenigstens noch einen Versuch machen mit Deinem Makro1, der doch einwandfrei funktioniert:

Sub Makro1()
MsgBox Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Address
End Sub

Er erkennt bei der aktuellen Konstellation die Zelle D45. 

Mein Makro Verknuepfung 

Sub Verknuepfung()
'
' Verknuepfung Makro
'   Range("D46").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-21]C[4]"
End Sub


arbeitet aber - weil er mit dem Macrorecorder aufgezeichnet worden ist, mit einer festgelegten Zelle D46. Das passt dann nicht mehr, wenn Dein Makro schon D46 als die Zelle ermittelt hat, in der eine Formel durch einen Wert ersetzt werden muss. Dann müsste mein Makro D47 mit H25 verknüpfen.

Anders ausgedrückt, müsste mein Makro so umgestaltet werden, dass es abhängig vom Ergebnis Deines Makros eine eigene Zelladresse berechnet,  mit Bezug auf Makro1 also immer die darauffolgende Adresse.

Wenn das gelänge, wäre das Makro in jedem nachfolgenden Zeitrahmen einsetzbar.

Hardbopper

[schauan]

Hallöchen,

mit dem Code wird im Ausgangszustand anhand H13 die Zelle D45 gefunden und eine eventuelle Formel darin ersetzt.
Dann wird in D46 die Formel =$H$25 eingetragen.

Code:

Sub DisplayedToActual()
'Updateby20131126
'Variablendeklarationen
Dim Rng As Range
'On Error Resume Next
'Zelle mit Wert aus H13 finden und Zelle darunter merken
  Set WorkRng = Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
In gefundener Zelle Formel durch Wert ersetzen
WorkRng.Value = WorkRng.Value
'Formel mit Bezug auf H25 unter der gefundenen Zelle eintragen
WorkRng.Offset(1, 0).FormulaR1C1 = "=R25C8"
'Inputbox Titel festlegen
  xTitleId = "KutoolsforExcel"
'neue Zelladresse / Bereich eingeben, Eingabevorschlag gefundene Adresse
  Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
'Bildschirmflackerrn aus
  Application.ScreenUpdating = False
'für jede Zelle im eingegebenen Bereich
  For Each Rng In WorkRng
'Wert durch Text ersetzen
    Rng.Value = Rng.Text
'Ende für jede Zelle im eingegebenen Bereich
  Next
'Bildschirmflackerrn ein
  Application.ScreenUpdating = True
End Sub