PDA

Vollständige Version anzeigen : Werte in Tabellenblatt zählen wenn Name in Spalte C


krampi
16.02.2008, 11:28
Hallo Excel-Gemeinde,

bin mal wieder mit einer Frage hier.
Es gibt ein kleines Problem, dass ich, der einfachen Bedienung halber, mit VBA lösen möchte und wo ich keine Formeln zu fand.
Folgendes Problem:
Ich habe ein Tabellenblatt "states" und einen mit dem Namen "regions". In "regions" findet man verschiedenste Regionen, die einem Staat zugeordnet sind (die Staatszugehörigkeit kann sich aber im Laufe der Zeit ändern).
Nun soll über ein Makro für jeden Staat in "state" geschaut werden, welche Region in "region" laut Spalte C zum jeweiligen Staat gehört. Wenn also z.B. NRW zu Deutschland gehört, so soll das Makro den Wert aus der Spalte D einlesen und weitersuchen, bis alles aufgenommen ist. Dabei könnte man, wenn man es dadurch vereinfacht, ja schauen, ob man auch wirklich alle Regionen (die über eine ZählenWenn-Funktion gezählt wurden) erwischt hat.
In der jeweiligen Zelle sollen halt die Werte der ganzen Regionen addiert werden. Und das Makro würde ich dann für die anderen Staaten anwenden.
Hoffe, ich konnte mich gut ausdrücken.

Gruß,
krampi

P.S.: Vielen dank im Vorraus, auch für Versuche. :)

jinx
16.02.2008, 16:01
<font size="2" face="Century Gothic">Moin, krampi,

und woher weiß das Makro, ob es alle Vorkommen erwischt hat? Wie wäre es mit einem Anhang zur Verdeutlichung - mir ist gerade nicht nach dem Erstellen eines Modells, wenn es Dir vorliegen sollte.</font>

krampi
17.02.2008, 14:36
Hi Jinx,

das ursprüngliche Problem wurde gelöst (stolz bin). Aber ein ähnliches ist aufgetreten, dass sich jedoch nicht auf Zahlen bezieht. Ich habe in der data bei der Mappe regions verschiedene Regionen mit regionalen Hauptstädten. Daneben existiert eine Spalte mit dem Begriff Hauptstadt. Die Zellen dadrunter sind entweder mit Ja oder Nein ausgefüllt. Nun ist folgendes das Problem. Für beispielsweise den Jemen möchte ich die Hauptstadt ermitteln. Also sollen per VBA am besten die Regionen abgesucht werden. Dabei wird zuerst in der Spalte C von regions geschaut, zu welchem Staat welche Region gehört. Dabei sollte das Makro bis zur letzten Zelle schauen. Dann wird in jeder Zeile, die eine Region besitzt, die dem Jemen angehört, die regions-Spalte G ("Hauptstadt" mit Ja oder Nein) abgesucht. Wenn es ein Ja gibt, wird der Eintrag aus der Spalte F (die regionale Hauptstadt) als Hauptstadt des Jemen geführt. Wenn es zweimal Ja gibt,sollte Fehler angezeigt werden.
Hoffe, du konntest verstehen, wie ich es meine.
Gruß,
krampi

jinx
17.02.2008, 17:48
Moin, krampi,

Ansatz siehe Makro bzw. Anhang:

Private Sub FindUniqueItems(UniqueItems As Variant, FilterRange As String)
' returns a list containing all unique items in the filter range
Dim TempList() As String, UniqueCount As Integer, cl As Range, i As Integer
Range(FilterRange).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
UniqueCount = Range(FilterRange).SpecialCells(xlCellTypeVisible).Count
ReDim TempList(1 To UniqueCount - 1)
i = 0
For Each cl In Range(FilterRange).SpecialCells(xlCellTypeVisible)
i = i + 1
If i > 1 Then TempList(i - 1) = cl.Formula ' ignore the heading
Next cl
Set cl = Nothing
UniqueItems = TempList
End Sub

'===

Sub FilterToCheck()
'Idee: ERLANDSEN DATA CONSULTING, http://www.erlandsendata.no/downloads/filterandprint.zip
Dim ItemList As Variant
Dim i As Integer
Dim rngCapital As Range
Dim rngcell As Range
Dim strAngaben As String
Dim lngCounter As Long

Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
FindUniqueItems ItemList, "Staaten"

Range("Staaten").AutoFilter
For i = 1 To UBound(ItemList)
Range("Staaten").AutoFilter 1, ItemList(i)
Set rngCapital = Range("G4:G" & Cells(Rows.Count, 7).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells
For Each rngcell In rngCapital
If rngcell.Value = "Ja" Then
strAngaben = strAngaben & rngcell.Address & vbCrLf
lngCounter = lngCounter + 1
End If
Next rngcell
If lngCounter > 1 Then
MsgBox "Mehrfache Fundstellen für " & ItemList(i) & vbCrLf & strAngaben, vbInformation
End If
lngCounter = 0
strAngaben = ""
Next i

Application.ScreenUpdating = True
With ActiveSheet
.ShowAllData
.AutoFilter
End With

End Sub

krampi
18.02.2008, 21:06
Hallo Jinx,

danke für die Antwort. Ich muss jedoch zugeben, dass ich den VBA-Code nicht wirklich verstehe. Ich weiß, dass es ein Ansatz ist, aber ich will auch den Ansatz verstehen.

Andererseits, sollte ich den Code doch verstanden haben, hast du verstanden, was mein Problem ist? :D
Bitte versteh mich nicht falsch. Ich möchte nicht unhöflich sein.

Gruß,

krampi

jinx
19.02.2008, 05:59
<font size="2" face="Century Gothic">Moin, krampi,

ein löblicher Vorsatz - aber was spricht dagegen, den Zellzeiger in die Prozedur FilterToCheck zu stellen und per F8 mit einer Sicht aus der VBE auf die Tabelle den Code zu durchlaufen? Ich kenne kein Argument, was dagegen spricht - und es gibt nichts Besseres, um sich selbst die Funktionsweise eines Codes zu erarbeiten...

Wenn Du den Code verstanden hättest, wußtest Du, dass Du mit der Variablen lngCounter die Anzahl des Vorkommens von ja erhältst. Und die 3 meiner Meinung nach relevanten Zustände kann man in einer einfachen Select Case Anweisung auswerten: 0, 1 und mehr als 1. Darüber hinaus enthält die Variable strAngaben entweder nichts (keine Fundstelle) oder die Adresse(n) der Fundstelle(n) und einen Zeilenumbruch (den kann man bei einer Fundstelle durch die Textfunktion Left sowie durch die Länge des Strings abzüglich der Zehl des/der Zeichen für den Zeilenumbruch abschneiden). Und dann kann man bei einer Fundstelle Offset auf den so erhaltenen Range einsetzen, um den Wert umzuschreiben.

Nein, ich habe das Problem nicht erkannt - eine Lösung für die dargestellte Aufgabenstellung kann ich auch ohne Verständnis dessen erstellen...</font>

krampi
25.02.2008, 13:55
Also, ich habe einen Code geschrieben - nach reichlicher Recherche. Den wollte ich zwar ausprobieren. Allerdings erzählt mir VBA dann, dass die Typen unverträglich sind. Kurz: ich habe unter VBA den Code für Tabelle1 ("states") geschrieben. Demnach nicht als Modus. Vllt. mag dies was heißen.

Option Explicit

Sub suchmich()

Dim wstates As Worksheet
Dim fundstates As Range 'Fund in wstates
Dim wsregions As Worksheet
Dim fundregions As Range 'Fund in wsregions
Dim lngCLastR As Long 'Letzte zu kopierende Zeile
Dim strSuche As String 'Suchbegriff
Dim strSuche2 As String 'Suchbegriff 2



'letzte befüllte Zelle innerhalb der Spalte C ermitteln
LetzteZeile = Worksheets("regions").Cells(Rows.Count, 3).End(xlUp).Row

'Das sheet, in das eingetragen werden soll:
Set wstates = Worksheets("states")

'Das Sheet, in dem auch gesucht wird:
Set wsregions = Worksheets("regions").Range("C4:C" & LetzteZeile)

'Suchbegriff
strSuche = "Ja"
strSuche2 = Worksheets("states").Range("A2")


'Suchen in wstates:
Set fundregions = wsregions.Cells.Find(what:=strSuche, lookat:=xlWhole)
Set fundregions = wsregions.Cells.Find(what:=strSuche2, lookat:=x1Whole)


If Not fundstates Is Nothing Then
'einfügen
fundstates = Worksheets("states").Range("G2")



End If

'Variablen wieder freigeben:
Set fundstates = Nothing
Set fundregions = Nothing
Set wstates = Nothing
Set wsregions = Nothing

End Sub


Ich gehe davon aus, dass ich da nur einen kleinen Fehler gemacht habe. Aber welcher ist das? So weit müsste - gehe ich ja von aus - der Code richtig sein.

Gruß,
krampi

jinx
25.02.2008, 18:26
<font size="2" face="Century Gothic">Moin, krampi,

wsregions ist ein Worksheet, dem zusätzlich ein Range zugeordnet werden soll - das läuft so nicht und muss aufgeteilt werden:

'Das Sheet, in dem auch gesucht wird:
Set wsregions = Worksheets("regions")

'Der Bereich, in dem gesucht wird
Set rngregions = wsregions.Range("C4:C" & LetzteZeile)
Weshalb Du die Suche dann doppelt durchführen lässt aber nur einen Bereich für die Fundstelln hast, verstehe ich nicht:
'Suchen in wstates:
Set fundregions = wsregions.Cells.Find(what:=strSuche, lookat:=xlWhole)
Set fundregions = wsregions.Cells.Find(what:=strSuche2, lookat:=xlWhole)
(Tippfehler bei xlWhole - es muss xl heißen und nicht x1).

So ist dann die Variable fundstates immer nothing, weil nirgends gesetzt...</font>

krampi
25.02.2008, 18:46
Also, der Gedanke ist, dass das Makro in "regions" jede Zeile nach dem Namen des Staates (in Spalte C, ist strSuche2) und dem Wort "Ja" (in Spalte G, ist strSuche) durchsucht. Wenn dies passiert, soll sich das Makro den Wert aus Spalte F schnappen und unter "states" in Spalte G als Hauptstadt eines Staates eintragen.
Ist sozusagen wie ein Roboter, der in einem Versandhauslager eine Bestellung aus Büchern und DVDs zusammenstellt. Zuerst sucht er das Regal "Bücher" und schnappt sich beispielsweise "VBA mit Excel", legt es in den Bestellkarton so weiter.
Kann dieses Problem daran liegen? Ich weiß, dass mein Code noch nicht ganz fertig ist. Bei der Finalisierung soll das Makro den jeweiligen Wert ja auch raussuchen. Das kommt noch.

jinx
25.02.2008, 19:34
<font size="2" face="Century Gothic">Moin, krampi,

ein Worlsheet ist immer die gesamte Tabelle, und ein Range ist ein (Teil-)Bereich davon. Bei Deinem Code müsste analog zu meinen Anmerkungen bei Suchen/Finden der Suchbereich verändert werden:

Set fundregions = rngregions.Cells.Find(what:=strSuche, lookat:=xlWhole)
Das Problem scheint mir aber das ursrüngliche zu sein, das im ersten Posting dargelegt wurde... ;) </font>