Formel aus Zellen erstellen und einfügen als Forme

Q: Anzeigen von Werten und Formeln

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Zeile einfügen und Formeln und Werte übernehmen

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Formeln mit Zelladressen speichern

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Zeilenumbruch in Formel erzwingen

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Aktueller Arbeitsblattname in Formel

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Variable in Formel einbauen

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Ein Zeichen in einer Formel suchen

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Variable in Excel-Formel einbauen.

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Summenformel unter Zahlenreihe eintragen

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Q: Arbeitszeittabelle und SVERWEIS-Formel

Antworten und eine Beispielarbeitsmappe zu Fragen zum Thema "Formel" finden Sie hier: Hier klicken

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

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

Formel aus Zellen erstellen und einfügen als Forme

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

AW: Formel aus Zellen erstellen und einfügen als Forme - von ChrisL am 13.09.2019 12:52:34

AW: Formel aus Zellen erstellen und einfügen als Forme - von fcs am 13.09.2019 13:09:44

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 13.09.2019 13:19:35

AW: Formel aus Zellen erstellen und einfügen als Forme - von Daniel am 13.09.2019 13:25:51

AW: Verstanden - von Peter am 13.09.2019 13:27:51

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 13.09.2019 17:49:52

AW: Formel aus Zellen erstellen und einfügen als Forme - von Werner am 13.09.2019 23:45:47

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 14.09.2019 06:57:15

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 14.09.2019 07:26:00

AW: Formel aus Zellen erstellen und einfügen als Forme - von Werner am 14.09.2019 12:33:36

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 14.09.2019 12:42:29

AW: Formel aus Zellen erstellen und einfügen als Forme - von fcs am 14.09.2019 14:26:42

AW: Formel aus Zellen erstellen und einfügen als Forme - von Peter am 14.09.2019 19:07:10

AW: Fehler gefunden - von Peter am 14.09.2019 19:23:05

AW: das ist die Lösung - von Peter am 13.09.2019 13:10:13

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