Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Excel Eine Zahl eingeben, dass dann automatisch ein text erscheint





Frage

Hallo Zusammen!!! Ich möchte mit dem Excel 03 eine Bestellliste erstellen. a1= Nummer b1=Preis Die Materialien haben alle eine Nummer. Jetzt möchte ich dass wenn ich zum Beispiel in a2 die 1 eingebe, dass dann statt eine 1 zum Beispiel ein Bleistift hervorkommt. Nun sollte dann auch noch der Preis bei b2 stehen. Ich weiss dass das sich nur mit einem Makro machen lässt. Aber ich habe keine Ahnung davon. Desshalb währe ich froh wenn mit jemand den Makrocode geben könnte Mfg arniakop

Antwort 1 von CaroS

Hallo arnikaop,

Du hast völlig recht, in A2 eine 1 eingeben und dann in derselben Zelle statt der 1 den Text Bleistift angezeigt bekommen, das geht nur mit einem Makro. Da Du Dich aber leider mit VBA nicht so auskennst, mache ich Dir einen ähnlichen Vorschlag, den Du sehr gut und einfach mit Formeln realisieren kannst.

Du gibst die 1 in A2 ein und erhältst in B2 den "Bleistift" und in C2 den Preis zu diesem Bleistift. Dazu brauchst Du nur eine dreispaltige Tabelle anlegen mit Spalte 1 (z. B. K) = Kennnummer (z. B. 1 oder 001 - da müsstest Du Dich zwischen Zahl und Text entscheiden), Spalte 2 (z. B. L) = Artikel (z. B. Bleistift), Spalte 3 (z. B. M) = Preis pro Einheit (z. B. 0,15 €). Wenn diese Hilfstabelle, die auf demselben Tabellenblatt oder auch irgendeinem anderen stehen kann, 100 Artikel mit Nummer und Preis enthält, dann steht sie z. B. in K2:M101.

Das war schon der größte Teil der Arbeit, die Du für das VBA-MAkro aber auch so oder so ähnlich hättest investieren müssen, denn nun kommen nur noch zwei "einfache" Formeln:

B2: =SVERWEIS(A2; K$2:M$101; 2; FALSCH)
C2: =SVERWEIS(A2; K$2:M$101; 3; FALSCH)

Befindet sich die "Hilfstabelle" K2:M101 auf einem anderen Tabellenblatt, z. B. in Tabelle2, dann lauten die Formeln:

B2: =SVERWEIS(A2; Tabelle2!K$2:M$101; 2; FALSCH)
C2: =SVERWEIS(A2; Tabelle2!K$2:M$101; 3; FALSCH)

Jetzt brauchst Du die Zellen B2 und C2 nur noch so weit runterzukopieren oder runterzuziehen, wie Du sie für in A eingegebene Nummern brauchst.

Das ist allerdings ziemlich unpraktisch, weil Du dann jedesmal, wenn Du in Spalte A eine oder mehrere neue Nummern eingibst, die Formeln in B und C wieder ein Stück runterkopieren/runterziehen müsstest. Kopierst/ziehst Du sie gleich weiter runter als in A Werte stehen, bekommst Du bei fehlenden A-Werten als Ergebnis #NV angezeigt. Das lässt sich aber auch vermeiden, und zwar mit den Formeln (- mit oder ohne Tabelle2!):

B2: =WENN(A2 = ""; ""; SVERWEIS(A2; Tabelle2!K$2:M$201; 2; FALSCH))
C2: =WENN(A2 = ""; ""; SVERWEIS(A2; Tabelle2!K$2:M$201; 3; FALSCH))

Jetzt kannst Du etwas großzügiger (für spätere Erweiterungen) rangehen:
Auch wenn Du erst 60 Artikel für die Hilfstabelle hast, kannst Du in den Formeln ruhig ein paar (leere) Zeilen mehr angeben, 100, 150 oder 200, siehe oben.
Und Du kannst die Formeln in B und C weiter runterkopieren/runterziehen, als momentan Werte in Spalte A stehen.

Probier´s am besten mal aus! Gruß,
CaroS

Antwort 2 von arnikaop

Hallo CaroS

Leider habe ich das nicht kappiert. :-(
Ich bin ein sehr schlechter Excel kenner.
Wenn du dich mit VBA auskennst, könntest du mir nicht den fertigen code geben, den ich im editor nur noch einfügen muss. ( und anschkiessend noch die namen ändern.

MFG

arnikaop

Antwort 3 von nighty

hi all :)

ein beispiel :)

einzufuegen unter alt f11/projektexplorer/DeineTabelle

gruss nighty

tabelle2/spalteA=artikel
tabelle2/spalteB=Preis

eingabe einer zahl bei tabelle1/spalteA

zahl=eine zahl von MinArikelAnzahl bis MaxArtikelAnzahl

noch arg verbesserungswuerdig

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim merker As Long
ReDim Artikel(1) As String
ReDim Preis(1) As Variant
ReDim Zahl(1) As Long
Dim zaehler As Long
Dim index As Long
merker = Sheets(1).Cells(Target.Row, Target.Column)
If Target.Column = 1 And merker <> 0 Then
If Artikel(1) = "" Then
For zaehler = 2 To Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
index = index + 1
ReDim Preserve Artikel(index)
ReDim Preserve Preis(index)
ReDim Preserve Zahl(index)
Artikel(index) = Sheets(2).Cells(zaehler, 1)
Preis(index) = Sheets(2).Cells(zaehler, 2).Value
Zahl(index) = zaehler - 1
Next zaehler
End If
If Target.Column = 1 Then
Sheets(1).Cells(Target.Row, 1) = Artikel(merker)
Sheets(1).Cells(Target.Row, 2) = Preis(merker)
End If
End If
Application.EnableEvents = True
End Sub

Antwort 4 von CaroS

Hallo arnikaop,

ich kann Dir den VBA-Code schreiben, aber dafür brauche ich noch folgende Angaben:

- Wie heißt das Tabellenblatt, auf dem die 1 u. a. Kennnummern eingegeben werden sollen und in welchem Zellbereich (Spaltenbereich) soll das stattfinden?

- In welchen Spalten sollen die Ergebnisse (Artikel, Preis) stehen und sollen sie immer in genau derselben Zeile stehen, wo die Kennnummer eingegeben wurde?

- Wo sollen die Daten (Hilfstabelle) stehen, die das Makro braucht, um aus einer 1 einen Bleistift mit Preis zu machen?

> Variante 1: Im VBA-Code selbst
>> Vorteile: Sie brauchen keinen Platz auf einem Tabellenblatt, Daten sind im Code "versteckt" und "geschützt".
>> Nachteile: Es gibt für den Anwender keine einsehbare (und kontrollierbare) Übersicht über die Daten. Jede Änderung/Erweiterung muss im VBA-Code vorgenommen werden.

> Variante 2: In einer Hilfstabelle auf einem extra Tabellenblatt
>> Vorteile: Übersicht vorhanden, leicht änder- und erweiterbar, auf einem extra Tabellenblatt unabhängig vom Einfügen/Löschen von Zeilen/Spalten in der "Haupttabelle", Tabellenblatt mit Hilfstabelle kann bei Bedarf ausgeblendet/geschützt werden.

Auch wenn der Programmieraufwand in Variante 2 etwas größer ist, spricht fast alles dafür. Ich würde also mit dieser Variante mal anfangen, aber im Moment passt es zeitlich nicht so.

Gruß,
CaroS

Antwort 5 von CaroS

Hallo nighty,

eine schöne Lösung hast Du da programmiert, während ich noch meinen Text geschrieben habe. Und ich hätte fast schwören können, dass wir hier wieder ein Beispiel von Dir bekommen, wie es statt mit einer Schleife mit einer Suche zu machen geht.

Ich habe letztens Deinen Tipp mal befolgt und einen SVERWEIS so programmiert und wollte es diesmal wieder so machen. Aber das kann ich ja immer noch, das wird dann eine andere Lösung als Deine, nimmt sich im Endeffekt für den Anwender nicht viel. Nur dass es bei mir immer ein klein wenig länger dauert :-))

Gruß und schönes Wochenende!
CaroS

Antwort 6 von nighty

hi all :)

hier noch eine variant :)

tabelle2/spalteA=artikel
tabelle2/spalteB=Preis
tabelle2/spalteC=Nummer

eingabe einer zahl bei tabelle1/spalteA

zahl=eine zahl von tabelle2/spalteC=Nummer

gruss nighty

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim suche As Range
If Target.Column = 1 Then
Set suche = Sheets(2).Range("C2" & ":C" & Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell).Row).Find(Sheets(1).Cells(Target.Row, Target.Column))
If Not suche Is Nothing Then
Sheets(1).Cells(Target.Row, 1) = Sheets(2).Cells(suche.Row, 1)
Sheets(1).Cells(Target.Row, 2) = Sheets(2).Cells(suche.Row, 2)
End If
End If
Application.EnableEvents = True
End Sub

immer noch verbesserungwuerdig :))

Antwort 7 von Beverly

Hi,

ich nehme an, dass deine Artikelnummern bei 1 beginnen und fortlaufend sind? Angenommen sie stehen in I1:I24, deine Artikelbezeichnung (Bleistift usw.) in J1:J24 und der Preis in K1:K24. Bei Eingabe einer Artikelnummer in Spalte A

Code mit Verwendung von VLookup (SVERWEIS):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varWert As Variant
    Application.ScreenUpdating = False
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    If Target > 24 Then
        MsgBox "Diese Artikelnummer gibt es nicht"
        Application.EnableEvents = False
        Target.Select
        Target = ""
        Application.EnableEvents = True
        Exit Sub
    End If
    varWert = Target
    Application.EnableEvents = False
    Cells(Target.Row, 2) = Application.WorksheetFunction.vlookup(varWert, Range("$I$1:$K$24"), 3, Range("$I$1:$I$24"))
    Cells(Target.Row, 1) = Application.WorksheetFunction.vlookup(varWert, Range("$I$1:$K$24"), 2, Range("$I$1:$I$24"))
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub


Code unter Verwendung einer Schleife:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim loletzte As Long, loZeile As Long
    Application.ScreenUpdating = False
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    loletzte = IIf(IsEmpty(Cells(Rows.Count, 9)), Cells(Rows.Count, 9).End(xlUp).Row, Rows.Count)
    For loZeile = 1 To loletzte
        If Cells(loZeile, 9) = Target Then
            Cells(Target.Row, 1) = Cells(loZeile, 10)
            Cells(Target.Row, 2) = Cells(loZeile, 11)
            Exit Sub
        End If
        If loZeile = loletzte And Cells(loZeile, 9) <> Target Then
            MsgBox "Kein Artikel vorhanden"
            Application.EnableEvents = False
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
    Next loZeile
    Application.ScreenUpdating = True
End Sub


Bis später,
Karin

Antwort 8 von nighty

hi all :)

so gefaellts mir richtig gut :))

angaben wie oben

gruss nighty

einzufuegen alt f11/allgemeines modul

Public Artikel() As String
Public Preis() As String
Public index As Long

einzufuegen alt f11/projektexplorer/DeinWorkbook

Private Sub Workbook_Open()
Dim index As Long
Dim zaehler As Long
For zaehler = 2 To Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
index = index + 1
ReDim Preserve Artikel(0 To index)
ReDim Preserve Preis(0 To index)
Artikel(index) = Sheets(2).Cells(zaehler, 1)
Preis(index) = Sheets(2).Cells(zaehler, 2)
Next zaehler
End Sub

einzufuegen alt f11/projektexplorer/DeineTabelle

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
merker = Val(Sheets(1).Cells(Target.Row, Target.Column))
If Target.Column = 1 And Sheets(1).Cells(Target.Row, Target.Column).Value < Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1 Then
Sheets(1).Cells(Target.Row, 1) = Artikel(merker)
Sheets(1).Cells(Target.Row, 2) = Preis(merker)
End If
Application.EnableEvents = True
End Sub

Antwort 9 von nighty

hi caros :))

im laufenden betrieb doch noch etwas schneller als die findfunction :)

gruss nighty