Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1724to1728
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Zeile suchen und ändern
02.12.2019 18:00:00
Alex
Moin,
ich möchte folgendes machen. Über eine Userform suche ich einen Artikel und kann diesen über comboboxen ändern. Wenn ich nun 2 mal den selben Artikel habe, ich aber nur Einen änder, soll automatisch bei dem zweiten gleichnamigen Artikel ein "XXX" erscheinen.
Wie folgt habe ich es bisher versucht:
  With ActiveSheet   'geöffnete Inventur Datei!
lngZeile = i
Do While .Cells(i, 1).Text = CStr(Me.ComboBox1.Text) And .Cells(lngZeile, 22) = ""
.Cells(lngZeile, 22) = "XXX"
Exit Do
Loop
End With

Zusatzinfos:
-Über die ComboBox1 wird die Artikelnummer gesucht und in Spalte 22 soll das "XXX" eingetragen werden falls dort noch nichts steht.
-Teilweise existieren Artikel bis zu 4 mal.
Kann mir jemand helfen?
Gruss Alex

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeile suchen und ändern
02.12.2019 19:09:30
ChrisL
Hi Alex
Ich nehme mal an, Variable i entspricht bereits der Zeilennummer des ersten gefundenen Eintrages. Weiter nehme ich an, dass die Daten sortiert sind.
lngZeile = i + 1
Do While .Cells(i, 1).Text = CStr(Me.ComboBox1.Text) And .Cells(lngZeile, 22) = ""
.Cells(lngZeile, 22) = "XXX"
lngZeile = lngZeile + 1
Loop
Bei Do While (mach, solange Bedingung erfüllt ist), brauchst du kein zusätzliches Exit Do. Dafür musst du bei der Loop Variante (im Gegensatz zu For Next) die Zeilennummer erhöhen.
cu
Chris
AW: Zeile suchen und ändern
03.12.2019 08:47:15
Alex
Moin,
also wenn ich das mit deinem Code mache werden komplett alle Zeilen unterhalb der gefundenen Zeile mit einem "XXX" in Zeile 22 gekennzeichnet. Es sollen aber nur die mit dem gefundenen Artikel übereinstimmenden Artikel mit einem "XXX" gekennzeichnet werden.
Vielleicht hab ich das nicht genau genug beschrieben. Also ich suche mittels einer Userform eine Artikelnummer (wird in ComboBox1 eingetragen). Anschließend werden die gefundenen Artikel in einer ListBox ausgegeben. Wenn ich dort auf einen Artikel klicke werden die gewünschten Daten zurück in die restlichen ComboBoxen der Userform gegeben. Zusätzlich kann ich nun z.B. den Zähler (in Exceltabelle Zeile 22) eintragen. Wenn nun die anderen gleichnamigen Artikel nicht gezählt wurden und somit auch kein Zähler eingetragen ist(sprich die Zelle ist leer), soll automatisch ein "XXX" dort eingetragen werden.
Gruß Alex
Anzeige
AW: Zeile suchen und ändern
03.12.2019 08:56:39
ChrisL
Hi Alex
Lade eine Beispieldatei (anonymisiert und auf die Aufgabe reduziert). Sonst muss man von Annahmen ausgehen (z.B. Sortierung), die womöglich gar nicht zutreffen.
cu
Chris
AW: Zeile suchen und ändern
03.12.2019 10:49:37
Alex
Moin,
Datei zum öffnen der Userform :
https://www.herber.de/bbs/user/133556.xlsm
Zählliste:
https://www.herber.de/bbs/user/133557.xlsx
Standortliste:
https://www.herber.de/bbs/user/133558.xlsx
Lagerortliste:
https://www.herber.de/bbs/user/133559.xlsx
In dem Modul 1 muss noch der Pfad für den Speicherort eingestellt werden und in der Userform die Pfade für Lagerort und Standortdatei.
Gruß Alex
Anzeige
AW: Zeile suchen und ändern
03.12.2019 15:03:14
ChrisL
Hi Alex
Solch ein Gesamtprojekt zu analysieren benötigt viel Zeit. Es wäre nett, wenn du die Beispieldatei in Zukunft auf die eigentliche Aufgabe reduzierst, denn damit lassen sich solche Probleme in Minuten statt in Stunden lösen.
Schlussendlich war es nur ein kleiner Flüchtigkeitsfehler:
lngZeile = i + 1
Do While .Cells(lngZeile, 1).Text = CStr(Me.ComboBox1.Text) And .Cells(lngZeile, 22) = ""
.Cells(lngZeile, 22) = "XXX"
lngZeile = lngZeile + 1
Loop
cu
Chris
AW: Zeile suchen und ändern
04.12.2019 11:52:37
Alex
Moin,
erstmal danke für deine Hilfe. Es funktioniert nun, dass die nachfolgenden gleichnamigen Artikel mit einem "XXX" versehen werden. Wähle ich aber bspw. den 2. gefundenen Artikel aus der Listbox aus, funktioniert das Ganze nicht mehr. Es müssten aber alle Artikel die die gleiche Artikelnummer haben und nicht bearbeitet wurden mit einem XXX versehen werden. Zusätzlich ist mir noch aufgefallen, dass die Änderungen nicht mehr in das Tabellenblatt "Änderungen" geschrieben werden. Dort taucht lediglich ein Artikel auf aber ohne die eingegebenen Daten. Sobald ich die Do While Schleife rausnehme, tauchen wieder alle eingegebenen Daten in der Änderungstabelle auf.
Gruß Alex
Anzeige
AW: Zeile suchen und ändern
04.12.2019 17:51:54
ChrisL
Hi Alex
Die Performance wird darunter leiden, aber Quick & Dirty
With ActiveSheet
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If .Cells(i, 1) = ComboBox1 And .Cells(i, 22) = "" Then _
.Cells(i, 22) = "XXX"
Next i
End With
cu
Chris
AW: Zeile suchen und ändern
05.12.2019 11:39:30
Alex
Moin,
das funktioniert wunderbar danke. Leider werden die Zeilen wo das XXX eingetragen wird nicht in das Tabellenblatt "Änderungen" übernommen. Liegt wohl daran, dass ich Excel vorgebe das die "lngZeile" kopiert werden soll und das ist eben nur die Zeile wo der entsprechende Artikel gefunden wurde. Habe gerade schon ein wenig rumprobiert aber bisher bin ich zu keiner Lösung gekommen. Kann ich deine Hilfe dafür nochmal in Anspruch nehmen?
Ein Problem was ich dabei auch noch sehe ist, dass die Änderungen stumpf in das Tabellenblatt "Änderungen" reingeschrieben werden, auch wenn der Artikel bereits dort drin steht. Kann man das so einrichten, dass der Eintrag überschrieben wird?
Gruß Alex
Anzeige
AW: Zeile suchen und ändern
05.12.2019 19:08:48
ChrisL
Hi Alex
Aber wenn du die Einträge im Änderungsprotokoll laufend überschreibst, hast du doch letztlich nur eine 1:1 Kopie der bearbeiteten Daten. Anders ausgedrückt: Der Timestamp und User in der Zählliste (=Inventur) gibt doch bereits Auskunft über die letzte Änderung.
Ich würde mir noch einmal Gedanken zur Datenstruktur generell machen. Sollten die User im Netzwerk verbunden sein, dann wäre eine zentrale Datenbank angebracht. Lager- und Standortliste befinden sich idealerweise in der gleichen Datenbank. Wenn die beiden Bezugslisten zwingend extern sein müssen, dann halt kurz ReadOnly (Lesezugriff) öffnen.
(Datenbank kann auch eine Excel-Mappe mit mehreren Tabellen sein)
Es würde mich interessieren wie der konkrete Arbeitsablauf aussieht z.B. arbeiten die User im gleichen Netzwerk, wer bearbeitet welche Datei, arbeiten mehrere User gleichzeitig und wie werden die Daten nach Beendigung der Inventur zusammengefasst? Welche Änderungen müssen wozu und wie protokolliert sein?
cu
Chris
Anzeige
AW: Zeile suchen und ändern
06.12.2019 08:36:11
Alex
Moin Chris,
also das gesetzte Ziel ist folgendes:
Das eigentliche Verwaltungstool generiert jede Nacht eine Excelliste mit allen Artikeln und deren Beständen (siehe Bsp. zählliste) und legt diese auf einem Server ab. Zusätzlich wird eine Liste mit Lagerorten und Standorten generiert (sprich nicht mehr 2 seperate Listen sondern eine Excelliste mit Lager- und Standorten). Die Mitarbeiter sollen dann während der Inventur die Userform nutzen und die gezählten Daten darüber in die generierte Excelliste schreiben. Damit das eigentliche Verwaltungstool nicht alle Artikel überprüfen muss, habe ich die Tabelle "Änderungen" angelegt, in der nur die geänderten Artikel erscheinen. Da die Zählliste jedoch jede Nacht neu erstellt wird, verschwindet die Änderungstabelle auch jede Nacht und muss am nächsten Tag neu erstellt werden.
Die Artikel welche mit dem "XXX" versehen wurden sollen dem eigentlichen Verwaltungstool zeigen, dass ein Artikel der mehrmals auftaucht zwar gezählt wurde, aber eben nicht alle davon. In der in der Nacht neu generierten und aktualisierten Excelliste tauchen dann in den letzten Spalten (die Spalten in denen die Eintragung durch die Userform auftauchen) nur noch der Zähler und das Zähldatum auf.
Achso und zu dem Zugriff: Ich lasse die Datei beim ersten Start freigeben, da mehrere Leute an verschiedenen Laptops gleichzeitig über die Userform Änderungen eintragen.
Ich hoffe ich habe das soweit verständlich für dich beschrieben.
Gruß Alex
Anzeige
AW: Zeile suchen und ändern
06.12.2019 09:03:22
ChrisL
Hi Alex
Was ist denn das für ein Verwaltungstool?
Normalerweise würde man doch die Daten im Verwaltungstool verwalten.
Wie werden die Daten am Abend von Excel zurück ins Verwaltungstool eingelesen?
Ich denke hier müsste nur das Änderungsdatum des jeweiligen Tages gefiltert werden. Ich sehe im Moment noch nicht ganz ein, weshalb du die geänderten Daten laufend duplizieren (aufwändige Synchronisation von 2 Tabellen) willst, obwohl ein einfaches Filterkriterium vorhanden wäre.
cu
Chris
AW: Zeile suchen und ändern
06.12.2019 17:02:37
ChrisL
Hi Alex
Endlich eine ruhige Minute. Ich habe dir die Start-Routine mal komplett neu geschrieben. Code Standardmodul
Option Explicit
Public wksInventur As Worksheet
Public strPfad As String

Sub Inventur_Datei_Öffnen()
Dim wkbTemp As Workbook, varDatei As Variant
strPfad = ThisWorkbook.Path 'hier Pfad anpassen
If Right(strPfad, 1)  "\" Then strPfad = strPfad & "\"
Application.ScreenUpdating = False
' prüfen, ob Zählliste schon offen
For Each wkbTemp In Application.Workbooks
If wkbTemp.Name = "zaehlliste.xlsx" Then
Set wksInventur = wkbTemp.Worksheets(1)
Die_Inventur.Show
Exit Sub
End If
Next wkbTemp
' prüfen, ob Zählliste vorhanden, aber noch nicht geöffnet
If Dir(strPfad & "zaehlliste.xlsx")  "" Then
Set wkbTemp = Workbooks.Open(strPfad & "zaehlliste.xlsx")
Die_Inventur.Show
Exit Sub
End If
' noch nichts vorhanden oder geöffnet, dann neu anlegen
varDatei = Application.GetOpenFilename(MultiSelect:=False)
If varDatei = False Then
Exit Sub
Else
Application.ScreenUpdating = False
Set wkbTemp = Workbooks.Open(varDatei)
wkbTemp.SaveAs Filename:=strPfad & "zaehlliste.xlsx", AccessMode:=xlShared
Set wksInventur = wkbTemp.Worksheets(1)
Die_Inventur.Show
End If
Application.ScreenUpdating = False
End Sub
Beachte, die zwei Variablen, welche Public deklariert sind d.h. die Variablen bleiben über die ganze Laufzeit (ausser man drückt im Debugger "End") erhalten.
Anschliessend machst du über das Modul vom Userform ein Suchen "ActiveSheet" und Ersetzen "wksInventur". Themenstellung:
https://www.herber.de/vbabasics/0009.html
Dann noch kleine Anpassungen in der Initialisierung (Verwendung des zuvor Public deklarierten strPfad und Anregung zur Listbox-Eigenschaft):
Private Sub UserForm_Initialize()
Dim lngZeile As Long
Dim lngZeileMax As Long
Dim i As Long
' ********** Löschen
' ********** diesen Teil direkt in den Listbox-Eigenschaften definieren **********
With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "120;180;60;70;90;80;93;60;50"
.TextAlign = fmTextAlignCenter
End With
Application.ScreenUpdating = False
'ComboBox4 (Lagerorte) konfigurieren
With Workbooks.Open(strPfad & "\Lagerorte.xlsx", ReadOnly:=True)
ComboBox4.List = .Sheets("Tabelle1").Range("A2:A2021").Value
.Close
End With
'ComboBox18 (Standorte) konfigurieren
With Workbooks.Open(strPfad & "\Standorte.xlsx", ReadOnly:=True)
ComboBox18.List = .Sheets("Tabelle1").Range("A2:A207").Value
.Close
End With
'Cursor standardmäßig in die erste Textbox setzen
Me.ComboBox1.SetFocus
Me.Caption = ActiveWindow.Caption
End Sub
Schau mal, ob mein Verständnis soweit richtig ist und ob der Code funktioniert. Ggf. könnte man dann Anfangen den weiteren Code zu bereinigen.
cu
Chris
Anzeige
AW: Zeile suchen und ändern
09.12.2019 09:13:21
Alex
Moin Chris,
sorry das ich jetzt erst antworte aber ich war das ganze Wochenende nicht zuhause. Ich habe den Code wie du ihn umgeschrieben hast getestet und er funktioniert einwandfrei. Zuvor hat er zwar auch schon funktioniert aber so läuft das Ganze noch flüssiger. Ich finde es im Prinzip auch besser, dass die Datei nun automatisch gewählt wird und nicht erst vom Anwender ausgewählt werden muss.
Bei dem Startmodul musste ich noch einfügen, dass der Anwender welcher die Datei als erstes öffnet, diese auch freigibt aber das war kein Problem.
Das mit der Änderungstabelle habe ich jetzt rausgenommen und bin deinem Rat gefolgt, die Änderungen über das Tagesdatum zu filtern.
Danke für deine Hilfe ! Nun fehlen mir eigentlich nur noch Kleinigkeiten. Ich wollte es eigentlich so handhaben, dass in der Combobox das Mausrad zum Scrollen verwendet werden kann, die Codes dazu aus Google funktionieren aber irgendwie nicht bei mir... Das Problem bei den Lagerorten ist eben, dass laufend auch welche dazu kommen und ich den Zellenbereich dementsprechend nicht eingrenzen kann. Wenn ich den Scollbalken verwende lande ich aber direkt soweit unten, dass keine Lagerorte mehr angezeigt werden. Weißt du dafür noch eine Lösung?
Als kurze Zusatzinfo. Diesen Code zur Nutzung des Mausrads habe ich bei Google gefunden:
'''''' normal module code
Option Explicit
Private Type POINTAPI
X As Long
Y As Long
End Type
Private Type MOUSEHOOKSTRUCT
pt As POINTAPI
hwnd As Long
wHitTestCode As Long
dwExtraInfo As Long
End Type
Private Declare PtrSafe Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function GetWindowLong Lib "user32.dll" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" ( _
ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) As Long
Private Declare PtrSafe Function CallNextHookEx Lib "user32" ( _
ByVal hHook As Long, _
ByVal nCode As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" ( _
ByVal hHook As Long) As Long
'Private Declare Function PostMessage Lib "user32.dll" _
'                                         Alias "PostMessageA" ( _
'                                                         ByVal hwnd As Long, _
'                                                         ByVal wMsg As Long, _
'                                                         ByVal wParam As Long, _
'                                                         ByVal lParam As Long) As Long
Private Declare PtrSafe Function WindowFromPoint Lib "user32" ( _
ByVal xPoint As Long, _
ByVal yPoint As Long) As Long
Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" ( _
ByRef lpPoint As POINTAPI) As Long
Private Const WH_MOUSE_LL As Long = 14
Private Const WM_MOUSEWHEEL As Long = &H20A
Private Const HC_ACTION As Long = 0
Private Const GWL_HINSTANCE As Long = (-6)
'Private Const WM_KEYDOWN As Long = &H100
'Private Const WM_KEYUP As Long = &H101
'Private Const VK_UP As Long = &H26
'Private Const VK_DOWN As Long = &H28
'Private Const WM_LBUTTONDOWN As Long = &H201
Private mLngMouseHook As Long
Private mListBoxHwnd As Long
Private mbHook As Boolean
Private mCtl As MSForms.Control
Dim n As Long
Sub HookListBoxScroll(frm As Object, ctl As MSForms.Control)
Dim lngAppInst As Long
Dim hwndUnderCursor As Long
Dim tPT As POINTAPI
GetCursorPos tPT
hwndUnderCursor = WindowFromPoint(tPT.X, tPT.Y)
If Not frm.ActiveControl Is ctl Then
ctl.SetFocus
End If
If mListBoxHwnd  hwndUnderCursor Then
UnhookListBoxScroll
Set mCtl = ctl
mListBoxHwnd = hwndUnderCursor
lngAppInst = GetWindowLong(mListBoxHwnd, GWL_HINSTANCE)
' PostMessage mListBoxHwnd, WM_LBUTTONDOWN, 0&, 0&
If Not mbHook Then
mLngMouseHook = SetWindowsHookEx( _
WH_MOUSE_LL, AddressOf MouseProc,  _
lngAppInst, 0)
mbHook = mLngMouseHook  0
End If
End If
End Sub
Sub UnhookListBoxScroll()
If mbHook Then
Set mCtl = Nothing
UnhookWindowsHookEx mLngMouseHook
mLngMouseHook = 0
mListBoxHwnd = 0
mbHook = False
End If
End Sub
Private Function MouseProc( _
ByVal nCode As Long, ByVal wParam As Long, _
ByRef lParam As MOUSEHOOKSTRUCT) As Long
Dim idx As Long
On Error GoTo errH
If (nCode = HC_ACTION) Then
If WindowFromPoint(lParam.pt.X, lParam.pt.Y) = mListBoxHwnd Then
If wParam = WM_MOUSEWHEEL Then
MouseProc = True
'                                If lParam.hwnd > 0 Then
'                                        PostMessage mListBoxHwnd, WM_KEYDOWN, VK_UP, 0
'                                Else
'                                        PostMessage mListBoxHwnd, WM_KEYDOWN, VK_DOWN, 0
'                                End If
'                                PostMessage mListBoxHwnd, WM_KEYUP, VK_UP, 0
If lParam.hwnd > 0 Then idx = -1 Else idx = 1
idx = idx + mCtl.ListIndex
If idx >= 0 Then mCtl.ListIndex = idx
Exit Function
End If
Else
UnhookListBoxScroll
End If
End If
MouseProc = CallNextHookEx( _
mLngMouseHook, nCode, wParam, ByVal lParam)
Exit Function
errH:
UnhookListBoxScroll
End Function
'''''''' end normal module code

Gruß Alex
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige