Microsoft Excel

Herbers Excel/VBA-Archiv

WorksheetFunction blattübergreifend

Betrifft: WorksheetFunction blattübergreifend von: Karl
Geschrieben am: 14.10.2020 09:42:34

Hallo liebe Forumuser,

Ich hoffe jemand kann mir helfen.
Trotz zahlreichen Recherchen weiß ich immer nochnicht, inwiefern dieser Code falsch ist:

Tabelle.Cells(i + 1, 3).Value = Application.WorksheetFunction.CountIf(.Range("Sheet1_STRING!A:A"), Str(Cells(i + 1, 2).Value))

"Tabelle" ist ein neues Tabellenblatt,
"i" ist eine Zählvariable in einer Schleife,
"Sheet1_STRING" ist ebenfalls ein Tabellenblatt

was ich umsetzen möchte:

for i = 1 To 10
Inhalt der Zelle B i in "Tabelle"
=ZÄHLENWENN( Sheet_STRING!A:A; Tabelle!A i)

in Worten:

In Spalte A im neuen Blatt ("Tabelle") stehen Begriffe, die ich im alten Blatt ("Sheet1_STRING) in der Spalte A mithilfe der Funktion "=ZÄHLENWENN()" zählen möchte.
VBA soll die Formeln automatisch mit einer Zählschleife ergänzen und dabei die Ausgabezelle und die Zelle mit Suchwort anpassen.
Vielen Dank schonmal im Voraus!!

Gruß, Karl

Betrifft: AW: WorksheetFunction blattübergreifend
von: Karl
Geschrieben am: 14.10.2020 09:44:38

ups, Formel falsch angepasst:

Tabelle.Cells(i + 1, 2).Value = Application.WorksheetFunction.CountIf(.Range("Sheet1_STRING!A:A"), Str(Cells(i + 1, 1).Value))

Gruß, Karl

Betrifft: AW: WorksheetFunction blattübergreifend
von: Nepumuk
Geschrieben am: 14.10.2020 09:53:07

Hallo Karl,

teste mal:

Tabelle.Cells(i + 1, 2).Value = WorksheetFunction.CountIf( _
    Worksheets("Sheet1_STRING").Columns(1), Tabelle.Cells(i + 1, 1).Value)

Gruß
Nepumuk

Betrifft: AW: WorksheetFunction blattübergreifend
von: Karl
Geschrieben am: 14.10.2020 10:01:04

Hallo Nepumuk,

Vielen Dank für die rasche Antowrt! Deine Formel funktioniert einbahnfrei!!

Gruß, Karl

Betrifft: AW: WorksheetFunction blattübergreifend
von: Daniel
Geschrieben am: 14.10.2020 10:14:57

Hi

Meiner Ansicht nach muss der Punkt vor dem Range weg, wenn du das Tabellenblatt in der Range-Adresse mit angibst.
Alternativ wie bereits gezeigt.
Was macht die Str-Funktion? Du kannst die Zelle dort direkt angeben.

Generell besser wäre es jedoch, hier die Formeln in die Zellen zu schreiben.
Das geht schneller, weil du keine Schleife brauchst sondern die Formel in alle Zellen in einem Schritt schreiben kannst.

Gruß Daniel

Betrifft: AW: WorksheetFunction blattübergreifend
von: Karl
Geschrieben am: 14.10.2020 10:47:24

Hallo,

Gut, meine erste Frage wurde geklärt, jedoch habe ich ein weiteres Problem:
@Daniel, Ich kann die Formel nicht wirklich manuell aufschreiben, da es ein zu großer Aufwand wäre, bei dieser Datenmenge.

in der Formel
Tabelle.Cells(i + 1, 2).Value = WorksheetFunction.CountIf( _
Worksheets("Sheet1_STRING").Columns(1), Tabelle.Cells(i + 1, 1).Value)

von Nepumuk
wird genau die Spalte A abgesucht, so wie ich es wollte.

Wie ist das jedoch aufgebaut, wenn ich als Range 2 Zellen angeben möchte?, also wie:

Range = "A" & str(Cells(i, 11).Value) & ":A" & str(Cells(i + 1, 11).Value)

bzw.

Range = A(Ki):A(Ki+2)

Gruß, Karl

Betrifft: AW: WorksheetFunction blattübergreifend
von: Daniel
Geschrieben am: 14.10.2020 10:57:54

Hi

Ob du die Berechnung in VBA furchführst oder in einer Formel auf dem Blatt, macht keinen Unterschied. VBA ist genauso schnell, wenn gleiche Funktionen verwendet werden.
Es ist aber zeitaufwendiger, Zellen einzeln zu befüllen als viele Zellen gleichzeitig in einem Schritt.

Zu deiner Frage:
Hier ein paae Möglichkeiten

Range("A" & i & ":A" & i + 2)
Range(Cells(i, "A"), Cells(i + 2, "A"))
Range(Cells(i, 1), Cells(i + 2, 1))
Cells(i, 1).Resize(3)

Gruß Daniel

Betrifft: AW: WorksheetFunction blattübergreifend
von: Karl
Geschrieben am: 14.10.2020 11:04:45

Hi Daniel,

Danke für deine AW. Sollte funktionieren, jedoch habe ich meine Frage ungenau gestellt.
Wie kann ich in dieser Formel zusätzlich "Sheet1" oder so integreiren? Also:

Range(Sheet1!"A" & i & ":A" & i + 2)

oder wie?

Gruß, Karl

Betrifft: AW: WorksheetFunction blattübergreifend
von: Daniel
Geschrieben am: 14.10.2020 11:16:50

Hi

Naja wenn du das Sheet in der Rangeadresse haben willst, dann muss es Teil des Textes sein und innerhalb der Anführungszeichen stehen.
Range("Sheet1!A" & i & ":A" & i + 2)
Alternativ aber besser als eigenständiges Objekt und in der Range nur die reine Adresse der Zellen:
Sheets("Sheet1").Range("A" & i & ":A" & i + 2)
Wenn du die Range(Cells(), Cells()) - Variante wählst, muss das Sheet vor beiden Cells stehen:
Range(Sheets("Sheet1").Cells(), Sheets("Sheet1").Cells())
Gruß Daniel

Betrifft: AW: WorksheetFunction blattübergreifend
von: Karl
Geschrieben am: 14.10.2020 11:18:36

Hallo Daniel,

Ok, Vielen Dank! Funktioniert prima!

Gruß, Karl