Befehle kürzer schreiben

Hallo,
diese Befehle lassen sich sicherlich vereinfachen. Kann mir jemand zeigen, wie?
Danke
Gruß Karsten
Range("b1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R3C2"
Range("C1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R4C3"
Range("b1").Select
Application.Run "nnn02"
Range("b1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R5C2"
Range("C1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R6C3"
Range("b1").Select
Application.Run "nnn02"
Range("b1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R7C2"
Range("C1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R8C3"
Range("b1").Select
Application.Run "nnn02"
Range("b1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R9C2"
Range("C1").Select
ActiveCell.FormulaR1C1 = "='Stundenplan'!R10C3"
Range("b1").Select
Application.Run "nnn02"

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

HalloAndrRo,
Danke
[B1].Select ist, wie ich gerade sehe, wohl überflüssig. Den nnn02-Makro habe ich mal drunter kopiert.

Sub nnn02()
Range("c1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("b1").Select
Dim i As Integer
Dim n As String
Dim z As Range
For Each z In Intersect(Selection, ActiveSheet.UsedRange)
n = z.Value
i = InStr(n, ",")
If i > 0 Then
z.Value = Left(n, i + 2) & ""
End If
Next z
Set R1 = Cells(1, 3).find("n")
If R1 Is Nothing Then
Exit Sub
End If
Dim b As Range
Range("Spalte_erste_Zelle:Spalte_letzte_Zelle").Select
Set cb = Selection.find(What:=Cells(1, 2), SearchFormat:=False)
If cb Is Nothing Then
MsgBox ActiveSheet.Range("b1").Value
Else
End If
End Sub

Sub BefehleKuerzerSchreiben() Dim lngZeile As Long For lngZeile = 3 To 9 Step 2 [B1].FormulaR1C1 = "='Stundenplan'!R" & lngZeile & "C2" [C1].FormulaR1C1 = "='Stundenplan'!R" & lngZeile + 1 & "C3" [B1].Select 'Ist das unbedingt notwendig ? - Was passiert in Makro "nnn02" ? Application.Run "nnn02" Next End Sub

Sub nnn02() Range("c1").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("b1").Select Dim i As Integer Dim n As String Dim z As Range For Each z In Intersect(Selection, ActiveSheet.UsedRange) n = z.Value i = InStr(n, ",") If i > 0 Then z.Value = Left(n, i + 2) & "" End If Next z Set R1 = Cells(1, 3).find("n") If R1 Is Nothing Then Exit Sub End If Dim b As Range Range("Spalte_erste_Zelle:Spalte_letzte_Zelle").Select Set cb = Selection.find(What:=Cells(1, 2), SearchFormat:=False) If cb Is Nothing Then MsgBox ActiveSheet.Range("b1").Value Else End If End Sub

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread