Monate als Array

Sub Test() Dim VMonat As Variant VMonat = Array(" ", "Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", _ "August", "September", "Oktober", "November", "Dezember") VMonat.Range("C3:AG147").FormulaR1C1 = "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)" ' _ Formel einfügen End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Hallo Heinz,

Sub Test1()
  Dim lngIndex As Long
  
  For lngIndex = 1 To 12
    Sheets(Format(DateSerial(1, lngIndex, 1), "MMMM")).Range("C3:AG147").FormulaR1C1 = _
      "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)"
  Next
  
End Sub



'oder
Sub Test2()
  Dim VMonat  As Variant
  Dim lngIndex As Long
  
  VMonat = Array(" ", "Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", _
    "August", "September", "Oktober", "November", "Dezember")
  
  For lngIndex = 0 To UBound(VMonat)
    Sheets(VMonat(lngIndex)).Range("C3:AG147").FormulaR1C1 = _
      "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)"
  Next
  
End Sub

« Gruß Sepp »

Option Explicit 'von: Josef Ehrensberger Formel von Jänner bis Dezember Sub Test2() Dim lngIndex As Long Application.ScreenUpdating = False For lngIndex = 1 To 12 Sheets(Format(DateSerial(1, lngIndex, 1), "MMMM")).Range("C3:AG147").FormulaR1C1 = _ "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)" Next End Sub

Sub Test2() Dim lngIndex As Long Application.ScreenUpdating = False For lngIndex = 1 To 12 With Sheets(Format(DateSerial(1, lngIndex, 1), "MMMM")).Range("C3:AG147") .FormulaR1C1 = "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)" .Value = .Value End With Next End Sub

Option Explicit Sub Makro1() Dim VMonat ' As Variant VMonat = Array(" ", "Jänner", "Februar", "März", "April", "Mai", "Juni", _ "Juli", "August", "September", "Oktober", "November", "Dezember") Sheets(VMonat).Select Range("C3:AG147").Select Selection.FormulaR1C1 = "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)" End Sub

Sub Test() Dim VMonat, b As Variant VMonat = Array("Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", _ "August", "September", "Oktober", "November", "Dezember") For b = Lbound(VMonat) To Ubound(VMonat) - 1 Sheets(VMonat(b + 1)).Range("C3:AG147").FormulaR1C1 = _ "=INDEX(R500C3:R523C33,MATCH(RC2,R500C2:R523C2,0),)" Next End Sub

Beliebteste Forumthreads (12 Monate)

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread