Formel in Range(Cells(x,y), cells(x,y)) packen.... aber wie...??

  • Excel

Es gibt 9 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    Formel in Range(Cells(x,y), cells(x,y)) packen.... aber wie...??

    Hallo liebe Community!

    Ich würde gerne wissen, wie ich eine Formel einem Range-Cells Ausdruck zuweisen kann...

    r entspricht einer Variablen, der ein Zeilenwert(Zeilennummer) zugewiesen wird.

    Nun... wenn ich also einfach nur einen Ausdruck ausgeben möchte z.B.:

    Visual Basic-Quellcode

    1. Range(Cells(r, 1), Cells(r, 1)).Value = 25


    Dann schreibt der mir das schön brav hin!

    Mein Ziel ist es jedoch, dass ich der Zelle eine Formel zuweisen möchte!

    Also in etwa sowas...:

    Visual Basic-Quellcode

    1. Range(Cells(r, 1), Cells(r, 1)).Value = Range.FormulaR1C1 = "='4 - Aufträge mit Null Beträgen'!RC1"


    ... nur wird das nicht ganz gehen, da das ja kein value ist...

    aber... wie muss ich denn das schreiben, dass ich mit Cells-Angabe den .Formula Ausdruck der Zelle zuweisen kann??

    Dankeschön!

    LG Tim

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ereza“ ()

    hi xtts02!

    danke für den hinweis... aber.. das funktioniert bei mir nicht...

    weil... das ist mein Dilemma... ich möchte nicht mit einer fixen Zelle arbeiten...

    wenn ich also das probiere:

    Visual Basic-Quellcode

    1. Range(Cells(r,1), Cells(r,1)).FormulaR1C1 = ".......blabla..."


    dann geht das leider nicht :((
    ich meine folgendes...

    So wäre mein ursprünglicher code:

    Visual Basic-Quellcode

    1. Private Sub CommandButton2_Click()
    2. Dim a As Long
    3. Dim b As Long
    4. Dim r As Long
    5. a = Tabelle3.Cells(Rows.Count, 1).End(xlUp).Row
    6. b = Tabelle5.Cells(Rows.Count, 1).End(xlUp).Row
    7. Tabelle6.Range("A2:AR1048576").ClearContents
    8. Range("A2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC1"
    9. Range("B2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC2"
    10. Range("C2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC3"
    11. Range("D2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC4"
    12. Range("E2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC5"
    13. Range("F2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC6"
    14. Range("G2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC7"
    15. Range("H2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC8"
    16. Range("I2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC9"
    17. Range("J2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC10"
    18. Range("K2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC11"
    19. Range("L2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC12"
    20. Range("M2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC13"
    21. Range("N2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC14"
    22. Range("O2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC15"
    23. Range("P2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC16"
    24. Range("Q2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC17"
    25. Range("R2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC18"
    26. Range("S2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC19"
    27. Range("T2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC20"
    28. Range("U2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC21"
    29. Range("V2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC22"
    30. Range("W2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC23"
    31. Range("X2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC24"
    32. Range("Y2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC25"
    33. Range("Z2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC26"
    34. Range("AA2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC27"
    35. Range("AB2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC28"
    36. Range("AC2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC29"
    37. Range("AD2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC30"
    38. Range("AE2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC31"
    39. Range("AF2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC32"
    40. Range("AG2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC33"
    41. Range("AH2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC34"
    42. Range("AI2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC35"
    43. Range("AJ2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC36"
    44. Range("AK2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC37"
    45. Range("AL2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC38"
    46. Range("AM2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC39"
    47. Range("AN2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC40"
    48. Range("AO2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC41"
    49. Range(Cells(2, 1), Cells(a, 41)).FillDown
    50. r = Cells(Rows.Count, 1).End(xlUp).Row + 1
    51. Range(Cells(r, 1), Cells(r, 1)).FormulaR1C1 = "='4 - Aufträge mit Null Beträgen'!RC1"
    52. End Sub


    So... heißt... der holt sich von 2 - Kosten EXKL. Null-Beträge die ganzen Daten raus und schreibt sie hin... beginnend von Zelle A2 weg...

    Nach dem Filldown vom ersten part soll ein zweiter part von '4 - Aufträge mit Null Beträgen' dazugehängt werden...

    Jz möchte ich natürlich dass der dort anfängt wo er aufgehört hat in Spalte A. Deshalb sage ich:

    Visual Basic-Quellcode

    1. r = Cells(Rows.Count, 1).End(xlUp).Row + 1


    Damit wäre das die erste freie Zelle in Spalte A.

    So, nun soll er dort fortsetzen und mir von '4 - Aufträge mit Null Beträgen' den Wert herholen... Aber da liegt mein Problem.. denn in '4 - Aufträge mit Null Beträgen' soll er von A2 (also im Tabellenblatt: '4 - Aufträge mit Null Beträgen' von Zelle A2) den Wert beziehen...

    Das tut er aber nicht... sondern nimmt vom aktuellen worksheet, die Zeilennummer... (in meinem Fall wäre das 804)

    Also wenn ich auf diese Zelle klicke steht als Wert:

    ='4 - Aufträge mit Null Beträgen'!$A804

    drinnen....

    Wie schaffe ich es also, dass der mir von "oben" in "4 - Aufträge mit Null Beträgen" beginnt?? und mir dann: ='4 - Aufträge mit Null Beträgen'!$A2 hinschreibt??
    Ändere

    Visual Basic-Quellcode

    1. Range(Cells(r, 1), Cells(r, 1)).FormulaR1C1 = "='4 - Aufträge mit Null Beträgen'!RC1"

    Entweder in die A1 schreibweise also

    Visual Basic-Quellcode

    1. Range(Cells(r, 1), Cells(r, 1)).Formula "=4-'Aufträge mit Null Beträgen!'!A2"


    Oder erechne halt das richtig R1C1 Format anhand von deinem r

    Visual Basic-Quellcode

    1. Range(Cells(r, 1), Cells(r, 1)).FormulaR1C1 ="=4-'Aufträge mit Null Beträgen!'!R[-" & CStr(r - 2) & "]C"


    LG
    Das ist meine Signatur und sie wird wunderbar sein!
    FormulaR1C1 kannst Du meines Wissens nur einzelner Zelle zuweisen. Also wenn Du einen ganzen Bereich mit Formel füllen willst, musst Du alle Zellen dieses Bereiches durchgehen und die Formel in die einzelne Zelle setzen. Zum Beispiel

    Visual Basic-Quellcode

    1. Dim colOffset, rowOffset As Long
    2. Dim srcSheet As Worksheet
    3. Set srcSheet = ThisWorkbook.Worksheets("Tabelle1")
    4. For colOffset = 0 To srcSheet.UsedRange.Columns.Count - 1
    5. For rowOffset = 0 To srcSheet.UsedRange.Rows.Count - 1
    6. Range("A1").offset(rowOffset, colOffset).FormulaR1C1 = "=Tabelle1!RC"
    7. Next
    8. Next

    xtts02 schrieb:

    FormulaR1C1 kannst Du meines Wissens nur einzelner Zelle zuweisen
    Ne, müsste auch für einen ganzen Range funktionieren.

    Aber was äusserst merkwürdig ist, ist diese Konstruktion: Range(Cells(r, 1), Cells(r, 1))
    Das ergibt einen Range aus einer einzelnen Zelle.

    Ansonsten gelten die Aussagen von aus Post #6.
    Willst du tatsächlich eine Formel, also dass sich das Sheet dynamisch verändert, wenn die referenzierte Zelle sich ändert, oder willst du nur den Wert übertragen?
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Hallo!

    Aber was äusserst merkwürdig ist, ist diese Konstruktion: Range(Cells(r, 1), Cells(r, 1))
    Das ergibt einen Range aus einer einzelnen Zelle.


    Ja, ich wollte, das so, damit ich in dieser "Cells-Format-Schreibweise" die Zeilenvariable r unterbringe und das dem ganzen eine Formel zuweisen kann...

    Im Grunde hat mir das aber alles schon super weitergeholfen, denn...
    ... ich habe mein Problem nun mit eurer Hilfe folgendermaßen, erfolgreich und sehr zufriedenstellend gelöst...:

    Visual Basic-Quellcode

    1. Private Sub CommandButton2_Click()
    2. Dim a As Long
    3. Dim b As Long
    4. Dim c As Long
    5. Dim r As Long
    6. a = Tabelle3.Cells(Rows.Count, 1).End(xlUp).Row
    7. b = Tabelle5.Cells(Rows.Count, 1).End(xlUp).Row - 1
    8. c = a + b
    9. Tabelle6.Range("A2:AR1048576").ClearContents
    10. Range("A2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC1"
    11. '..... usw.. bis....
    12. Range("AO2").FormulaR1C1 = "='2 - Kosten EXKL. Null-Beträge'!RC41"
    13. Range(Cells(2, 1), Cells(a, 41)).FillDown 'Filldown von Zeile 2 bis zur letzten Zeile mit dem Index aus Variable a
    14. r = Cells(Rows.Count, 1).End(xlUp).Row + 1
    15. Range(Cells(r, 1), Cells(r, 1)).FormulaR1C1 = "='4 - Aufträge mit Null Beträgen'!R[-" & CStr(r - 2) & "]C1"
    16. '..... usw.. bis....
    17. Range(Cells(r, 41), Cells(r, 41)).FormulaR1C1 = "='4 - Aufträge mit Null Beträgen'!R[-" & CStr(r - 2) & "]C41"
    18. Range(Cells(r, 1), Cells(c, 41)).FillDown
    19. End Sub



    So funktionierts! :))

    Vielen herzlichen Dank nochmals für eure Hilfe!!


    LG

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ereza“ ()

    ereza schrieb:

    Ja, ich wollte, das so, damit ich in dieser "Cells-Format-Schreibweise" die Zeilenvariable r unterbringe und das dem ganzen eine Formel zuweisen kann...
    Das geht einfacher.
    Range(Cells(r, 1), Cells(r, 1)) ist dasselbe wie Cells(r,1) ;)
    Beides sind Range-Objekte, die nur diese eine Zelle beinhalten.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --