Eleganz mit Schleifen

Bild

Betrifft: Eleganz mit Schleifen
von: Klaus Moucha
Geschrieben am: 08.06.2015 22:19:02

Hey Leute,
ich bin noch ziemlich am Anfang mit meinen VBA-Kenntnissen und versuche das Programmieren zu erlernen.
Hierzu eine banale Frage.
Für die Berechnung einer Matrix benötige ich habe folgende Formeln in VBA:
For i = 0 To 29
Cells(3 + i, 11) = Sqr((Cells(3 + i, 2) - Cells(2, 2)) ^ 2 + (Cells(3 + i, 3) - Cells(2, 3)) ^ 2)
Next i
For i = 0 To 28
Cells(4 + i, 12) = Sqr((Cells(4 + i, 2) - Cells(3, 2)) ^ 2 + (Cells(4 + i, 3) - Cells(3, 3)) ^ 2)
Next i
For i = 0 To 27
Cells(5 + i, 13) = Sqr((Cells(5 + i, 2) - Cells(4, 2)) ^ 2 + (Cells(5 + i, 3) - Cells(4, 3)) ^ 2)
Next i
For i = 0 To 26
Cells(6 + i, 14) = Sqr((Cells(6 + i, 2) - Cells(5, 2)) ^ 2 + (Cells(6 + i, 3) - Cells(5, 3)) ^ 2)
Next i
For i = 0 To 25
Cells(7 + i, 15) = Sqr((Cells(7 + i, 2) - Cells(6, 2)) ^ 2 + (Cells(7 + i, 3) - Cells(6, 3)) ^ 2)
Next i
For i = 0 To 24
Cells(8 + i, 16) = Sqr((Cells(8 + i, 2) - Cells(7, 2)) ^ 2 + (Cells(8 + i, 3) - Cells(7, 3)) ^ 2)
Next i
For i = 0 To 23
Cells(9 + i, 17) = Sqr((Cells(9 + i, 2) - Cells(8, 2)) ^ 2 + (Cells(9 + i, 3) - Cells(8, 3)) ^ 2)
Next i
For i = 0 To 22
Cells(10 + i, 18) = Sqr((Cells(10 + i, 2) - Cells(9, 2)) ^ 2 + (Cells(10 + i, 3) - Cells(9, 3)) ^ 2)
Next i
For i = 0 To 21
Cells(11 + i, 19) = Sqr((Cells(11 + i, 2) - Cells(10, 2)) ^ 2 + (Cells(11 + i, 3) - Cells(10, 3)) ^ 2)
Next i
For i = 0 To 20
Cells(12 + i, 20) = Sqr((Cells(12 + i, 2) - Cells(11, 2)) ^ 2 + (Cells(12 + i, 3) - Cells(11, 3)) ^ 2)
Next i
For i = 0 To 19
Cells(13 + i, 21) = Sqr((Cells(13 + i, 2) - Cells(12, 2)) ^ 2 + (Cells(13 + i, 3) - Cells(12, 3)) ^ 2)
Next i
For i = 0 To 18
Cells(14 + i, 22) = Sqr((Cells(14 + i, 2) - Cells(13, 2)) ^ 2 + (Cells(14 + i, 3) - Cells(13, 3)) ^ 2)
Next i
For i = 0 To 17
Cells(15 + i, 23) = Sqr((Cells(15 + i, 2) - Cells(14, 2)) ^ 2 + (Cells(15 + i, 3) - Cells(14, 3)) ^ 2)
Next i
For i = 0 To 16
Cells(16 + i, 24) = Sqr((Cells(16 + i, 2) - Cells(15, 2)) ^ 2 + (Cells(16 + i, 3) - Cells(15, 3)) ^ 2)
Next i
For i = 0 To 15
Cells(17 + i, 25) = Sqr((Cells(17 + i, 2) - Cells(16, 2)) ^ 2 + (Cells(17 + i, 3) - Cells(16, 3)) ^ 2)
Next i
For i = 0 To 14
Cells(18 + i, 26) = Sqr((Cells(18 + i, 2) - Cells(17, 2)) ^ 2 + (Cells(18 + i, 3) - Cells(17, 3)) ^ 2)
Next i
For i = 0 To 13
Cells(19 + i, 27) = Sqr((Cells(19 + i, 2) - Cells(18, 2)) ^ 2 + (Cells(19 + i, 3) - Cells(18, 3)) ^ 2) * UMF
Next i
For i = 0 To 12
Cells(20 + i, 28) = Sqr((Cells(20 + i, 2) - Cells(19, 2)) ^ 2 + (Cells(20 + i, 3) - Cells(19, 3)) ^ 2)
Next i
For i = 0 To 11
Cells(21 + i, 29) = Sqr((Cells(21 + i, 2) - Cells(20, 2)) ^ 2 + (Cells(21 + i, 3) - Cells(20, 3)) ^ 2)
Next i
For i = 0 To 10
Cells(22 + i, 30) = Sqr((Cells(22 + i, 2) - Cells(21, 2)) ^ 2 + (Cells(22 + i, 3) - Cells(21, 3)) ^ 2)
Next i
For i = 0 To 9
Cells(23 + i, 31) = Sqr((Cells(23 + i, 2) - Cells(22, 2)) ^ 2 + (Cells(23 + i, 3) - Cells(22, 3)) ^ 2)
Next i
For i = 0 To 8
Cells(24 + i, 32) = Sqr((Cells(24 + i, 2) - Cells(23, 2)) ^ 2 + (Cells(24 + i, 3) - Cells(23, 3)) ^ 2)
Next i
For i = 0 To 7
Cells(25 + i, 33) = Sqr((Cells(25 + i, 2) - Cells(24, 2)) ^ 2 + (Cells(25 + i, 3) - Cells(24, 3)) ^ 2)
Next i
For i = 0 To 6
Cells(26 + i, 34) = Sqr((Cells(26 + i, 2) - Cells(25, 2)) ^ 2 + (Cells(26 + i, 3) - Cells(25, 3)) ^ 2)
Next i
For i = 0 To 5
Cells(27 + i, 35) = Sqr((Cells(27 + i, 2) - Cells(26, 2)) ^ 2 + (Cells(27 + i, 3) - Cells(26, 3)) ^ 2)
Next i
For i = 0 To 4
Cells(28 + i, 36) = Sqr((Cells(28 + i, 2) - Cells(27, 2)) ^ 2 + (Cells(28 + i, 3) - Cells(27, 3)) ^ 2)
Next i
For i = 0 To 3
Cells(29 + i, 37) = Sqr((Cells(29 + i, 2) - Cells(28, 2)) ^ 2 + (Cells(29 + i, 3) - Cells(28, 3)) ^ 2) * UMF
Next i
For i = 0 To 2
Cells(30 + i, 38) = Sqr((Cells(30 + i, 2) - Cells(29, 2)) ^ 2 + (Cells(30 + i, 3) - Cells(29, 3)) ^ 2)
Next i
For i = 0 To 1
Cells(31 + i, 39) = Sqr((Cells(31 + i, 2) - Cells(30, 2)) ^ 2 + (Cells(31 + i, 3) - Cells(30, 3)) ^ 2)
Next i
For i = 0 To 0
Cells(32 + i, 40) = Sqr((Cells(32 + i, 2) - Cells(31, 2)) ^ 2 + (Cells(32 + i, 3) - Cells(31, 3)) ^ 2)
Next i
Das Ganze ist jedoch sehr umständlich. Mit der Zahlenfolge sollte jedoch eine viel kürzere Variante, z.B. in Form einer weiteren Schleife, möglich sein!
Ich komm jedoch leider nicht darauf wie sie funktioniert:/
Thx for your help :)
Cheers

Bild

Betrifft: AW: Eleganz mit Schleifen
von: Sepp
Geschrieben am: 08.06.2015 23:12:27
Hallo Klaus,
so?

Dim i As Long, n As Long

For i = 3 To 32
  For n = 2 To 31
    Cells(i, n + 9) = Sqr((Cells(i, 2) - Cells(n, 2)) ^ 2 + (Cells(i, 3) - Cells(n, 3)) ^ 2)
  Next
Next

Gruß Sepp


Bild

Betrifft: AW: Eleganz mit Schleifen
von: Klaus
Geschrieben am: 09.06.2015 16:46:11
Hey Sepp,
Super Danke

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Berechnung gewichteter Durchschnitt"