Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Formel aus Zellen erstellen und einfügen als Forme


Betrifft: Formel aus Zellen erstellen und einfügen als Forme von: Peter
Geschrieben am: 13.09.2019 09:43:42

Guten Morgen,

ich möchte mit dem Wert aus einer Zelle eine Formel erstellen und in andere Zelle einfügen.

Mein Ansatz hierzu:

Worksheets(1).range("U2") = Wert1

Ergebnis Wert1 = "Buchen_123"

Formel - soll lauten: =Buchen_123!$U$2

dann Worksheets(2).range("I2").FormulaLocal=Formel

Könnt ihr mir hierbei bitte helfen.

Gruss
Peter

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1712885.html
Geschrieben am: 13.09.2019 12:52:34

Hi Peter

Falls ich richtig interpretiere, möchtest du den Tabellennamen variabel gestalten und aus Tabelle1!U2 beziehen.

Hierfür könntest du die Formel INDIREKT verwenden (ganz ohne VBA):
=INDIREKT(Tabelle1!U2&"!$U$2")

oder so...

Worksheets(2).range("I2").Formula = "=" & Worksheets(1).range("U2") & "!$U$2"

cu
Chris

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1712890.html
Geschrieben am: 13.09.2019 13:09:44

Hallo Peter,

hier meine Lösung inkl. Prüfung, ob der in der Formel zu verndende Blattname existiert.

LG
Franz

Sub aaTest()
                Dim rngSource As Range
                Dim rngTarget As Range
                Dim rngTest As Range
                Dim sMsg As String
                
                On Error GoTo Fehler
                
                Set rngSource = Worksheets(1).Range("U2")
                
                Set rngTarget = Worksheets(2).Range("I2")
                'prüfen, ob berechnete Zieladresse existiert
                Set rngTest = Worksheets(rngSource.Text).Range(rngSource.Address)
                rngTarget.FormulaLocal = "='" & rngSource.Text & "'!" & rngSource.Address
                
            Resume01:
            
            Fehler:
                With Err
                    sMsg = "Fehler-Nr: " & .Number & vbLf & .Description
                    Select Case .Number
                    Case 0 'alles OK
                    Case 9 'Index-Error
                        MsgBox sMsg & vbLf & vbLf & "Die berechnete Zelle """ & "'"  _
                            & rngSource.Text & "'!"& rngSource.Address & """ existiert nicht!", _
                            vbInformation + vbOKOnly, "Fehler"
                        Resume Resume01
                    Case Else
                        MsgBox sMsg, vbInformation + vbOKOnly, "Fehler"
                    End Select
                End With
            End Sub
            

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1712898.html
Geschrieben am: 13.09.2019 13:19:35

Hallo Franz,

besten Dank für Deine Hilfe. Der Test ist sehr gut verlaufen.

Habe aber für mein Verständnis noch eine Frage zu Deinem Code:

Mit welcher Anweisung ergibt sich das Einfügen von "$" ?

Gruss
Peter

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1712901.html
Geschrieben am: 13.09.2019 13:25:51

HI
wenn du dir mal die Parameter von .Address in der Hilfe anschaust, wirst du feststellen, dass du präzise einstellen kannst, wie die Zelladresse ausgegeben werden soll, dh. als R1C1- oder A1-Adresse, als absoluter oder relativer Zellbezug.

die Einstellung "A1-Adresse mit absoluten Zellbezügen ($)" ist die Standardeigenschaft, die verwendet wird, wenn du keine Angaben machst.
brauchst du den relativen Zellbezug (ohne $), nimmst du .Address(0, 0).

Gruß Daniel

  

Betrifft: AW: Verstanden von: 1712902.html
Geschrieben am: 13.09.2019 13:27:51

Hallo Daniel,

besten Dank für Deine Ausführungen. Das ist super erklärt.

Gruss
Peter

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1712984.html
Geschrieben am: 13.09.2019 17:49:52

Hallo Franz,

mit Deinem Code wird die Formel in die Zelle I2 eingetragen.

Wie kann man diesen Code umschreiben, dass die Zellen I2:I6 mit der Formel ausgefüllt werden?

Besten Dank

Gruss
Peter

  

Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713013.html
Geschrieben am: 13.09.2019 23:45:47

Hallo Peter,

das sollte jetzt aber nicht so schwer sein.

  • Set rngTarget = Worksheets(2).Range("I2:I6")


  • Gruß Werner
      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713016.html
    Geschrieben am: 14.09.2019 06:57:15

    Hallo Werner,

    besten Dank, manchesmal übersieht man die kleinsten Dinge.

    Wünsche Dir ein schönes Wochenende.

    Gruss
    Peter

      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713017.html
    Geschrieben am: 14.09.2019 07:26:00

    Hallo Werner,

    leider noch ein Denkfehler.

    Ich möchte die Werte in eine variable Zelle einfügen nicht nur in J2:M2.

    Mit dem nachstehenden Code markiere ich von mir gesuchte Zelle:

    'funktioniert markieren der ersten leere Zelle in Zeile1
    <pre>Sub letzte()
    Dim a 'benötigt für das Auffinden der benutzten Spalten
    a = Sheets("Worddaten").UsedRange.Columns.Count 'zählt die benutzten Spalten
    Cells(1, a).Select 'selektiert die erste Zelle der ersten freien Spalte
    End Sub</pre>


    Und ab dieser Zelle sollen die Werte für Set rngTarget = Worksheets(2).Range("I2:M2") eingefügt werden.

    Leider sind mehrere Versuche meinerseits fehlgeschlagen.

    Wäre super wenn Du mir helfen könntest.

    Gruss
    Peter

      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713045.html
    Geschrieben am: 14.09.2019 12:33:36

    Hallo Peter,

    meinst du so?

    Sub aaTest()
        Dim rngSource As Range, rngTarget As Range
        Dim rngTest As Range, a As Long, sMsg As String
        
        On Error GoTo Fehler
        
        Set rngSource = Worksheets(1).Range("U2")
        a = Worksheets("Worddaten").UsedRange.SpecialCells(xlCellTypeLastCell).Column
        
        With Worksheets(2)
            Set rngTarget = .Range(.Cells(2, a), .Cells(6, a))
        End With
        'prüfen, ob berechnete Zieladresse existiert
        Set rngTest = Worksheets(rngSource.Text).Range(rngSource.Address)
        rngTarget.FormulaLocal = "='" & rngSource.Text & "'!" & rngSource.Address
        
        Resume01:
        
        Fehler:
        With Err
            sMsg = "Fehler-Nr: " & .Number & vbLf & .Description
            Select Case .Number
            Case 0 'alles OK
            Case 9 'Index-Error
                MsgBox sMsg & vbLf & vbLf & "Die berechnete Zelle """ & "'" _
                    & rngSource.Text & "'!" & rngSource.Address & """ existiert nicht!", _
                    vbInformation + vbOKOnly, "Fehler"
                Resume Resume01
            Case Else
                MsgBox sMsg, vbInformation + vbOKOnly, "Fehler"
            End Select
        End With
        
        Set rngTarget = Nothing: Set rngTest = Nothing
        End Sub
    Gruß Werner
      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713046.html
    Geschrieben am: 14.09.2019 12:42:29

    Hallo Werner,

    Dein Code funktioniert leider nicht so wie von mir gewünscht.

    Zwischenzeitlich habe ich einen Code der funktioniert konstruiert:


    'funktioniert, !!! wenn jedoch die Werte wieder aus den Zahlen gelöscht werden, dann müssen die Spalten insgesamt gelöscht werden !!!
    <pre>Sub Daten_für_neues_Konto_übertragen3a()
    Dim wksH As Worksheet 'benötigt für Set Hilfstabelle
    Dim wksWD As Worksheet 'benötigt für Set Worddaten
    Dim a 'benötigt für das Auffinden der benutzten Spalten


    Set wksH = Worksheets("Hilfstabelle")
    Set wksWD = Worksheets("Worddaten")

    'Anfang Überschriften in Worddaten für neue Konten einfügen
    a = Sheets("Worddaten").UsedRange.Columns.Count 'zählt die benutzten Spalten

    wksH.Range("Überschrift_neues_Konto_Worddaten").Copy
    wksWD.Cells(1, a + 2).PasteSpecial 'mit cells(1, a +2) wird in die zweite Zelle nach der letzte benutzten Zelle die kopierten Werte eingetragen
    Application.CutCopyMode = False 'Aufhebung von Copy-Befehl
    Selection.EntireColumn.AutoFit 'Spaltenbreite anpassen der Spalten, in welche die Daten eingefügt wurden
    'Ende Überschriften in Worddaten für neue Konten einfügen


    'Anfang Kontodaten in Zeile2 einfügen als Formel
    Dim AKto As Object
    a = Sheets("Worddaten").UsedRange.Columns.Count 'zählt die benutzten Spalten

    wksWD.Cells(1, a).Offset(1, -4).Select 'wählt die erste Zelle in der nächsten Zeile aus beginnend von der letzten beschriebenen Zelle in Zeile1


    'Anfang Formel einfügen:
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim rngTest As Range
    Dim sMsg As String

    On Error GoTo Fehler

    Set rngSource = Worksheets("Buchen_1087789").Range("U2") 'wählte den Namen der Kontos aus - später durch Daten aus UF ersetzen !!!!!!


    ' Set rngTarget = Worksheets("Worddaten").Range(AKto) 'falsch ggf. später löschen

    Set rngTarget = Selection 'prüft die Zelle in welche die Formel eingefügt werden soll - erste Zelle in zweiter Zeile

    'prüfen, ob berechnete Zieladresse existiert
    Set rngTest = Worksheets(rngSource.Text).Range(rngSource.Address)
    rngTarget.FormulaLocal = "='" & rngSource.Text & "'!" & rngSource.Address

    Resume01:

    Fehler:
    With Err
    sMsg = "Fehler-Nr: " & .Number & vbLf & .Description
    Select Case .Number
    Case 0 'alles OK
    Case 9 'Index-Error
    MsgBox sMsg & vbLf & vbLf & "Die berechnete Zelle """ & "'" _
    & rngSource.Text & "'!" & rngSource.Address & """ existiert nicht!", _
    vbInformation + vbOKOnly, "Fehler"
    Resume Resume01
    Case Else
    MsgBox sMsg, vbInformation + vbOKOnly, "Fehler"
    End Select
    End With

    'Anfang fügt in die restlichen 4 Zellen der Zeile 2 die Formeln ein
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 4)), Type:=xlFillDefault 'funktioniert aber mit Selection
    'Ende fügt in die restlichen 4 Zellen der Zeile 2 die Formeln ein

    'Anfang Aufhebung der Set-Anweisungen
    Set rngTest = Nothing
    Set rngTarget = Nothing
    Set AKto = Nothing
    'Ende Aufhebung der Set-Anweisungen

    'Ende Kontodaten in Zeile2 einfügen als Formel

    End Sub</pre>


    Falls Du hier noch Verbesserungen findest, wäre ich Dir sehr dankbar.

    Besten Dank

    Gruss
    Peter

      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713057.html
    Geschrieben am: 14.09.2019 14:26:42

    Hallo Peter,

    ich hab dein Makro jetzt mal etwas angepasst/optimiert.

    1. Die 1. Spalte zum Einfügen der Daten für das nächste Konto wird einmal ermittelt und dann in allen weiteren Anweisungen verwendet.

    2. Die Objektvariable wksWD ersetzt die vorhandenen Worksheets("Worddaten")

    3. Die Select/Selection sind eleminiert - Die Zellen/Zellbereiche werden direkt zugewiesen.

    LG
    Franz

    Sub Daten_für_neues_Konto_übertragen3a()
          Dim wksH As Worksheet 'benötigt für Set Hilfstabelle
          Dim wksWD As Worksheet 'benötigt für Set Worddaten
          Dim a 'benötigt für das Auffinden der benutzten Spalten
          
          
          Set wksH = Worksheets("Hilfstabelle")
          Set wksWD = Worksheets("Worddaten")
          
          With wksWD
              'Spalte zum Einfügen der Daten für nächtes Konto ermitteln
              a = .UsedRange.Column + .UsedRange.Columns.Count - 1 '
              'Eine Spalte leerlassen
              a = a + 2
              'Anfang Überschriften in Worddaten für neue Konten einfügen
              wksH.Range("Überschrift_neues_Konto_Worddaten").Copy Destination:=.Cells(1, a)
          
              'Spaltenbreite anpassen der Spalten, in welche die Daten eingefügt wurden
              .Range(.Columns(a), .Columns(a + 4)).AutoFit
          'Ende Überschriften in Worddaten für neue Konten einfügen
          
          End With
          
          'Anfang Kontodaten in Zeile2 einfügen als Formel
          Dim AKto As Object
          
          'Anfang Formel einfügen:
          Dim rngSource As Range
          Dim rngTarget As Range
          Dim rngTest As Range
          Dim sMsg As String
          
          On Error GoTo Fehler
          
          Set rngSource = Worksheets("Buchen_1087789").Range("U2") 'wählte den Namen der Kontos aus -  _
      später durch Daten aus UF ersetzen !!!!!!
          
          'Einfügezelle für Formel setzen - erste Zelle in zweiter Zeile
          Set rngTarget = wksWD.Cells(2, a)
          
          'prüfen, ob berechnete Zieladresse existiert
          Set rngTest = Worksheets(rngSource.Text).Range(rngSource.Address)
          'Formel einfügen -hier ggf. noch anpassen, welche Zellbezüge absolut sein sollen
          rngTarget.FormulaLocal = "='" & rngSource.Text & "'!" _
                  & rngSource.Address(Rowabsolute:=True, columnabsolute:=False, ReferenceStyle:=xlA1)
          
          'Anfang fügt in die restlichen 4 Zellen der Zeile 2 die Formeln ein
          rngTarget.AutoFill Destination:=Range(rngTarget, rngTarget.Offset(0, 4)), Type:= _
      xlFillDefault
          'Ende fügt in die restlichen 4 Zellen der Zeile 2 die Formeln ein
          
      Resume01:
          
      Fehler:
          With Err
              sMsg = "Fehler-Nr: " & .Number & vbLf & .Description
              Select Case .Number
              Case 0 'alles OK
              Case 9 'Index-Error
                  MsgBox sMsg & vbLf & vbLf & "Die berechnete Zelle """ & "'" _
                      & rngSource.Text & "'!" & rngSource.Address & """ existiert nicht!", _
                      vbInformation + vbOKOnly, "Fehler"
                  Resume Resume01
              Case Else
                  MsgBox sMsg, vbInformation + vbOKOnly, "Fehler"
              End Select
          End With
          
          'Anfang Aufhebung der Set-Anweisungen
          Set rngTest = Nothing
          Set rngTarget = Nothing
          Set AKto = Nothing
          'Ende Aufhebung der Set-Anweisungen
          
          'Ende Kontodaten in Zeile2 einfügen als Formel
      
      End Sub
      

      

    Betrifft: AW: Formel aus Zellen erstellen und einfügen als Forme von: 1713104.html
    Geschrieben am: 14.09.2019 19:07:10

    Hallo Franz,

    besten Dank für Deine Hilfe. Leider nicht mit dem entsprechenden Erfolg.

    1. Die erste Zeile wird richtig eingefügt.

    2. Die erste Formel wird eingefügt mit: "=Buchen_1087789!U$2"
    richtig wäre aber: "=Buchen_1087789!$U$2"

    dadurch

    3. die folgenden vier Zellen nach rechts sind falsch:
    erste Zelle rechts = "=Buchen_1087789!V$2", zweite Zelle rechts = "=Buchen_1087789!W$2", dritte Zelle rechts = "=Buchen_1087789!X$2", vierte Zelle rechts = "=Buchen_1087789!Y$2".

    Leider erkenne ich nicht auf Anhieb, was ich ändern müsste um die Bezüge zu ändern.

    Gruss
    Peter

      

    Betrifft: AW: Fehler gefunden von: 1713110.html
    Geschrieben am: 14.09.2019 19:23:05

    Hallo Franz,

    habe den Fehler gefunden.

    Die Zeile habe ich wie folgt geändert:

    rngTarget.FormulaLocal = "='" & rngSource.Text & "'!" _
    & rngSource.Address(Rowabsolute:=True, columnabsolute:=True, ReferenceStyle:=xlA1)
    'Fehler behoben durch columnabsolute:=True

    Wünsche Dir noch ein schönes Wochendende.

    Gruss
    Peter

      

    Betrifft: AW: das ist die Lösung von: 1712891.html
    Geschrieben am: 13.09.2019 13:10:13

    Hallo Chris,

    besten Dank für Deine Hilfe.

    Da ich eine komplexe Datenmenge übertragen muss, ist die zweite Lösung von Dir genau passend.

    Gruss
    Peter

    Beiträge aus dem Excel-Forum zum Thema "Formel aus Zellen erstellen und einfügen als Forme"