Microsoft Excel

Herbers Excel/VBA-Archiv

VBA-Formel optimieren

Betrifft: VBA-Formel optimieren von: erichm
Geschrieben am: 20.11.2014 20:50:47

Hallo,

nach längerer Recherche habe ich für mein Problem noch keine Lösung gefunden. Jetzt habe ich mit meinen bescheidenen VBA-Kenntnissen einen Code entwickelt, der meine erwarteten Ergebnisse bringt.
ABER: die Formel muss ich für sehr viele Zeilen erweitern, weswegen ich eine Optimierung benötige, aber alle meine Versuche sind gescheitert.
Tabelle1

 ABCDEFGHIJKLMNOPQRSTUVW
1Range A  Range B Zeile 2Zeile 3
2123456789101112  5678910 66
3357681091113121416  7911131415 35
4581181114111417141720  91215181920 23


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Es geht darum, dass die Werte von Range A mit den Werten mit Range B wie folgt verglichen werden:
Zeile 2 mit Zeile 2
Zeile 2 mit Zeile 3
Zeile 2 mit Zeile 4
usw....
--> das Ergebnis kommt in Spalte V ab Zeile 2

dann
Zeile 3 mit Zeile 2
Zeile 3 mit Zeile 3
Zeile 3 mit Zeile 4
usw....
--> das Ergebnis kommt in Spalte W ab Zeile 2

Dafür habe ich diesen Code:
Sub zählen1()
'Zählen Zeile 2 aus Range A mit Zeilen aus Range B
Worksheets(4).Cells(2, 22) = Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("O2"))  _
+ _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("P2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("Q2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("R2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("S2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("T2"))
 
Worksheets(4).Cells(3, 22) = Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("O3"))  _
+ _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("P3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("Q3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("R3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("S3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("T3"))
 
Worksheets(4).Cells(4, 22) = Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("O4"))  _
+ _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("P4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("Q4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("R4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("S4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A2:L2"), Range("T4"))

'Zählen Zeile 3 aus Range A mit Zeilen aus Range B
Worksheets(4).Cells(2, 23) = Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("O2"))  _
+ _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("P2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("Q2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("R2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("S2")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("T2"))
                            
 Worksheets(4).Cells(3, 23) = Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("O3")) _
 + _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("P3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("Q3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("R3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("S3")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("T3"))
                            
Worksheets(4).Cells(4, 23) = Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("O4"))  _
+ _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("P4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("Q4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("R4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("S4")) + _
 _
                            Application.WorksheetFunction.CountIf(Range("A3:L3"), Range("T2"))
End Sub
Und diesen müsste ich jetzt für die vielen Zeilen nach unten fortschreiben; aber da gibts bestimmt eine andere, bessere Lösung.

Der Einfachheit halber, habe ich Range A und Range B in der gleichen Tabelle; in der Endfassung ist dann Range B in einer anderen Tabelle wie Range A. Aber das müsste dann anpassbar sein.

Besten Dank für eine Hilfe.
mfg

  

Betrifft: AW: VBA-Formel optimieren von: Daniel
Geschrieben am: 21.11.2014 10:04:35

Hi
schreibe doch die Formeln in die Zelle.
mit Hilfe der Absoluten und relativen Addressierung und der Zeilenauswahl über INDEX mit Hilfe der SPALTE-Funktion kommt in jede Zelle dieselbe Formel und die kannst du dann in alle Zellen gleichzeitig schreiben:

x = Zeilennummer der letzen befüllten Spalte
With Range("V2:W" & x)
    .Formula = "=CountIf(Index($A:$L;Column(B$1)),$O2)+CountIf(Index($A:$L;Column(B$1)),$P2)+... _
+CountIf(Index($A:$L;Column(B$1)),$T2)"
End With
Gruß Daniel


  

Betrifft: AW: VBA-Formel optimieren von: erichm
Geschrieben am: 21.11.2014 18:57:22

Danke; probiere ich und melde mich.