Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Excel VBA Sverweis nach klick auf Button

Frage Entwicklung VB for Applications

Mitglied: Sanjao

Sanjao (Level 1) - Jetzt verbinden

10.02.2010 um 10:06 Uhr, 9155 Aufrufe, 20 Kommentare

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.
Mitglied: simsons
10.02.2010 um 10:15 Uhr
Zelle A1 "Firma Müller"
Zelle B1 "2"
Zelle A2 "Firma Schmitz"
Zelle B2 "5"
Bitte warten ..
Mitglied: Sanjao
10.02.2010 um 10:30 Uhr
Tut mir leid versteh nicht was du damit meinst.
Bitte warten ..
Mitglied: TheEternalPhenom
10.02.2010 um 11:53 Uhr
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

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
Bitte warten ..
Mitglied: Sanjao
10.02.2010 um 12:31 Uhr
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.
Bitte warten ..
Mitglied: Sanjao
10.02.2010 um 14:58 Uhr
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.
Bitte warten ..
Mitglied: 76109
10.02.2010 um 15:31 Uhr
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
Bitte warten ..
Mitglied: Sanjao
10.02.2010 um 15:49 Uhr
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.
Bitte warten ..
Mitglied: 76109
10.02.2010 um 16:07 Uhr
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 vor

Gruß Dieter
Bitte warten ..
Mitglied: TheEternalPhenom
10.02.2010 um 18:35 Uhr
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.



01.
Sub test() 
02.
Dim id As String 
03.
Dim idtemp(1 To 10) As String 
04.
Dim temp As String 
05.
 
06.
id = InputBox("Geben Sie die ID ein.") 
07.
anzahl = InputBox("Geben Sie die Anzahl der Besucher an.") 
08.
id = CStr(id) 
09.
 
10.
For a = 1 To 10 
11.
zelle = "A" & a 
12.
zelle = CStr(zelle) 
13.
Range(zelle).Select 
14.
temp = ActiveCell 
15.
idtemp(a) = temp 
16.
If idtemp(a) = id Then 
17.
zelle = "B" & a 
18.
zelle = CStr(zelle) 
19.
Range(zelle).Select 
20.
ActiveCell = anzahl 
21.
End If 
22.
Next a 
23.
 
24.
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
Bitte warten ..
Mitglied: bastla
10.02.2010 um 20:09 Uhr
@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:
01.
Sub Eintragen() 
02.
id = InputBox("Geben Sie die ID ein.") 
03.
 
04.
With Range("A2:A200") 
05.
    Set c = .Find(What:=id, LookIn:=xlValues, LookAt:=xlWhole) 
06.
    If Not c Is Nothing Then 
07.
        c.Offset(0, 1) = InputBox("Geben Sie die Anzahl der Besucher an.") 
08.
    Else 
09.
        MsgBox id & " nicht gefunden!" 
10.
    End If 
11.
End With 
12.
End Sub
Grüße
bastla
Bitte warten ..
Mitglied: TheEternalPhenom
10.02.2010 um 21:19 Uhr
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.
Bitte warten ..
Mitglied: bastla
10.02.2010 um 21:27 Uhr
@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
Bitte warten ..
Mitglied: Sanjao
11.02.2010 um 09:09 Uhr
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.
Bitte warten ..
Mitglied: 76109
11.02.2010 um 09:44 Uhr
Guten Morgen Sanjo!

So in etwa würde ich die Tabelle erstellen: b0cccac0226723da8d78cb326633e797 - Klicke auf das Bild, um es zu vergrößern
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:
01.
Private Sub CommandButton1_Click() 
02.
    Call SetKundendaten 
03.
End Sub
Quellcode im VB-Editor in einem Modul:
01.
Option Explicit 
02.
 
03.
Const InputKn = "D4"        'Zelle: Eingabe Kundennummer 
04.
Const InputPs = "D5"        'Zelle: Eingabe Anzahl Personen 
05.
 
06.
Const EtFarbe = "J3"        'Zelle: Etikett Farbe 
07.
Const EtKdNr = "J4"         'Zelle: Etikett Kundennummer 
08.
Const EtFirma = "J5"        'Zelle: Etikett Firma 
09.
Const EtName = "J6"         'Zelle: Etikett Name 
10.
Const EtOrt = "J7"          'Zelle: Etikett Ort 
11.
 
12.
Const SumSaFa = "N5"        'Zelle: Summe Samstag Anzahl Firmen 
13.
Const SumSaPs = "O5"        'Zelle: Summe Samstag Anzahl Personen 
14.
Const SumSoFa = "N8"        'Zelle: Summe Sonntag Anzahl Firmen 
15.
Const SumSoPs = "O8"        'Zelle: Summe Sonntag Anzahl Personen 
16.
 
17.
Const Zeile1Kd = 24         'Zeile 1 Kundendaten 
18.
 
19.
Const SpalteKn = "C"        'Spalte: Kundendaten Kundennummer 
20.
Const SpalteFa = "D"        'Spalte: Kundendaten Firma 
21.
Const SpalteCo = "E"        'Spalte: Kundendaten Farbe 
22.
Const SpalteNa = "F"        'Spalte: Kundendaten Name 
23.
Const SpalteOr = "H"        'Spalte: Kundendaten Ort 
24.
 
25.
Const SpalteSa = "N"        'Spalte: Kundendaten Samstag 
26.
Const SpalteSo = "O"        'Spalte: Kundendaten Sonntag 
27.
 
28.
Const Msg1 = "Die Eingaben sind unvollständig!" 
29.
Const Msg2 = "Kunden-Nr.: % nicht gefunden!" 
30.
Const Msg3 = "Heute ist kein Samstag oder Sonntag!" 
31.
 
32.
Const Samstag = 6           'Wochentag Samstag 
33.
Const Sonntag = 7           'Wochentag Sonntag 
34.
 
35.
Sub SetKundendaten() 
36.
    Dim KdNr As String, Daten As Range, Tag As Integer, Zeile As Long 
37.
    Dim SpalteW As String, ZelleFa As String, ZellePs As String 
38.
     
39.
    If Range(InputKn) = "" Or Range(InputPs) = "" Then 
40.
        MsgBox Msg1, vbExclamation, "Fehler":  Exit Sub 
41.
    End If 
42.
     
43.
    Tag = DatePart("w", Date, vbMonday) 
44.
             
45.
    If Tag = Samstag Then 
46.
        SpalteW = SpalteSa:  ZelleFa = SumSaFa:  ZellePs = SumSaPs 
47.
    ElseIf Tag = Sonntag Then 
48.
        SpalteW = SpalteSo:  ZelleFa = SumSoFa:  ZellePs = SumSoPs 
49.
    Else 
50.
        MsgBox Msg3, vbExclamation, "Fehler":  Exit Sub 
51.
    End If 
52.
             
53.
    Set Daten = Columns(SpalteKn).Find(Range(InputKn), LookIn:=xlValues, LookAt:=xlWhole) 
54.
         
55.
    If Daten Is Nothing Then 
56.
        MsgBox Replace(Msg2, "%", Range(InputKn)), vbExclamation, "Fehler" 
57.
    Else 
58.
        Zeile = Daten.Row 
59.
        Range(EtFarbe).Interior.ColorIndex = Cells(Zeile, SpalteCo).Interior.ColorIndex 
60.
        Range(EtKdNr) = Cells(Zeile, SpalteKn) 
61.
        Range(EtFirma) = Cells(Zeile, SpalteFa) 
62.
        Range(EtName) = Cells(Zeile, SpalteNa) 
63.
        Range(EtOrt) = Cells(Zeile, SpalteOr) 
64.
             
65.
        If Range(InputPs) = 0 Then Cells(Zeile, SpalteW) = "" Else Cells(Zeile, SpalteW) = Range(InputPs) 
66.
     
67.
        Zeile = Cells(Rows.Count, SpalteW).End(xlUp).Row 
68.
         
69.
        With WorksheetFunction 
70.
            Range(ZelleFa) = .Count(Range(Cells(Zeile1Kd, SpalteW), Cells(Zeile, SpalteW))) 
71.
            Range(ZellePs) = .Sum(Range(Cells(Zeile1Kd, SpalteW), Cells(Zeile, SpalteW))) 
72.
        End With 
73.
    End If 
74.
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
Bitte warten ..
Mitglied: TheEternalPhenom
11.02.2010 um 11:02 Uhr
@ bastla

Tipps nehme ich natürlich gerne an.

@ didi1954

Neben deinem Script erblasst mein und bastla´s Script in Neid
Bitte warten ..
Mitglied: 76109
11.02.2010 um 11:11 Uhr
Hallo duffman521!

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

Gruß Dieter
Bitte warten ..
Mitglied: TheEternalPhenom
11.02.2010 um 11:29 Uhr
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
Bitte warten ..
Mitglied: Sanjao
11.02.2010 um 11:57 Uhr
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.
Bitte warten ..
Mitglied: TheEternalPhenom
11.02.2010 um 12:20 Uhr
Das ist eine gute Einstellung, reines abtippen hat dem eine oder anderen meiner ehemaligen Klassenkameraden das Genick im Prog.unterricht gebrochen.
Bitte warten ..
Mitglied: 76109
11.02.2010 um 12:40 Uhr
Hallo Sanjao!

Zitat von Sanjao:
Ihr seid echt Klasse!
Vielen Dank für die Mühe Didi.
Gern geschehen
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 gerne

Gruß Dieter
Bitte warten ..
Neuester Wissensbeitrag
Ähnliche Inhalte
Microsoft Office
Excel VBA "SVERWEIS" über mehrere Dateien in Ordner (2)

Frage von Acht85 zum Thema Microsoft Office ...

Microsoft Office
Excel VBA: Tranferieren von Werten aus Quelldatei in die Masterdatei mit SVERWEIS (3)

Frage von peter.schroeder zum Thema Microsoft Office ...

VB for Applications
Excel VBA Sortierung von Daten (4)

Frage von easy4breezy zum Thema VB for Applications ...

VB for Applications
gelöst Excel VBA Eine oder mehrere Zellen Verschieben (2)

Frage von batchnewbie zum Thema VB for Applications ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
FritzBox, zwei Server, verschiedene Netze (18)

Frage von DavidGl zum Thema LAN, WAN, Wireless ...

Windows Netzwerk
Windows 10 RDP geht nicht (18)

Frage von Fiasko zum Thema Windows Netzwerk ...

Windows Server
Outlook Verbindungsversuch mit Exchange (15)

Frage von xbast1x zum Thema Windows Server ...