Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1072to1076
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
Inhaltsverzeichnis

VBA-Sverweis + Datum, mehr als 1 Zelle

VBA-Sverweis + Datum, mehr als 1 Zelle
02.05.2009 16:43:02
Alex
Hallo Leute,
mit diesem Code führe ich für eine Datei einen Bearbeiternachweis.
Das läuft so, wenn jemand auf dem 1. Tabellenblatt eine Eintragung macht, wird dies auf dem 2. Tabellenblatt mit Name und Datum protokolliert.
Das geht, wenn man nur 1 Zelle beschreibt oder die Eintragung dort löscht.
Macht man es mit mehreren Zellen, sagt Excel "Typen unverträglich".
Dann schreibt XL nicht den Namen und das Datum in die entsprechenden Zellen, sondern die Formel:
=SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "," & " " & SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH)
Das Problem liegt scheinbar an dem "& Date".
Arbeite ich ohne "& Date" in dem Code, nur mit ".Value", dann gehts - dann wird halt der Name ohne Datum in die entsprechenden Zellen geschrieben.
ist es möglich den Code so zu verändern dass die Formel im Code dann so lautet?:
=SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "," & " " & SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) &" " & TEXT(HEUTE();"TT.MM.JJJJ")
hier ist der aktuelle Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sUser As String
Dim rZelle As Range
ActiveSheet.Unprotect "Kennwort"
sUser = Environ$("USERNAME")
'Zelle
Set rZelle = Sheets("Bearbeiternachweis").Range(Target.Address)
'Sverweisformel
With rZelle
.Formula = "=VLookup(" & sUser & "," & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200, 2, False) & "","" & "" "" &  _
VLookup(" & sUser & "," & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200, 3, False)"
.Formula = .Value & " - " & Date
End With
ActiveSheet.Protect "Kennwort", userinterfaceonly:=True
ActiveSheet.EnableAutoFilter = True
End Sub


Danke für die Hilfe,
Gruss
Alex

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Quick und Dirty
03.05.2009 13:44:51
Ramses
Hallo
With rZelle
.FormulaLocal = "=SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "," & " " & SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) &" " & TEXT(HEUTE();"TT.MM.JJJJ")"
Gruss Rainer
Kleine Korrektur...
03.05.2009 13:47:08
Ramses
Hallo
Die Anführungszeichen müssen natürlich doppelt sein
With rZelle
.FormulaLocal = "=SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "","" & "" "" & SVERWEIS(4682232;'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) &"" "" & TEXT(HEUTE();""TT.MM.JJJJ"")"
Gruss Rainer
Anzeige
@Rainer: Frage...
04.05.2009 16:25:43
Alex
Hallo Rainer,
danke für deine Antwort!
Das mit "FormulaLocal" klappt super!
Ich hab da nur noch ein Problemchen (wegen meiner schlechten Kenntnisse) bei der Umsetzung.
Wenn du dich erinnerst hab ich in meinem Code mit VLookup gearbeitet. Bei dieser engl. Bezeichnung klappt es nicht, wenn ich FormulaLocal verwende. Nur mit der deutschen Bezeichnung SVerweis.
Was ist denn da der Unterschied?
Ich brauche nicht nur meinen Usernamen 4682232, sondern (variabel) wer angemeldet ist als Suchkriterium.
Bei meinem Ursprungscode ist das sUser=Environ$ ("USERNAME")
Ich habs mal so probiert:
With rZelle
.FormulaLocal = "=SVERWEIS(sUser & "";"" &'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "","" & "" "" & SVERWEIS(sUser & "";"" &'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) & "" "" & TEXT(HEUTE();""TT.MM.JJJJ"")"
aber damit kann SVerweis nix anfangen: Name?
Wie kann ich denn diesen sUser als Suchkriterium verwenden?
Danke für deine Hilfe
Gruss
Alex
Anzeige
Erledigt, siehe posting...
04.05.2009 16:42:44
Alex
Hi Rainer,
habe auch mal etwas selber geschafft (stolz!!!)
Habs jetzt so geschrieben und klappt:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sUser As String
Dim rZelle As Range
'angemeldeter Username
sUser = Environ$("USERNAME")
'Zelle
Sheets("BearbeiternachweisB").Unprotect "Kennwort"
Set rZelle = Sheets("BearbeiternachweisB").Range(Target.Address)
'Sverweisformel
With rZelle
.FormulaLocal = "=SVERWEIS(" & sUser & ";" & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "","" & "" "" & SVERWEIS(" _
& sUser & ";" & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) & "" "" & TEXT(HEUTE();""TT. _
MM.JJJJ"")"
End With
Sheets("BearbeiternachweisB").Protect "Kennwort"
End Sub


Danke noch mal für deine Hilfe,
obwohl mir der Unterschied (wenn es einen gibt) zu VLookup noch nicht klar ist in Bezug auf FormulaLocal.
Gruss
Alex

Anzeige
Ganz einfach...
04.05.2009 17:20:28
Ramses
Hallo
Mit "FormulaLocal" kannst du die Formel in der lokalen Landessprache übergeben, und EXCEL übernimmt sie so wie sie ist. Das funktioniert einwandfrei, solange die Formel mit VBA in einer deutschen Version/Windows-Umgebung verwendet wird.
Mit einer anderen Sprachversion klappt das nicht mehr. Das kommt dann zum tragen, wenn die Dateien international verwendet werden.
Für Programmieranfänger,... und auch Profis, ist das elegant. Du kannst die Formel in der Tabelle testen, und wenn sie funktioniert, übernimmst du sie in den VBA-Code
Gruss Rainer
AW: Cool! Mir ist aber folgendes aufgefallen...
04.05.2009 18:07:21
Alex
Hi Rainer,
mir ist gerade aufgefallen, dass mit dem deutschen Codeteil die Formel direkt in den Bearbeiternachweis übernommen wird.
Bei VLookup wurde nur der Wert, sprich das Ergebnis der 2 Sverweise plus das Datum in die jeweiligen Zellen geschrieben.
Ist das jetzt gar nicht mehr anders möglich?
Danke für die Hilfe
Gruss
Alex
Anzeige
AW: Cool! Mir ist aber folgendes aufgefallen...
04.05.2009 18:19:58
Ramses
Hallo
Kommt darauf an was du haben willst.
Die Formel,... dann mit FormulaLocal
Nur die Werte,...dann mit
DeineZelle = Application.Worksheetfunction.VlookUp(....) usw.
Damit kannst du von jeder EXCEL Funktion, mit den entsprechenden Parameterns natürlich, die Werte in die Zelle schreiben.
Gruss Rainer
ich kriegs nicht gebacken
04.05.2009 19:56:54
Alex
Hallo Rainer,
ich kapier nicht wie ich das "DeineZelle = Application.Worksheetfunction.VlookUp" in dem Code einsetzen soll.
Kannst du mir den Code ergänzen?
Oder hab ich dich missverstanden und das ist mit der "deutschen Bezeichnung" gar nicht möglich.
hier ist noch mal der mit deiner Hilfe aktualisierte Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sUser As String
Dim rZelle As Range
'angemeldeter Username
sUser = Environ$("USERNAME")
'Zelle
Sheets("BearbeiternachweisB").Unprotect "Kennwort"
Set rZelle = Sheets("BearbeiternachweisB").Range(Target.Address)
'Sverweisformel
With rZelle
.FormulaLocal = "=SVERWEIS(" & sUser & ";" & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 2; FALSCH) & "","" & "" "" &  _
SVERWEIS(" _
& sUser & ";" & _
"'Z:\Dateipfad\[Basis.xls]Tabelle1'!$K$5:$N$200; 3; FALSCH) & "" "" & TEXT(HEUTE();"" _
TT. _
MM.JJJJ"")"
End With
Sheets("BearbeiternachweisB").Protect "Kennwort"
End Sub


Danke für die Hilfe,
Gruss Alex

Anzeige
Was willst du denn erreichen ?
04.05.2009 22:50:23
Ramses
Hallo
Willst du nur die Werte schreiben ?
With rZelle
.Value = Application.Worksheetfunction.Vlookup(......)
End With
Wie die Formel in die Zelle kommt weisst du ja.
Gruss Rainer
Kann mich schlecht erklären, hab mal ne Mappe ang
05.05.2009 15:42:50
Alex
Hi Rainer,
ist für mich schwer zu erklären!
Hab dir mal ne Mappe gebastelt.
Darin stellt Tabelle1 eine externe Datei dar, woher ich mit Sverweis Daten beziehe.
Wenn du jetzt mal in Bronze! ein "x" in den Bereich schreibst, schreiben sich im BearbeiternachweisB!
die aus dem Username per Sverweis ermittelten Daten + das Datum.
Wenn du das mit mehreren Zellen machst (mehrere Zellen Markieren und z.B. auf Entf. drücken) dann kommt die Fehlermeldung.
Und wenn du im Bearbeiternachweis schaust, steht da plötzlich die Formel und nicht mehr ein Wert mit Datum.
Ich möchte also erreichen, dass dort (auch wenn ich mehrere Zellen gleichzeitig bearbeite/ löschen/eintrage) der Text per Code wie in den Zellen BearbeiternachweisB!F7 oder G7 steht.
Nicht wie in F8 oder G8 - ohne Datum und per hineingeschriebene Formel (diese Zellen-Bronze!F8 und G8 habe ich markiert und gleichzeitig (durch z.B.: Entf.) bearbeitet.)
Hier ist der Link:
da kannst du mal probieren, muss aber noch deinen Username in Tabelle1! reinschreiben.
https://www.herber.de/bbs/user/61621.xls
Vielen lieben Dank für deine Hilfe,
Gruss Alex
Anzeige

308 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige