Loop sehr langsam

[snb]

@Schauan

Beiepieldaten hochladen scheint noch immer 'unheimlich' schwierig zu sein ;)

Und deine Array Verwendung muss bestimmt schneller sein.

[and.so]

Sorry, dass ich die Datei nicht hochladen kann - ist wie gesagt recht komplex mit 19 Sheets und enthält viele private Daten, die man erst bereinigen müsste. Ich denke, wir können es auch so lösen. Der Bezug ist indirekt auf B2. Einfach vorstellen, C4 = B2. Oder in den Index/Match Formeln C4 durch B2 ersetzen.

Den Array Code habe ich leicht angepasst (um in die richtigen Zellen zu kopieren) und er läuft durch. Laufzeit 30 Sekunden; meine vorherige Lösung 35 Sekunden. Also schonmal vielen Dank dafür :)

Hier die angepasst Version:

Code:

Sub Test()
Application.ScreenUpdating = False

Dim i As Long, arrData(1 To 63, 1)
With Sheets("WKN Data")
  For i = 1 To 63

    .Range("B2").Value = i
    arrData(i, 0) = .Range("C3").Value
    arrData(i, 1) = .Range("G3").Value
  Next i
  .Range(.Cells(5, 10), Cells(68, 11)).Value = arrData()
End With

End Sub

2 Fragen dazu:
- kann ich die "63" variabel lassen? Also z.B. soll dort der Wert aus Zelle I3 rein. Hat bei mir leider nicht geklappt, als ich es versucht habe
- Einige Daten im Array müssten theoretisch nicht aktualisiert werden (siehe dazu meinen letzten Code) - wäre das in dieser Version des Codes auch möglich? Dh, wenn I5-68 mit "*" beginnen, muss der Wert nicht aktualisiert werden. Die Daten vorzusortieren geht leider nicht.

Nochmal vielen Dank für die Hilfe!!
Andreas

[snb]

Bitte erzähle:

welche Daten in
   Sheets("Performance").Range("N$14:N$183")

dau mussen die Ziffern 1 bis 143 (sortiert ?) stehen ?

und welche in
    Sheets("Performance").Range("$B$14:$B$183")

[and.so]

In den beiden Spalten stehen jeweils feststehende Werte - in B stehen Namen (zur Identifikation des Index Match in 'WKN Data' Zelle C4. In Spalte N stehen dann entsprechende %-Werte, die ich in 'WKN Data' in Spalte J kopieren möchte. Ich könnte also theoretisch Spalte J direkt über das Sheet 'Performance' füttern und nicht über das Makro laufen lassen. Würde das Zeit sparen? Dachte, da der Loop sowieso durchlaufen muss macht es keinen Unterschied..

Die Sortierung der Namen ist immer die gleiche, da sonst der Loop die falschen Daten kopiert.

Danke!

[snb]

Dann sind alle Match() Befehle überflüssig, denn es gibt constante Kombinationen von namen und Werten.

[and.so]

Ja - wie gesagt für Spalte J stimmt das und man kann sich einen Teil des Makros sparen.
Für Spalte K stimmt das nicht - denn die Werte dafür werden auf Basis von B2/C4 durchgehend neu errechnet.

[and.so]

Also, ich habe jetzt alle Matches ersetzt und das Makro auf eine Spalte reduziert, in der Hoffnung das bringt etwas mehr Performance - leider nicht, Laufzeit immer noch 30 Sekunden..

Code:

Sub Test()
Application.ScreenUpdating = False

Dim i As Long, arrData(1 To 63, 0)
With Sheets("WKN Data")
  For i = 1 To 63

    .Range("B2").Value = i
    '.Cells(i + 4, 10).Value = .Range("C3").Value
    '.Cells(i + 4, 11).Value = .Range("G3").Value
    arrData(i, 0) = .Range("G3").Value
    ' arrData(i, 1) = .Range("G3").Value
  Next i
  .Range(.Cells(5, 11), Cells(68, 11)).Value = arrData()
End With

End Sub

Meine beiden Fragen von vorhin sind also weiterhin relevant -
- kann ich die "63" variabel lassen? Also z.B. soll dort der Wert aus Zelle I3 rein. Hat bei mir leider nicht geklappt, als ich es versucht habe

- Einige Daten im Array müssten theoretisch nicht aktualisiert werden (siehe dazu Beitrag #13) - wäre das in dieser Version des Codes auch möglich? Dh, wenn I5-68 mit "*" beginnen, muss der Wert nicht aktualisiert werden. Die Daten vorzusortieren geht leider nicht.

[Kuwer]

Hallo Andreas,

Sub Test()

 Dim i As Long, lngZ As Long

 Dim arrData As Variant, arrFilter As Variant

 

 Application.ScreenUpdating = False

 With Sheets("WKN Data")

   lngZ = .Range("J3").Value

   ReDim arrData(1 To lngZ, 1 To 1)

   arrFilter = .Range("J5").Resize(lngZ).Value

   For i = 1 To lngZ

     If Left(arrFilter(i, 1), 1) <> "*" Then

       .Range("B2").Value = i

       arrData(i, 1) = .Range("G3").Value

     End If

   Next i

   .Range("K5").Resize(lngZ).Value = arrData

 End With

 Application.ScreenUpdating = True

End Sub

Gruß Uwe

[and.so]

Danke Uwe - funktioniert mit leichten Anpassungen bzgl. der Zelllreferenzen. Die nicht betroffenen Zeilen bleiben in diesem Fall logischerweise leer. Das behebe ich dann durch eine Referenz der leeren Zellen auf eine Hilfsspalte.

Code:

Sub WKN_Update()
 Dim i As Long, lngZ As Long
 Dim arrData As Variant, arrFilter As Variant
 
 Application.ScreenUpdating = False
 With Sheets("WKN Data")
   lngZ = .Range("I3").Value
   ReDim arrData(1 To lngZ, 1 To 1)
   arrFilter = .Range("I5").Resize(lngZ).Value
   For i = 1 To lngZ
     If Left(arrFilter(i, 1), 2) <> " *" Then
       .Range("B2").Value = i
       arrData(i, 1) = .Range("G3").Value
     End If
   Next i
   .Range("K5").Resize(lngZ).Value = arrData
   .Range("K5:K147").Select
    Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[9]"
    .Range("A1").Select
End With
End Sub

Gesamtlaufzeit ist damit knapp über 10 Sekunden - passt!
Nochmal vielen Dank an alle!

[Kuwer]

Hallo,

wenn fehlende Werte aus Spalte T geholt werden, kann man ja gleich das Array mit Spalte T füllen.

Sub Test()

 Dim i As Long, lngZ As Long

 Dim arrData As Variant, arrFilter As Variant

 

 Application.ScreenUpdating = False

 With Sheets("WKN Data")

   lngZ = .Range("J3").Value

   'ReDim arrData(1 To lngZ, 1 To 1)

   arrData = .Range("T5").Resize(lngZ).Value

   arrFilter = .Range("I5").Resize(lngZ).Value

   For i = 1 To lngZ

     If Left(arrFilter(i, 1), 1) <> "*" Then

       .Range("B2").Value = i

       arrData(i, 1) = .Range("G3").Value

     End If

   Next i

   .Range("K5").Resize(lngZ).Value = arrData

 End With

 Application.ScreenUpdating = True

End Sub

Deine hinzugefügte Select-Orgie tut schon weh, zumal es ja hier gerade um Zeiteinsparung geht.  :22:

Gruß Uwe