Rahmen per Makro

Dieses Thema im Forum "Excel und VBA" wurde erstellt von Lancelot, 3 Juli 2008.

  1. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Hallo allerseits,

    ich will mittels Makro einen Rahmen um eine Tabelle setzen. Das habe ich mit dem Makrorecorder (zwar nicht die beste Lösung, aber ich bin in VBA nicht sonderlich bewandert) auch gemacht, aber da sich die Länge der Tabelle ändert, kann ich vorher ja Bereich nicht definieren.
    Der Rahmen beginnt immer bei A1 und geht immer bis zur Spalte O, nur die Länge variiert.
    Habe da an eine Inputbox gedacht, welche die letzte Zeile abfragt
    Code:
    varEnde = InputBox("Bitte Endzeile eingeben")
    ,
    allerdings komme ich jetzt nicht weiter, d.h. wie bringe ich das Makro jetzt dazu den Rahmen bis zur entsprechenden Stelle zu ziehen.

    Danke schon mal
    Gruss
    Lancelot
     
  2. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Morgen Lancelot,

    sowas?

    cu, Bernd
    --
    Code:
    Option Explicit
    
    Sub x()
       Dim lngZeile As Long
       
       lngZeile = CLng(Val(InputBox("Bitte Endzeile eingeben")))
       If lngZeile > 0 And lngZeile <= Rows.Count Then
          With Range("A1:O" & lngZeile)
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
             .Borders(xlInsideVertical).LineStyle = xlNone
             .Borders(xlInsideHorizontal).LineStyle = xlNone
             With .Borders ' entspricht xlEdgeLeft, xlEdgeTop, xlEdgeBottom und xlEdgeRight
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
             End With
          End With
       End If
    End Sub
     
  3. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Ja genau sowas, allerdings brauche ich nur den Rahmen aussenrum, nicht um jede Zelle. Seltsamerweise macht das Makro das nicht, obwohl der Code mit dem Makrorecorder aufgezeichnet etwas genauso aussieht.

    Gruss
     
  4. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    verschiebe die beiden Zeilen:

    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone

    mal nach unten.

    cu, Bernd
    --
    Code:
    Option Explicit
    
    Sub x2()
       Dim lngZeile As Long
       
       lngZeile = CLng(Val(InputBox("Bitte Endzeile eingeben")))
       If lngZeile > 0 And lngZeile <= Rows.Count Then
          With Range("A1:O" & lngZeile)
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
             With .Borders ' entspricht xlEdgeLeft, xlEdgeTop, xlEdgeBottom und xlEdgeRight
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
             End With
             .Borders(xlInsideVertical).LineStyle = xlNone
             .Borders(xlInsideHorizontal).LineStyle = xlNone
          End With
       End If
    End Sub
     
  5. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Das isses, Danke
    Mit dem Befehl
    Code:
    with borders
    werden ja alle Seiten angesprochen.
    Wenn ich den Rahmen aber jetzt nur unten haben will muss ich mit
    Code:
    With Selection.Borders(xlEdgeBottom)
    arbeiten ?

    Gruss
     
  6. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    ja, mein Kommentar war nicht ganz korrekt. Wenn Du wie oben nicht auf eine einzelne Zelle sondern einen Bereich losgehst betrifft das Setzen von .Borders nicht nur den äußeren Rahmen des Bereiches sondern auch die inneren Rahmen um die einzelnen Zellen.

    D.h. .Borders entspricht xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical und xlInsideHorizontal.

    Da diese oben alle auf einmal gesetzt wurden, muss man die inneren Teile am Ende wieder löschen. Oder - alternativ - sowas benutzen:

    cu, Bernd
    --
    Code:
    Option Explicit
    
    Sub x()
       Dim lngZeile As Long, varItem As Variant
       
       lngZeile = CLng(Val(InputBox("Bitte Endzeile eingeben")))
       If lngZeile > 0 And lngZeile <= Rows.Count Then
          With Range("A1:C" & lngZeile)
             .Borders.LineStyle = xlNone
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
             
             For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
                With .Borders(varItem)
                   .LineStyle = xlContinuous
                   .Weight = xlThin
                   .ColorIndex = xlAutomatic
                End With
             Next
          End With
       End If
    End Sub
     
  7. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Ja Super, das isses, habs angepasst mit dem Bottom und geht alles.

    Ein Problem hab ich noch (sorry muss das ausnutzen, dass ich jemanden habe, der Ahnung hat :))

    Die Tabellenlänge kann ja variieren. Jetzt möchte ich aber unter diese (also in die erste freie Zeile) folgendes reinschreiben lassen (siehe Anhang), beginnend in Spalte F.
    Ich hab schon mal mit
    Code:
    Dim Loletzte As Long
    Loletzte = IIf(IsEmpty(Range("A65536")), Range("A65536").End(xlUp).Row + 1, 65536)
    rumgespielt (ich probier halt erstmal was aus, bevor ich frage) aber iergendwie hats nicht geklappt.

    Gruss
     

    Anhänge:

  8. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    Obiges bestimmt die letzte Zeile aber aus Spalte A, das entspricht in Excel so in etwa dem Selektieren der Zelle A65536 und dann dem Drücken von STRG + PfeilNachOben und dann noch eine Zeile nach unten gehen.

    Nimm oben einfach zwei Mal F anstatt A um die letzte belegte Zeile aus der Spalte F zu bestimmen oder bestimme die letzte Zeile aus dem UsedRange (d.h. die letzte belegte Zeile 'aller Spalten'):

    Code:
    LoLetzte = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    bzw. die erste freie Zeile via:

    Code:
    LoLetzte = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
    cu, Bernd
     
  9. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Hallo,

    jetzt hab ich Deinen Code und meinen zusammengefügt und jetzt gehts leider nicht mehr.
    Mit meinem Code.
    Code:
    Range("A2:B2,C2:E2,F2:G2,H2:K2,L2:M2,N2:O2,A3:B25,C3:C25,D3:E25,F3:G25,H3:K25,L3:M25,N3:O25").Select
        
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
    formatiere ich einen Bereich.
    Jetzt kommt die Abfrage nach der letzten Zeile (Dein Code)

    Code:
    Dim lngZeile As Long, varItem As Variant
       
       lngZeile = CLng(Val(InputBox("Bitte Endzeile eingeben")))
       If lngZeile > 0 And lngZeile <= Rows.Count Then
          With Range("A1:O" & lngZeile)
             .Borders.LineStyle = xlNone
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
             
             'For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
             For Each varItem In Array(xlEdgeBottom)
                With .Borders(varItem)
                   .LineStyle = xlContinuous
                   .Weight = xlMedium
                   .ColorIndex = xlAutomatic
                End With
             Next
          End With
       End If
    Und danach sind die von mir vorher erzeugten Rahmen alle Weg.
    Meinen Code hab ich mit dem Makrorecorder aufgezeichnet, der ist halt nicht so optimiert.
    Vielleicht hast Du ja eine Idee

    Gruss



    Edit: Ich habs gelöst, hab einfach Deinen und meinen Codeteil vertauscht, vielleicht nicht nie eleganteste Lösung aber es geht.
     
    #9 Lancelot, 3 Juli 2008
    Zuletzt bearbeitet: 3 Juli 2008
  10. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Hallo Bernd,

    eine abschliessende Frage hab ich noch.
    Da meine Tabelle ja ausser den variablen Aussenrahmen auch noch verschiedene Innenrahmen hat....
    Kann man diesen Code
    Code:
    With Range("A3:B" & lngZeile)
             .Borders.LineStyle = xlNone
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
         For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
             'For Each varItem In Array(xlEdgeBottom)
                With .Borders(varItem)
                   .LineStyle = xlContinuous
                   .Weight = xlMedium
                   .ColorIndex = xlAutomatic
                End With
             Next
          End With
          
          With Range("C3:E" & lngZeile)
             .Borders.LineStyle = xlNone
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
         For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
             'For Each varItem In Array(xlEdgeBottom)
                With .Borders(varItem)
                   .LineStyle = xlContinuous
                   .Weight = xlMedium
                   .ColorIndex = xlAutomatic
                End With
             Next
          End With
          
          With Range("F3:G" & lngZeile)
             .Borders.LineStyle = xlNone
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
         For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
             'For Each varItem In Array(xlEdgeBottom)
                With .Borders(varItem)
                   .LineStyle = xlContinuous
                   .Weight = xlMedium
                   .ColorIndex = xlAutomatic
                End With
             Next
          End With
    .
    .
    .
    .
    irgendwie zusammenfassen.
    Ich habs schon mit
    Code:
     With Range("F3:G,C3:E"" & lngZeile)
    versucht, aber das geht nicht

    Gruss
     
  11. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    Du brauchst dann hier sowas ähnliches:

    Range("A3:B" & lngZeile & ",C3:D" & lngZeile & ",E3:F" & lngZeile)

    Einfacher ist es aber wohl die Rahmen-Routine in eine eigene Sub auszulagern und sie ggf. mehrmals aufzurufen.

    Unten könntest Du dann natürlich die 3 SetBorder Aufrufe auch durch einen einzigen SetBorder Aufruf ersetzen:

    SetBorder Range("A3:B" & lngZeile & ",C3:D" & lngZeile & ",E3:F" & lngZeile)

    Du kannst hier nicht Union oder auch Range("A3:F" & lngZeile) o.ä. benutzen da Dir hier sonst die inneren Linien rechts der Spalten B und D fehlen!

    cu, Bernd
    --
    Code:
    Option Explicit
    
    Sub x()
       Dim lngZeile As Long
       
       lngZeile = 10
       SetBorder Range("A3:B" & lngZeile)
       SetBorder Range("C3:E" & lngZeile)
       SetBorder Range("F3:G" & lngZeile)
    End Sub
    
    Private Sub SetBorder(r As Range)
       Dim varItem As Variant
       
       With r
          .Borders.LineStyle = xlNone
          .Borders(xlDiagonalDown).LineStyle = xlNone
          .Borders(xlDiagonalUp).LineStyle = xlNone
          For Each varItem In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
             With .Borders(varItem)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
             End With
          Next
       End With
    End Sub
     
  12. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Gibt es auch die Möglichkeit mittels VBA Formeln in Zellen zu schreiben.
    z.B. kann ich ja mit
    Code:
    Range("G27").Select
    ActiveCell.FormulaR1C1 = "Fertigungsauftrag"
    
    Text in eine Zelle schreiben. Gehts das auch mit

    Code:
    =Summe(F3:F30)?
    Edit: erst suchen und testen, dann posten......habs selbst rausgekriegt.
    Danke Dir jedenfalls für die Tips heute

    Gruss
    *******
     
    #12 Lancelot, 3 Juli 2008
    Zuletzt bearbeitet: 3 Juli 2008
  13. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Oh Mann, gib Kollegen den kleinen Finger.......

    mit
    Code:
    Dim i As Long
        Dim oBlatt As Worksheet
    Set oBlatt = ThisWorkbook.Worksheets("Baab") 'Tabellennamen gg. anpassen
    i = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(i + 3, 6).Select
            Selection.FormulaLocal = "=SUMME(F4:F25)"
            
    trage ich eine Formel in eine bestimmt Zelle am Ende der Tabelle.
    Aber wie berücksichtige ich in der Formel hier die variable Tabellenlänge.
    Diese könnte ja auch bnis Zeile 30 oder nur bis Zeile 10 gehen.
    Ist das machbar oder sehr aufwändig.

    Gruss

    *******
     
  14. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    fange mit einer neuen Frage bitte auch einen neuen Thread an, ja?

    - i bestimmt die Anzahl der Zeilen hier aus ActiveSheet, nicht aus oBlatt

    - Den Select kannst Du dir sparen

    - Was willst Du denn anpassen, das i für die Zielzelle oder den Bereich der Summe?

    Sowas vielleicht?

    cu, Bernd
    --
    Code:
    Option Explicit
    
    Sub x()
       Dim i As Long
       Dim oBlatt As Worksheet
       
       Set oBlatt = ThisWorkbook.Worksheets("Baab") 'Tabellennamen gg. anpassen
       With oBlatt
          i = .Cells(.Rows.Count, 1).End(xlUp).Row ' solange in A65536 nichts steht geht das, besser nimm:
          i = IIf(Len(.Cells(.Rows.Count, 1)), .Rows.Count, .Cells(.Rows.Count, 1).End(xlUp).Row)
          If i + 3 <= .Rows.Count Then
             .Cells(i + 3, 6).Formula = "=SUM(F4:F" & i & ")"
          End If
       End With
    End Sub
    
    
     
  15. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Hallo,

    also da ich mittels der Summenfunktion Werte in bestimmten Spalten errechnen will, und die Tabellenlänge sich ja ändert, ändert sich der Bereich der Summe.
    Ergo muss sich der Bereich der Summe anpassen.
    Ich glaube
    Code:
    .Cells(i + 3, 6).Formula = "=SUM(F4:F" & i & ")"
    das ist sowas, ich werds mal testen.

    Danke

    und sorry wegen dem Thread

    Gruss
    *******
     
  16. Lancelot

    Lancelot
    Expand Collapse
    New Member

    Registriert seit:
    15 Mai 2008
    Beiträge:
    33
    Zustimmungen:
    0
    Super klappt.

    Jetzt kann ich ja mittels
    Code:
    .Cells(i + 2, 11).Select
    bestimmte Zellen anspringen.
    Wie kann ich diese aber verbinden
    also
    Code:
    .Cells(i + 2, 11) und .cells(i + 2, 12)
    Hab etwas rumprobiert mit "&" und "select", aber das funktioniert nicht.

    Ohne die variable Länge geht es ja einfach mittels
    Code:
    Range("H29:K29").Select
        With Selection
             .MergeCells = True
    Gruss


    Edit: so mit
    Code:
    .Range(.Cells(i + 4, 8), .Cells(i + 4, 9)).MergeCells = True
    hat auch das geklappt
     
    #16 Lancelot, 3 Juli 2008
    Zuletzt bearbeitet: 3 Juli 2008
  17. bst

    bst
    Expand Collapse
    <font color="#FF0000">Excel Moderator</font><br><i

    Registriert seit:
    26 Oktober 2004
    Beiträge:
    3.063
    Zustimmungen:
    1
    Hi,

    ich - ganz persönlich - halte überhaupt nichts vom Verbinden von Zellen, damit hat man M.E. weit aus mehr Schwierigkeiten als es Nutzen bringt.

    Wie dem auch sei, nimm hierzu einfach .Resize. Das ist (ebenso wie .Offset) M.E. sehr praktisch. Siehe die VBA-Hilfe dazu.

    Code:
    Cells(i + 2, 11).Resize(, 2).MergeCells = True
    Alternativ ginge auch sowas:

    Code:
    Range(Cells(i + 2, 11), Cells(i + 2, 12)).MergeCells = True
    cu, Bernd
     
Die Seite wird geladen...

Diese Seite empfehlen