AW: VBA "Makro Range"
15.02.2006 18:21:10
ChrisL
Hallo Werner
Ich verstehe die Frage nur ansatzweise. Ein Schuss ins Blaue: Du willst die Zeile variabel und nicht die Spalte. Die letzte Zeile wird anhand der Spalte A ermittelt.
Dim LetzteZeile As Long
LetzteZeile = Range("A65536").End(xlUp).Row
Selection.AutoFill Destination:=Range ("c2:c" & LetzteZeile)
N.b. der Code würde flüssiger werden wenn du die Bildschirmaktualisierung abschaltest.
Application.ScreenUpdating = False
' Dein Code
Application.ScreenUpdating = True
Und ein paarmal Selektieren weniger wäre das Tüpfchen auf dem "i" ;-)
http://xlfaq.herber.de/xlbasics/main_sel.htm
Gruss
Chris
Sub GEShares()
' GEShares Makro
' Makro am 11.02.2006 von fg00jot aufgezeichnet
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""000000000"")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C26")
Range("C2:C26").Select
Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "Prozente"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""00"")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I26")
Range("I2:I26").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Range("L1").Select
Selection.Copy
Range("M1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("M2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""JJJJ-MM-TT"")"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M26")
Range("M2:M26").Select
Columns("M:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=6
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Range("P2").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]*-1"
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P26")
Range("P2:P26").Select
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
Range("Q1").Select
Selection.Interior.ColorIndex = xlNone
ActiveCell.FormulaR1C1 = "Betrag"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""0\.\0\0"")"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q26")
Range("Q2:Q26").Select
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
End Sub