VBA Werte aus Formelergebnis übertragen

[Timmihase]

Hallo liebes Forum,

kann mir jemand bei folgendem Problem Helfen

Ich habe eine Tabelle in der von C6 bis C45 Namen stehen, welche sich Excel mit einer Wenn Formel holt.
Da ich aber nicht die Formel möchte sondern den Wert wird das Ergebnis per VBA in D6 bis D45 gesendet. Dazu wird dieser Code verwendet

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C5:C45")) Is Nothing Then
 Range("D5:D45").Value = Range("C5:C45").Value
    End If
End Sub

Ändert sich nun über die Wenn Formel aber der Wert in einer der C Zellen aktualisiert er die Zelle in D nicht. Erst wenn man quasi 1 mal in die Zelle klickt und das ,,manuell,, bestätigt.

Gibt es hier eine Möglichkeit den Code so zu ändern das dies Automatisch geschieht sobald die Formel ein anderen Wert ausspuckt ?

Danke sehr im vorraus

[Fennek]

Hallo,

Excel bietet bei Änderung einer Formel kein Event an. Es ist allerdings möglich mit recht viel Aufwand etwas zu konstruiren.

--------------------------------
Für eine Zelle:

Plaziere irgendwo auf dem Blatt eine Active-X-Textbox.

Verlinke sie mit der gewünschten Zelle (Eigenschaft LinkedCell) und verstecke sie (Eigenschaft Visible=False).

Schreibe deinen Code in das Change_Event der Textbox

------------------------------------

Es wäre besser, wenn du eine einfachere Lösung finden würdest.

mfg

[atilla]

Hallo,

Excel bietet bei Änderung einer Formel kein Event an. Es ist allerdings möglich mit recht viel Aufwand etwas zu konstruiren.

Hallo Fenek,

es gibt doch das Calculate Ereignis?!

[Fennek]

@Atilla

ja.

Aber der Code des TO in #1 löst nicht aus, wenn die Formeln ein neues Ergebnis zeigen.

Einfach ist es an der Stelle der Änderung (auch in einem anderen sheet) den Code zu starten, aber wenn man es kompliziert mag, geht auch meine Variante.

mfg

[Timmihase]

Danke erstmal für die Antworten.

Vielleicht hat jemand zu dem Grund warum ich das machen will eine Idee.

Ich hab ein Blatt in welchen ein Dropdown drin ist. Dieses Zieht aus einem anderen Tabellenblatt einen Namen.
Aus einem zweiten Blatt werden nun zu dem im Dropdown gewähltem Namen die dazu gehörenden Daten mittels SVerweis gezogen. Jedes Blatt soll ausgedruckt werden. Da nehme ich folgendes Makro

Code:

Sub WQChromDrucken()
    Dim Rng As Range, Zelle, TB
    Set Rng = Sheets("Prüflinge").Range("B4:B43") 'anpassen
    Set TB = Sheets("Auswertung WQ Chromatographie")
   
    For Each Zelle In Rng.SpecialCells(xlCellTypeConstants, 3)
        TB.Range("B2") = Zelle.Value
        TB.PrintOut
    Next
       
End Sub

Das funktioniert wunderbar. In der Tabelle Prüflinge stehen die Entsprechenden Namen von B4 bis B43

Solange die Namen in den Zellen stehen alles kein Problem. Werden die Namen über eine Formel reingeschrieben funktioniert das Makro aber nicht mehr. Der über Formel eingefügte Name wird quasi ignoriert und als Fehler kommt : Keine Zellen gefunden

Der Fehler kommt bestimmt daher das der Name welcher über die Formel kommt nicht erkannt wird und das Makro meint die Zellen sind leer.

Vielleicht hat da jemand eine Idee ?

Danke

[atilla]

Hallo,

das liegt an den SpecialCells. Such mal im www nach SpecialCells Methode. Bei Dir werden zellen mit Werten und nicht Formelwerte durchlauefen.

Aber es müsste auch so gehen:

Code:

Sub WQChromDrucken()
   Dim i As Long
    Set TB = Sheets("Auswertung WQ Chromatographie")
    For i = 4 To 43
        TB.Range("B2") = Sheets("Prüflinge").Cells(i, 2).Value
        TB.PrintOut
    Next
       
End Sub

[Timmihase]

Das sieht schon gut aus. Er erkennt jetzt auch Formeln. Leider führt das zu einem anderen Problem

Wenn in Bereich von B4:B43 mal kein Wert steht dann habe ich das Problem das im Dropdown nun auch die Leere Zelle gewählt wird und auch druckt.

Gibt es eine Möglichkeit das Excel mit diesem Makro nur die druckt wo auch Namen drin stehen. 

Beispiel :

B4, B18, B40 stehen Namen drin. Dann sollen auch nur diese gedruckt werden.
Beim nächsten mal kann dann sein das in B5, B23, B34

Dann sollen jeweils nur diese Zellen ausgewählt und gedruckt werden

[atilla]

Hallo,

dann vielleicht so:

Code:

Sub WQChromDrucken()
  Dim i As Long
    Set TB = Sheets("Auswertung WQ Chromatographie")
    For i = 4 To 43
      If Sheets("Prüflinge").Cells(i, 2).Value <> "" Then
        TB.Range("B2") = Sheets("Prüflinge").Cells(i, 2).Value
        TB.PrintOut
      End If
    Next
End Sub

[Timmihase]

Hallo nochmal 

Leider klappt das auch nicht. Habe aber eine Vermutung. Die Zellen ohne Namen sind ja nicht leer,  es steht ja die Formel drin mit Ergebnis 0 ( ist so eingestellt das Excel die 0 nicht anzeigt )

Geht es das nun das Makro alle Werte druckt welche nicht Null sind ?

Sorry das ich das nicht gleich gemerkt habe.

[marose67]

Hallo,

versuche mal im Code einfach das "" gegen 0 auszutauschen:

Code:

Sub WQChromDrucken()
  Dim i As Long
    Set TB = Sheets("Auswertung WQ Chromatographie")
    For i = 4 To 43
      If Sheets("Prüflinge").Cells(i, 2).Value <> 0 Then
        TB.Range("B2") = Sheets("Prüflinge").Cells(i, 2).Value
        TB.PrintOut
      End If
    Next
End Sub

Gruß
Marcus