Hallo zusammen,
ich habe in einer Tabelle in Spalte A verschiedene (teilweise öfters vorkommende) Namen stehen, die sich ständig erweitern.
In Spalte B soll für jeden Namen eine Zahl stehen, bei gleichen Namen dann auch die gleiche Zahl:
A1: "Hugo" B1: 1300
A2: "Erik" B2: 1503
A3: "Thomas" B3: 1651
A4: "Erik" B4: 1503
A5: "Thomas" B5: 1651
...
Es kommen wie gesagt immer wieder neue Zeilen (neue und bereits vorhandene Namen) dazu, was auch gleichzeitig mein Problem ist, ansonsten wärs für mich kein Problem.
Ich würde nun gerne Folgendes machen, weiss ober nicht, wie das am Besten bewerkstelligen kann:
Per VBA sollen die Zahlen für die bereits vorhandenen Namen geändert/aktualisiert werden.
Ich gebe den Namen ein, dessen Zahl geändert werden soll und die neue Zahl und es werden für diesen Namen alle Zahlen auf die neue geändert.
Ich hoffe ich habe mich einigermaßen verständlich ausgedrückt.
Vielen Dank für Eure Hilfen !
Gruß
vom Winni
Hi Winni,
muss es denn unbedingt VBA sein? Schau dir mal die Fkt. SVERWEIS an. Du legst dir ein Tabellenblatt mit den Namen (ohne Duplikate) und den dazugehörigen Nummern an und holst dir die Daten von dort.
Tabelle1 | A | B | C | D | E | F | G | H | I | J |
1 | Hugo | 1300 | | | | | | | Jonas | 1100 |
2 | Thomas | 1500 | | | | | | | Sarah | 1200 |
3 | Thomas | 1500 | | | | | | | Hugo | 1300 |
4 | Lukas | 1600 | | | | | | | Hans | 1400 |
5 | Hugo | 1300 | | | | | | | Thomas | 1500 |
6 | Hans | 1400 | | | | | | | Lukas | 1600 |
7 | Lukas | 1600 | | | | | | | | |
8 | Hugo | 1300 | | | | | | | | |
9 | | #NV | | | | | | | | |
10 | | #NV | | | | | | | | |
11 | | #NV | | | | | | | | |
Formeln der Tabelle |
Zelle | Formel | B1 | =SVERWEIS(A1;$I$1:$J$6;2;0) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Deine Referenztabelle kannst du auch, so wie in meinem Beispiel, in einen nicht sichtbaren Bereich legen. Die #NV-Meldungen eliminierst du, indem du um die SVERWEIS-Formel noch ein WENNFEHLER legst:
Code:
=WENNFEHLER(SVERWEIS(A1;$I$1:$J$6;2;0);"")
(17.08.2016, 05:29)WillWissen schrieb: [ -> ]Hi Winni,
muss es denn unbedingt VBA sein? Schau dir mal die Fkt. SVERWEIS an. Du legst dir ein Tabellenblatt mit den Namen (ohne Duplikate) und den dazugehörigen Nummern an und holst dir die Daten von dort.
Tabelle1
| A | B | C | D | E | F | G | H | I | J |
1 | Hugo | 1300 | | | | | | | Jonas | 1100 |
2 | Thomas | 1500 | | | | | | | Sarah | 1200 |
3 | Thomas | 1500 | | | | | | | Hugo | 1300 |
4 | Lukas | 1600 | | | | | | | Hans | 1400 |
5 | Hugo | 1300 | | | | | | | Thomas | 1500 |
6 | Hans | 1400 | | | | | | | Lukas | 1600 |
7 | Lukas | 1600 | | | | | | | | |
8 | Hugo | 1300 | | | | | | | | |
9 | | #NV | | | | | | | | |
10 | | #NV | | | | | | | | |
11 | | #NV | | | | | | | | |
Formeln der Tabelle |
Zelle | Formel | B1 | =SVERWEIS(A1;$I$1:$J$6;2;0) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Deine Referenztabelle kannst du auch, so wie in meinem Beispiel, in einen nicht sichtbaren Bereich legen. Die #NV-Meldungen eliminierst du, indem du um die SVERWEIS-Formel noch ein WENNFEHLER legst:
Code:
=WENNFEHLER(SVERWEIS(A1;$I$1:$J$6;2;0);"")
Hi Will
,
Danke für Deine Antwort,
Der Aufbau meiner Tabelle lässt Deine Idee vermutlich nicht zu:
Ich habe in Tabellenblatt 1 eine sich ständig erweiternde Liste mit Personen (Spalte A). In Spalte B steht für jede Person eine bestimmte Zahl (gleiche Person = gleiche Zahl). Es kommen ständig sowohl bereits vorhandene Personen, als auch neue hinzu.
Mit einer Matrixfunktion habe ich die doppelten Personen "eliminiert" und somit in einer anderen Spalte (Spalte D) des Tabellenblattes alle Personen nur ein mal stehen. Mit SVERWEIS könnte ich auch leicht, die dazuehörige Zahl in Spalte E schreiben.
Das Problem ist, dass sich die Zahlen mind. jedes Quartal ändern !
Und jetzt suche ich nach einer Möglichkeit, wie ich die Zahlen am einfachsten jedes Qurtal für best. Personen (die ja auch mehrfach vorkommen) ändern kann, ohne die ganze Spalte B händisch mit den neuen Zahlen zu füllen.
Und da dachte ich halt an VBA:
Eingabe: Name
Eingabe: Neue Zahl
Funktion: Excel sucht in Spalte A nach dem jeweiligen Namen und ersetzt für diesen Namen in Spalte B die alten Zahlen mit der neuen Zahl.
Oder gibts vielleicht doch ne andere Lösung ?
Gruß
vom Winni
Hallo Winni,
mal als Test. Schau mal ob Du damit arbeiten könntest:
Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G |
1 | Hugo | 2500 | | | | Thomas | 2100 |
2 | Thomas | 2100 | | | | | |
3 | Thomas | 2100 | | | | | |
4 | Lukas | 1600 | | | | | |
5 | Hugo | 2500 | | | | | |
6 | Hans | 1400 | | | | | |
7 | Lukas | 1600 | | | | | |
8 | Hugo | 2500 | | | | | |
9 | Jonas | 1100 | | | | | |
10 | Sarah | 1200 | | | | | |
11 | Hugo | 2500 | | | | | |
12 | Hans | 1400 | | | | | |
13 | Thomas | 2100 | | | | | |
14 | Lukas | 1600 | | | | | |
In ein Modul packst Du den folgenden Code
Code:
Sub Suchen()
Dim i As Long
Dim Treffer As Range
Dim strBegriff As String
With Tabelle1
strBegriff = .Range("F1").Value
Set Treffer = Range("A1")
For i = 1 To WorksheetFunction.CountIf(Columns(1), strBegriff)
Set Treffer = Columns(1).Find(What:=strBegriff, After:=Treffer, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not Treffer Is Nothing Then
Treffer.Offset(0, 1).Value = .Range("G1").Value
Treffer.Interior.ColorIndex = 6
End If
Next i
End With
End Sub
In F1 gibst Du den zu suchenden Namen ein. In G1 den Wert.
Das Makro sucht nach dem Namen und schreibt hinter alle Treffer den Wert aus G1.
Das Einfärben der Treffer (Treffer.Interior.ColorIndex = 6) habe ich nur zu Testzwecken eingebaut.
Die Codezeile ist überflüssig.
Gruß
Ich
Danke für diesen Code, ja genau das möchte ich machen, leider kommt beim Ausführen des Makros die Fehlermeldung: "Typen unverträglich"
Hallo Winni,
also bei mir lief das^^.
Das ist das Problem für uns Helfer wenn keine Beispieldatei zur Hand ist :32:.
Gruß
Ich
(17.08.2016, 10:54)IchBinIch schrieb: [ -> ]Hallo Winni,
mal als Test. Schau mal ob Du damit arbeiten könntest:
Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G |
1 | Hugo | 2500 | | | | Thomas | 2100 |
2 | Thomas | 2100 | | | | | |
3 | Thomas | 2100 | | | | | |
4 | Lukas | 1600 | | | | | |
5 | Hugo | 2500 | | | | | |
6 | Hans | 1400 | | | | | |
7 | Lukas | 1600 | | | | | |
8 | Hugo | 2500 | | | | | |
9 | Jonas | 1100 | | | | | |
10 | Sarah | 1200 | | | | | |
11 | Hugo | 2500 | | | | | |
12 | Hans | 1400 | | | | | |
13 | Thomas | 2100 | | | | | |
14 | Lukas | 1600 | | | | | |
In ein Modul packst Du den folgenden Code
Code:
Sub Suchen()
Dim i As Long
Dim Treffer As Range
Dim strBegriff As String
With Tabelle1
strBegriff = .Range("F1").Value
Set Treffer = Range("A1")
For i = 1 To WorksheetFunction.CountIf(Columns(1), strBegriff)
Set Treffer = Columns(1).Find(What:=strBegriff, After:=Treffer, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not Treffer Is Nothing Then
Treffer.Offset(0, 1).Value = .Range("G1").Value
Treffer.Interior.ColorIndex = 6
End If
Next i
End With
End Sub
In F1 gibst Du den zu suchenden Namen ein. In G1 den Wert.
Das Makro sucht nach dem Namen und schreibt hinter alle Treffer den Wert aus G1.
Das Einfärben der Treffer (Treffer.Interior.ColorIndex = 6) habe ich nur zu Testzwecken eingebaut.
Die Codezeile ist überflüssig.
Gruß
Ich
Wenn ich anstatt
Code:
With Sheets("Tabelle1")
schreibe verschwindet zwar die Fehlermeldung, es tut sich aber am Ergebnis nix ...
(17.08.2016, 14:24)IchBinIch schrieb: [ -> ]Hallo Winni,
also bei mir lief das^^.
Das ist das Problem für uns Helfer wenn keine Beispieldatei zur Hand ist :32:.
Gruß
Ich
OK Danke ! Wenns bei Dir lief muß ich hier bei mir auf Fehlersuche gehen !
Wie gesagt...
Bei mir lief's. Kannst Du gerne selbst testen.
[
attachment=6555]