sanjao
Goto Top

Excel VBA Sverweis nach klick auf Button

Hallo,

ich habe leider so gut wie gar keine Ahnung von Excel VBA muss aber ein Makro bis ende nächster Woche fertig stellen. Hab mir ein Buch "Excel VBA" von Bernd Held geholt und auch schon einiges gelesen. Leider wird die Zeit knapp und ich habe bis jetzt nicht das gefunden was ich benötige. Daher hoffe ich hier auf Hilfe.

Also ich arbeite mit Microsoft Excel 2007 und möchte folgendes mit einem Makro realisieren.

Ich habe eine Excel-Liste mit Kundendaten, nächste Woche haben wir eine Hausmesse und es soll notiert werden welche Kunden da waren und mit wie viel Personen. Die Hausmesse geht über 2 Tage.

Was ich mir jetzt gedacht habe:

In ein Feld wird die Kundennummer eingetragen so werden alle benötigten Daten zum Kunden per SVERWEIS ausgegeben.

In ein anderes Feld soll die Anzahl der Personen eingetrgen werden, die der Kunde mitbringt.

Es soll jetzt einen Button geben, der ein Makro aufruft, welches die bereits eingegebene Kundennummer nimmt und diese in der Matrix sucht und einige Zellen weiter rechts die Anzahl der Personen ausgibt.

Könnte mir da jemand helfen oder einige Infos geben?

Danke sehr.

Content-Key: 135609

Url: https://administrator.de/contentid/135609

Ausgedruckt am: 28.03.2024 um 13:03 Uhr

Mitglied: simsons
simsons 10.02.2010 um 10:15:58 Uhr
Goto Top
Zelle A1 "Firma Müller"
Zelle B1 "2"
Zelle A2 "Firma Schmitz"
Zelle B2 "5"
Mitglied: Sanjao
Sanjao 10.02.2010 um 10:30:55 Uhr
Goto Top
Tut mir leid versteh nicht was du damit meinst.
Mitglied: TheEternalPhenom
TheEternalPhenom 10.02.2010 um 11:53:43 Uhr
Goto Top
Ich versuche mal simsons Vorschlag zu erklären.

Eigentlich ganz einfach.

Du nimmst die Tabelle mit den Firmennamen.

In Spalte A schreibst du die Firmennamen und in Spalte B die Anzahl der Besucher.


Und jetzt zu deiner Frage.

Sverweis kann dir zwar die Kundendaten anzeigen aber du wirst (meines Wissens) über Sverweis nichts an einer Datenbank ändern.

Du hast (aus meiner Sicht) zwei Möglichkeiten.

1. Du nutzt die Suchenfunktion (Strg+F) in Verbindung mit dem Vorschlag von simsons.
Diese Möglichkeit ist eigentlich am einfachsten.

2. a) Du schreibst ein VBA Makro mit UserFormen.

In die UserForm gibst du die Kundennummer ein und die Anzahl der Besuche. Nach dem Bestätigen sucht das Programm automatisch nach der Kundennummer und trägt in in die entsprechende Zeile die Anzahl an Besucher ein.

2. b) Das gleiche ohne UserForm einfach mit Inputboxen bzw. MsgBoxen.

Ich denke jedoch, dass deine Kenntnisse vllt. mit diesem Programm etwas überfordert werden face-wink

Ich finde die erste Möglichkeit so oder so am einfachsten.

Solltes du jedoch ein solches Programm benötigen kann ich schauen ob ich es noch bis dahin schaffe. Ich kann es jedoch nicht versprechen.

Gruß

duffman521
Mitglied: Sanjao
Sanjao 10.02.2010 um 12:31:20 Uhr
Goto Top
Erstmal vielen Dank für deine Antwort Duffman.
Es muss auf der Messe schnell gehen daher ist es wäre es schön wenn es automatisch gehen würde. Mit strg + f müsste man ja immer noch suchen, scrollen, tippen anschließend wieder scrollen.

Ich habe mal ein Bild auf Imageshack hochgeladen und versuche es mal kurz zu erklären was ich vorhabe. Vielleicht denke ich viel zu kompliziert und es geht um einiges einfacher.

http://img192.imageshack.us/img192/6546/messedatei.jpg

Also oben links wird die Kundennummer eingetragen. Per Sverweis wird dann das "umgraute" Fenster rechts ausgegeben. Danach wird auf drucken gedrückt und der "umgraute" Feld wird auf ein Etikett gedruckt. Währenddessen soll die Anzahl der Personen eingetragen werden, die den Kunden begleiten. Dann soll auf den Button "zählen Samstag" geklickt werden. Es würde auch reichen wenn das Feld Kundennummer und das Feld Personen einfach kopiert werden. Also wie man das unten rechts auf dem Bild sieht. Dazu müsste aber beim nächsten Klicken der neue Wert eine Zeile tiefer eingetragen werden.

Das mit UserForm hab ich mir auch schon gedacht und hab versucht mich etwas schlau zu machen. Doch leider ist es etwas schwer zu verstehen wenn viel vorwissen fehlt.
An die Inputbox habe ich auch gedacht, aber funktioniert dann noch mein sverweis für das "umgraute" Feld?

Erst mal vielen Danke bis hier her.
Mitglied: Sanjao
Sanjao 10.02.2010 um 14:58:10 Uhr
Goto Top
Ich habe jetzt folgendes getestet:

Sub Makro1()

Range("D6").Select
Selection.Cut
Range("d17").Select
ActiveSheet.Paste

Range("D3").Select
Selection.Copy
Range("E17").Select
ActiveSheet.Paste

End Sub

Das ist ja schon mal gut. Jetzt soll aber beim nächsten Aufruf nicht die Zelle D3 und E17 überschrieben werden sondern, eine Zeile tiefer eingefügt werden. Geht das vllt mit einer if abfrage? Falls Zell besetzt nehme die Nächste.
Mitglied: 76109
76109 10.02.2010 um 15:31:52 Uhr
Goto Top
Hallo Sanjo!

Der Zusammenhang zwischen Deinem Makro und Deiner Beispieltabelle erschließt sich mir nicht so ganz?

Steht denn in dem unteren Teil die gesamte Liste mit Kundendaten und entspricht Deine Beispieltabelle dem Origial
oder ist das jetzt nur so ein Gebastel?

Gruß Dieter
Mitglied: Sanjao
Sanjao 10.02.2010 um 15:49:08 Uhr
Goto Top
Hallo Didi,

was möchtest du denn noch genauer wissen? Die Tabelle entspricht fast dem Original. An dem Design wird später noch gefeilt wenn Zeit ist.
Mitglied: 76109
76109 10.02.2010 um 16:07:51 Uhr
Goto Top
Hallo Sanjao!

Also, den Teil mit der Eingabe von Kundennummer und Anzahl von Personen und Ausfüllen des Etiketts verstehe ich ja noch, aber mit dem rechten unteren Teil habe ich irgendwie ein Verständnisproblem?

Makrotechnisch gesehen würde ich es so machen:
Nur einen Button z.B. mit dem Namen "Daten Übernehmen". Der Wochentag wird per Makro automatisch ermittelt und in die jeweilige Spalte Samstag oder Sonntag eingetragen. Das hat den Vorteil, dass keine falsche Eingabe erfolgen kann, wenn in der Hektik aus Versehen der falsche Button betätigt wird.

Und wenn mann sich in der Personenzahl verhauen hat, dann wird die Eingabe einfach wiederholt. Bei einer irrtümlich falschen Eingabe der Kundennummer, wird für diese einfach eine nochmalige 0-Eingabe für Anzahl Personen gemacht.

Außerdem würde im rechten unteren Teil eine Spalte für Samstag und eine Spalte für Sonntag reichen, in der nur die Anzahl der Personen stehen. Der Rest steht ja schon im linken Teil. D.h. also, in der jeweiligen Kundenzeile die Personenzahl in Spalte Samstag oder Sonntag eintragen.

Die Anzahl der Firmen läßt sich dann dadurch ermitteln, ob in der Zelle Anzahl Personen, was drinnen steht oder nicht.

Die Summen für Firmen und Personen im rechten oberen Teil, werden auch nach jeder Eingabe per Makro automatisch aktualisiert.

So stelle ich mir das zumindest vorface-smile

Gruß Dieter
Mitglied: TheEternalPhenom
TheEternalPhenom 10.02.2010 um 18:35:22 Uhr
Goto Top
So ich hatte grad mal etwas Zeit im bei der Zug fahrt.
Wohl gemerkt nur etwas. Also hab ich die Zeit etwas tot geschlagen und mal ein ganz einfaches Programm für dein Problem geschrieben.

Das Programm schreibt eigentlich hinter eine bestimmte ID eine beliebige Zeichenfolge.


Sub test()
Dim id As String
Dim idtemp(1 To 10) As String
Dim temp As String

id = InputBox("Geben Sie die ID ein.")  
anzahl = InputBox("Geben Sie die Anzahl der Besucher an.")  
id = CStr(id)

For a = 1 To 10
zelle = "A" & a  
zelle = CStr(zelle)
Range(zelle).Select
temp = ActiveCell
idtemp(a) = temp
If idtemp(a) = id Then
zelle = "B" & a  
zelle = CStr(zelle)
Range(zelle).Select
ActiveCell = anzahl
End If
Next a

End Sub

Du müsstest jedoch noch die Spalten bzw. Zeilen und die Länge der Tabelle ändern. Es sollte jedoch mit Vorsicht genossen werden, da es ein "Zug" Programm ist face-wink
Mitglied: bastla
bastla 10.02.2010 um 20:09:44 Uhr
Goto Top
@duffman521
Nur als Anmerkung: Anstelle der Zusammensetzung der Adresse in den Zeilen 11 bis 13 kannst Du auch einfach
Cells(a, "A")
verwenden ...

... und die Schleife ersparst Du Dir, wenn Du gleich auf die Excel-Suchfunktion zurückgreifst - also etwa:
Sub Eintragen()
id = InputBox("Geben Sie die ID ein.")  

With Range("A2:A200")  
    Set c = .Find(What:=id, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        c.Offset(0, 1) = InputBox("Geben Sie die Anzahl der Besucher an.")  
    Else
        MsgBox id & " nicht gefunden!"  
    End If
End With
End Sub
Grüße
bastla
Mitglied: TheEternalPhenom
TheEternalPhenom 10.02.2010 um 21:19:15 Uhr
Goto Top
Wie gesagt eine kleine Zug Spielerei, welche nur mit Vorsicht zu genießen ist.

Ich muss aber sagen sowie Sanjao zu VBA geäußert hat hab ich gar nicht an die Auto Suchfunktion gedacht und ein Script geschrieben welches mit relative einfachen Befehlen auskommt.
Mitglied: bastla
bastla 10.02.2010 um 21:27:14 Uhr
Goto Top
@duffman521
... sowie Sanjao zu VBA geäußert hat hab ich gar nicht an die Auto Suchfunktion gedacht und ein Script geschrieben welches mit relative einfachen Befehlen auskommt.
Meine Hinweise waren ja eigentlich auch eher für Dich gedacht (falls Du sie brauchen kannst) ...

Grüße
bastla
Mitglied: Sanjao
Sanjao 11.02.2010 um 09:09:21 Uhr
Goto Top
Guten Morgen zusammen.

Ersten Mal vielen Dank für eure Antworten.

zu Didi:
Also den rechten unteren Teil kannst du ignorieren, dort soll einfach nur die Anzahl der Personen ausgegeben werden. Wenn die Anzahl der Besucher in der selben Zeile wie die Kundendaten steht, brauch die KDNR nicht extra ausgegeben werden. So wie du denkst würde ich es auch machen... wenn ich es könnte. Genau dort liegt ja das Problem. Ich weis das es machbar ist, nur leider (noch) nicht von mir und ich kann von keinem erwarten mir so ein Makro zu schreiben.

zu Duffman:
Vielen vielen Dank für deine Mühe. Ich mach mich jetzt sofort daran und werde versuchen das Ganze umzusetzen und zu verstehen.

zu Bastla:
Auch dir danke für deine Mühe. Ich versuch mich erst mit dem Programm von Duffman und werde dann versuchen an dem Ganzen etwas zu feilen. Weil wie Duffman schon richtig erkannt hat, ich hab keine Ahnung und will es erst mal mit was einfachem versuchen.
Mitglied: 76109
76109 11.02.2010 um 09:44:56 Uhr
Goto Top
Guten Morgen Sanjo!

So in etwa würde ich die Tabelle erstellen:
b0cccac0226723da8d78cb326633e797
Deine Tabelle enthält ausgeblendete Spalten, in denen vermutlich die SVerweis-Formeln stehen. Bei der Makrosteuerung werden jedoch keine Formeln gebraucht, weil alles automatisiert abläuft.

Ich schlage vor Du siehst Dir die Tabelle an und baust diese bei Bedarf nach Deinen Wünschen um, und postest nochmal das Endergebnis, damit ich die Konstanten Zell-Adressen und Spalten in den Makro-Konstanten entsprechend anpassen kann. Hinterher kann immer noch verschoben werden, wobei dann auch nur Konstanten entsprechend angepasst werden müssen.

Die Spalte N und O auf der rechten Seite können dann alle Jahre zwei neue Spalten eingefügt und der obere Teil kopiert und mit einer neuen Jahreszahl versehen werden. Die anderen Jahre rutschen dann immer nach rechts und bleiben erhalten.

Der dazugehörige Code, würde passend zu meinem Tabellenentwurf, so aussehen, wobei dieser nur funktioniert, wenn in der Taskleiste das Systemdatum auf einen Samstag oder Sonntag gesetzt wird.

Quellcode im VB-Editor Tabellenblatt:
Private Sub CommandButton1_Click()
    Call SetKundendaten
End Sub
Quellcode im VB-Editor in einem Modul:
Option Explicit

Const InputKn = "D4"        'Zelle: Eingabe Kundennummer  
Const InputPs = "D5"        'Zelle: Eingabe Anzahl Personen  

Const EtFarbe = "J3"        'Zelle: Etikett Farbe  
Const EtKdNr = "J4"         'Zelle: Etikett Kundennummer  
Const EtFirma = "J5"        'Zelle: Etikett Firma  
Const EtName = "J6"         'Zelle: Etikett Name  
Const EtOrt = "J7"          'Zelle: Etikett Ort  

Const SumSaFa = "N5"        'Zelle: Summe Samstag Anzahl Firmen  
Const SumSaPs = "O5"        'Zelle: Summe Samstag Anzahl Personen  
Const SumSoFa = "N8"        'Zelle: Summe Sonntag Anzahl Firmen  
Const SumSoPs = "O8"        'Zelle: Summe Sonntag Anzahl Personen  

Const Zeile1Kd = 24         'Zeile 1 Kundendaten  

Const SpalteKn = "C"        'Spalte: Kundendaten Kundennummer  
Const SpalteFa = "D"        'Spalte: Kundendaten Firma  
Const SpalteCo = "E"        'Spalte: Kundendaten Farbe  
Const SpalteNa = "F"        'Spalte: Kundendaten Name  
Const SpalteOr = "H"        'Spalte: Kundendaten Ort  

Const SpalteSa = "N"        'Spalte: Kundendaten Samstag  
Const SpalteSo = "O"        'Spalte: Kundendaten Sonntag  

Const Msg1 = "Die Eingaben sind unvollständig!"  
Const Msg2 = "Kunden-Nr.: % nicht gefunden!"  
Const Msg3 = "Heute ist kein Samstag oder Sonntag!"  

Const Samstag = 6           'Wochentag Samstag  
Const Sonntag = 7           'Wochentag Sonntag  

Sub SetKundendaten()
    Dim KdNr As String, Daten As Range, Tag As Integer, Zeile As Long
    Dim SpalteW As String, ZelleFa As String, ZellePs As String
    
    If Range(InputKn) = "" Or Range(InputPs) = "" Then  
        MsgBox Msg1, vbExclamation, "Fehler":  Exit Sub  
    End If
    
    Tag = DatePart("w", Date, vbMonday)  
            
    If Tag = Samstag Then
        SpalteW = SpalteSa:  ZelleFa = SumSaFa:  ZellePs = SumSaPs
    ElseIf Tag = Sonntag Then
        SpalteW = SpalteSo:  ZelleFa = SumSoFa:  ZellePs = SumSoPs
    Else
        MsgBox Msg3, vbExclamation, "Fehler":  Exit Sub  
    End If
            
    Set Daten = Columns(SpalteKn).Find(Range(InputKn), LookIn:=xlValues, LookAt:=xlWhole)
        
    If Daten Is Nothing Then
        MsgBox Replace(Msg2, "%", Range(InputKn)), vbExclamation, "Fehler"  
    Else
        Zeile = Daten.Row
        Range(EtFarbe).Interior.ColorIndex = Cells(Zeile, SpalteCo).Interior.ColorIndex
        Range(EtKdNr) = Cells(Zeile, SpalteKn)
        Range(EtFirma) = Cells(Zeile, SpalteFa)
        Range(EtName) = Cells(Zeile, SpalteNa)
        Range(EtOrt) = Cells(Zeile, SpalteOr)
            
        If Range(InputPs) = 0 Then Cells(Zeile, SpalteW) = "" Else Cells(Zeile, SpalteW) = Range(InputPs)  
    
        Zeile = Cells(Rows.Count, SpalteW).End(xlUp).Row
        
        With WorksheetFunction
            Range(ZelleFa) = .Count(Range(Cells(Zeile1Kd, SpalteW), Cells(Zeile, SpalteW)))
            Range(ZellePs) = .Sum(Range(Cells(Zeile1Kd, SpalteW), Cells(Zeile, SpalteW)))
        End With
    End If
End Sub

In den Eingabefeldern dürfen keine Leereingaben erfolgen. Zum Löschen eines Eintrages muss der Wert 0 eingegeben werden, damit der Zellinhalt entfernt wird.

Gruß Dieter
Mitglied: TheEternalPhenom
TheEternalPhenom 11.02.2010 um 11:02:40 Uhr
Goto Top
@ bastla

Tipps nehme ich natürlich gerne an.

@ didi1954

Neben deinem Script erblasst mein und bastla´s Script in Neid face-smile
Mitglied: 76109
76109 11.02.2010 um 11:11:33 Uhr
Goto Top
Hallo duffman521!

Danke für die Blumen, aber bastla kann das mindestens genausogut und hat mir erstmal, aus reiner Höflichkeit den Vortritt gelassenface-smile

Gruß Dieter
Mitglied: TheEternalPhenom
TheEternalPhenom 11.02.2010 um 11:29:31 Uhr
Goto Top
Das vermute ich auch, wobei ich mich bei meinem Post auf die schon vorhandenen Programme bezogen habe.

Hätte ich nicht gerade die Zug fahrt genutzt wäre mein Programm wahrscheinlich auch etwas größer ausgefallen, denke ich jedenfalls face-big-smile
Mitglied: Sanjao
Sanjao 11.02.2010 um 11:57:50 Uhr
Goto Top
Ihr seid echt Klasse!
Vielen Dank für die Mühe Didi.

Ich brauche noch etwas Zeit, da ich versuche das Ganze auch zu verstehen und hier und da nach zu lesen. Möchte es nicht einfach nur abtippen.
Mitglied: TheEternalPhenom
TheEternalPhenom 11.02.2010 um 12:20:43 Uhr
Goto Top
Das ist eine gute Einstellung, reines abtippen hat dem eine oder anderen meiner ehemaligen Klassenkameraden das Genick im Prog.unterricht gebrochen.
Mitglied: 76109
76109 11.02.2010 um 12:40:30 Uhr
Goto Top
Hallo Sanjao!

Zitat von @Sanjao:
Ihr seid echt Klasse!
Vielen Dank für die Mühe Didi.
Gern geschehenface-wink
Ich brauche noch etwas Zeit, da ich versuche das Ganze auch zu verstehen und hier und da nach zu lesen. Möchte es nicht
einfach nur abtippen.
Da gebe ich duffman521 recht, dass ist eine gute Einstellung und falls Du Fragen hast, beantworten wir die gerneface-smile

Gruß Dieter