870 Aufrufe
Gefragt in Tabellenkalkulation von jonesm Mitglied (191 Punkte)
Hallo,

in Spalte A lese ich über einen Scanner einen 8-stelligen Barcode ein.

In derselben Zeile sollen dann in Spalte B die ersten vier Stellen eingetragen werden und in der Spalte C die letzten vier Stellen.

Bis jetzt mache ich dies mit der Formel =links(a1;4) und =rechts(a1;4). Allerdings muss ich die Werte mit SVERWEIS weiter verarbeiten und das funktioniert mit den Formeln nicht.

In Spalte D erscheint das Datum. Dies mache ich mit folgendem Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then
Else
'Target.Offset(0, 3).Value = Date & " " & Time
Target.Offset(0, 3).Value = Date
End If
End Sub

Wie muss ich das Makro anpassen, damit 4 link und 4 rechts eingetragen werden?

Vielen Dank und Grüße
Thomas

9 Antworten

0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Thomas,

Bis jetzt mache ich dies mit der Formel =links(a1;4) und
=rechts(a1;4).


Da es sich bei LINKS() und RECHTS() um Textfunktionen handelt,
erzeugen diese logischerweise auch Textwerte, auch wenn sie aus
Zahlen bestehen.

Abhilfe:
Setze 2 Minuszeichen vor die Formeln

=--LINKS(A1;4)
=--RECHTS(A1;4)


oder multipliziere die Formeln mit 1

=LINKS(A1;4)*1
=RECHTS(A1;4)*1


Damit werden die Ergebnisse in echte Zahlen ungewandelt und der
SVERWEIS() funktioniert damit.

Gruß
Rainer
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)
hi Thomas ^^

Wie gewünscht

Gruss nighty

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Cells(Target.Row, 2) = Mid(Cells(Target.Row, 1), 1, 4)
Cells(Target.Row, 3) = Mid(Cells(Target.Row, 1), 5, 4)
Cells(Target.Row, 4) = Date
End If
Application.EnableEvents = True
End Sub
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)
Hi Thomas ^^

Dein Makro beinhaltet auch 2 Fehler

1) Das Ereignis löst einen Cirkelbezug aus(2 maliger aufruf des ereignisses)
Vermeidbar mit
Am Anfang eines Ereignisses bzw deines Makros
Application.EnableEvents = False

Am Ende eines Ereignisses bzw deines Makros
Application.EnableEvents = True


2) Die if abfrage ist fehlerhaft,wozu das else ?
Korrigiert
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Offset(0, 3).Value = Date
End If


Gruss Nighty
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)
Hi Thomas ^^

Zum Verständnis eines Cirkelbezuges

Jedweder zugriff auf eine Zelle löst das ereigniss(in deinem Beispiel das Worksheet_Change ereigniss) erneut aus und dreht sich nun im Kreis :-)

Gruss nighty
0 Punkte
Beantwortet von jonesm Mitglied (191 Punkte)
Hallo,

vielen Dank für euer Hilfe!

@Rainer, vielen Dank für deinen Hinweis! Das hilft mir an anderer Stelle weiter! ;-)

@Nighty, das hat mein Problem gelöst! Danke.

Kann ich über diese Funktion auch einen SVERWEIS einfügen?
In Spalte E hole ich mir aus einer anderen Tabelle über SVERWEIS die Artikelgruppe dazu:
=SVERWEIS(B2;Hilfstabelle!A:C;2;0)

Würde das auch mit
Cells(Target.Row, 3) = ??? funktionieren?

VG Thomas
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Thomas,

probier's mal so

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Cells(Target.Row, 2) = Mid(Cells(Target.Row, 1), 1, 4)
Cells(Target.Row, 3) = Mid(Cells(Target.Row, 1), 5, 4)
Cells(Target.Row, 4) = Date
Cells(Target.Row, 5) =
WorksheetFunction.VLookup(Cells(Target.Row, 2),
Worksheets("Hilfstabelle").Range("A:C"), 2, 0)
End If
Application.EnableEvents = True
End Sub


Gruß
Rainer
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)
Hi Thomas und All ^^

Bei Excel 2000 !
Rechtsbündiger Inhalt einer Zelle wird als Zahl wiedergegeben
Linkssbündiger Inhalt einer Zelle wird als Text wiedergegeben

Sollte es bei anderen Excel Versionen anders sein,dann waere
die folgende Lösung eine möglichkeit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Cells(Target.Row, 2) = Val(Mid(Cells(Target.Row, 1), 1, 4))
Cells(Target.Row, 3) = Val(Mid(Cells(Target.Row, 1), 5, 4))
Cells(Target.Row, 4) = Date
End If
Application.EnableEvents = True
End Sub


Gruss Nighty
0 Punkte
Beantwortet von jonesm Mitglied (191 Punkte)
Hallo,

die Lösung von nighty ist leicht modifiziert im Einsatz. Allerdings mit noch folgendem Problem: Wenn der Wert aus Spalte 2 in der Hilfstabelle nicht vorhanden ist, bricht der Code ab mit einer Fehlermeldung:

Laufzeitfehler 1004:
Die VLookup-Eigenschaften kann nicht zugeordnet werden.


Nachdem ich auf Beenden klicke, läuft das Makro nicht mehr. Ich muss Excel schließen und neu starten, damit es wieder funktioniert (vielleicht auch nur die Datei...) ;-)

Was kann ich tun um den Fehler zu ignorieren oder einen Hinweis eintragen..."nicht vorhanden" oder ähnliches?
Vielen Dank und Grüße


[code]Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 1 Then
Cells(Target.Row, 2) = Mid(Cells(Target.Row, 1), 1, 4)
Cells(Target.Row, 3) = Mid(Cells(Target.Row, 1), 5, 4)
Cells(Target.Row, 4) = Date
Cells(Target.Row, 5) = WorksheetFunction.VLookup(Cells(Target.Row, 2), Worksheets("Hilfstabelle").Range("A:D"), 2, 0)
Cells(Target.Row, 6) = WorksheetFunction.VLookup(Cells(Target.Row, 2), Worksheets("Hilfstabelle").Range("A:D"), 3, 0)
End If
Application.EnableEvents = True

End Sub
0 Punkte
Beantwortet von jonesm Mitglied (191 Punkte)
Ach so,
Zeile 2 (links), 3 (rechts) und 4 (date) werden noch eingetragen, beim Sverweis steht dann das Makro.
...