Anpassung des Codes für eine VBA-Formel

Question

Hallo,

ich habe in diesem Beitrag eine Teillösung für mein Problem gefunden:
www.supportnet.de/fresh/2008/2/id1687622.asp

1. Frage:
Wenn ich die alternative Lösung

oder "verschachtelt":

oWS.Cells(lZeilenzaehler, iSpalteFormel).Formula = "=COUNTIF(" & _
Range(Cells(lZeilenzaehler, iSpalteBeginn), Cells(lZeilenzaehler, iSpalteEnde)).AddressLocal & _
","">6"")"
verwende, erhalte ich die Fehlermeldung:
Laufzeitfehler91; oWS habe ich As Range definiert;
was muss ich noch anpassen?

2. Frage:
Wie kann ich die Prüfung "">6"" so ändern, dass der Inhalt der Zelle O2 (= eine Zahl) gezählt wird.

3. Frage:
Abschließend würde ich gerne das Ergebnis aus dem Makro in die Zellen eintragen und nicht die Formel.

Besten Dank!
mfg

PS: Ich habe mich versucht zu registrieren, aber das hat leider (noch) nicht geklappt.

Answer 1

Hallo,

lass das oWS weg ;-).

Willst du gleich das Ergebnis eintragen, dann verwende

Cells(lZeilenzaehler, iSpalteFormel) = WorksheetFunction.CountIf(Range(Cells(lZeilenzaehler, iSpalteBeginn), Cells(lZeilenzaehler, iSpalteEnde)), Range("O2").Value)

Hier wird der Inhalt der Zelle O2 gezählt.

Und zurzeit funktioniert die Registrierung hier nicht.

Gruß

M.O.

Answer 2

DANKE - das klappt!

Jetzt habe ich festgestellt, dass ich bei der Schleife noch einen Denkfehler hatte. Die muss ich noch anpassen; Grundlage, Ziel:

i58.tinypic.com/1iz9jd.jpg

Es geht darum, dass die Werte der Bestandsdaten A mit den Werten der Prüfdaten 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

Und diese muss ich jetzt für viele Zeilen (ca. 5.300) nach unten fortschreiben; ebenso nach rechts die Spalten (ca. 200).

Der Einfachheit halber, habe ich Bestandsdaten A und Prüfdaten B in der gleichen Tabelle; in der Endfassung sind dann Prüfdaten B in einer anderen Tabelle wie Bestandsdaten A (in dieser Tabelle werden auch die Ergebnisse eingetragen). Aber das müsste dann anpassbar sein.

Aus Ressourcengründen kann ich keine Matrixformel mit ZÄHLENWENN verwenden; die habe ich in dem Beispiel zur Ermittlung der richtigen Ergebnisse benutzt.

Aktuell sieht mein Code so aus:

Public Sub testzählen2()
Dim lzeilenzaehler As Integer, lspaltenzaehler As Integer, i As Integer
Dim ispalteformel As Integer, iSpalteBeginn As Integer, iSpalteEnde As Integer
Dim strSpalteBeginn As String, strSpalteEnde As String
lzeilenzaehler = 2
ispalteformel = 22
iSpalteBeginn = 1
iSpalteEnde = 12
strSpalteBeginn = 1
strSpalteEnde = 12
strSpalteBeginn = Left(Cells(1, iSpalteBeginn).Address(True, False), _
InStr(Cells(1, iSpalteBeginn).Address(True, False), "$") - 1)

strSpalteEnde = Left(Cells(1, iSpalteEnde).Address(True, False), _
InStr(Cells(1, iSpalteEnde).Address(True, False), "$") - 1)

For lzeilenzaehler = 2 To 10
Cells(lzeilenzaehler, ispalteformel) = WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("O2").Value) + _
WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("P2").Value) + _
WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("Q2").Value) + _
WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("R2").Value) + _
WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("S2").Value) + _
WorksheetFunction.CountIf(Range(Cells(lzeilenzaehler, iSpalteBeginn), Cells(lzeilenzaehler, iSpalteEnde)), Range("T2").Value)

Next
End Sub

Das Makro rechnet jetzt natürlich falsch, weil die beiden "Variablen"
lzeilenzaehler
Range("O2").Value usw.
angepasst werden müssen. Aber da reichen meine VBA-Kenntnisse nicht mehr.

Besten Dank für eine weitere Hilfe!
mfg
excel2

Answer 3

Hallo,

statt eines Bildes wäre eine Beispieldatei besser gewesen ;-).

Versuch es mal mit folgendem Makro:

Sub zaehlenwenn()

Dim zeileB As Long
Dim zeileP As Long
Dim spalte As Long
Dim ergeb As Long
Dim zspalte As Long

zspalte = 22 'Variable für Spalte, in die die Ergebnisse der Zählenwenn-Durchläufe geschrieben werden sollen

For zeileP = 2 To 10 'Zeilen der Prüfdaten die durchlaufen werden

For zeileB = 2 To 10 'Zeilen der Bestandsdaten die durchlaufen werden sollen

ergeb = 0 'Variable für Aufsummierung der Zählenwenns wird auf Null gesetzt

'Schleife für Zählenwenn; Spalten für Prüfdaten: O bis T
For spalte = 15 To 20
ergeb = ergeb + WorksheetFunction.CountIf(Range(Cells(zeileB, 1), Cells(zeileB, 12)), Cells(zeileP, spalte).Value) 'Ergebnisse der Zählenwenns werden in Variable geschrieben
Next spalte

'Überschriften für Ergebnis
If zeileP = 2 Then Cells(1, zspalte) = "Zeile " & zeileB

'Das Ergebnis wird in Tabelle geschrieben
Cells(zeileP, zspalte) = ergeb
'Spalte für Ergebnis wird erhöht
zspalte = zspalte + 1

Next zeileB

'Spalte für Ergebnis wird wieder zurückgesetzt
zspalte = 22

Next zeileP

End Sub

Aus Ressourcengründen kann ich keine Matrixformel mit ZÄHLENWENN verwenden; die habe ich in dem Beispiel zur Ermittlung der richtigen Ergebnisse benutzt.

Dann überprüfe bitte mal deine Formel, denn deine Ergebnisse sind nur für die erste Zeile richtig (falls ich dich richtig verstanden habe).

Gruß

M.O.

Answer 4

Danke schon mal; habe heute leider keine Zeit und melde mich morgen oder übermorgen wieder!

mfg

Answer 5

Hallo,

also der Code funktioniert wunderbar!

BESTEN DANK!

Danke für den Hinweis bei der Formel, da war für die Zeile 3 ein Fehler drin.

Hochladen EXCEL-Datei als Beispieldatei ging leider nicht.

PROBLEM GELÖST !

mfg