PDA

Vollständige Version anzeigen : Einkaufsliste


toby_fi
21.07.2014, 18:09
Hallo zusammen,

ich möchte eine Einkaufsliste erstellen.
In der Spalte a sind die Produkte aufgeführt. In B&C die Auswahl zum an"x"en.
Jetzt hätte ich es gerne so, dass wenn ich z.b Marmelade und Salz an"x"e, dass diese dann automatisch auf das Tabellenblatt 2 in der zusammenfassung angezeigt werden. Dies soll unabhängig davon geschehen ob das x in Spalte b oder C steht.
Hat hier jmd eine Idee???;)

xlph
21.07.2014, 18:47
Hallo toby_fi,

ins Codefenster der Tabelle1:

Option Explicit

Private Const cUeberwachterBereich As String = "E1:F100000"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Artikel_Liste As Variant
Dim Checked_Liste As Variant
Dim IndexAL As Long

Dim Artikel_Checked As Variant
Dim IndexAC As Long

If Not Intersect(Range(cUeberwachterBereich), Target) Is Nothing Then
Artikel_Liste = Intersect(Columns(1), Range(cUeberwachterBereich).EntireRow, Me.UsedRange).Value
Checked_Liste = Intersect(Range(cUeberwachterBereich), Me.UsedRange).Value

ReDim Artikel_Checked(LBound(Artikel_Liste) To UBound(Artikel_Liste), 1 To 1)

For IndexAL = LBound(Artikel_Liste) To UBound(Artikel_Liste)
If Not IsEmpty(Artikel_Liste(IndexAL, 1)) Then
If LCase$(Checked_Liste(IndexAL, 1)) = "x" Or _
LCase$(Checked_Liste(IndexAL, 2)) = "x" Then
IndexAC = IndexAC + 1
Artikel_Checked(IndexAC, 1) = Artikel_Liste(IndexAL, 1)
End If
End If
Next

Tabelle2.Columns(1).ClearContents
If IndexAC > 0 Then
Tabelle2.Range("A1").Resize(IndexAC, 1).Value = Artikel_Checked
End If

End If
End Sub

Wilfried07
21.07.2014, 19:45
Hallo!

Oder so! Art.-Nr. und Menge eingeben

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#0000ff; font-weight:bold; ">&nbsp;</td><td >&nbsp;</td><td > </td><td > </td><td > </td><td style="background-color:#c0c0c0; ">&nbsp;</td><td style="background-color:#c0c0c0; ">Angebotsliste</td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffcc99; ">Art.-Nr.</td><td style="background-color:#ffcc99; ">Produkt</td><td style="background-color:#ffcc99; text-align:center; ">Menge</td><td style="background-color:#ffcc99; text-align:center; ">Preis</td><td style="background-color:#ffcc99; text-align:center; ">Summe</td><td style="background-color:#c0c0c0; ">Art.-Nr.</td><td style="background-color:#c0c0c0; ">Produkt</td><td style="background-color:#c0c0c0; ">Preis</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffcc99; text-align:right; ">2</td><td style="background-color:#ffcc99; ">Brot</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">2,52</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">5,04</td><td style="background-color:#c0c0c0; text-align:right; ">2</td><td style="background-color:#c0c0c0; ">Brot</td><td style="background-color:#c0c0c0; text-align:right; ">2,52</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffcc99; text-align:right; ">11</td><td style="background-color:#ffcc99; ">Mineral</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">13,4</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">26,8</td><td style="background-color:#c0c0c0; text-align:right; ">11</td><td style="background-color:#c0c0c0; ">Mineral</td><td style="background-color:#c0c0c0; text-align:right; ">13,40</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc99; text-align:right; ">1</td><td style="background-color:#ffcc99; ">Zucker</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">10,5</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">21</td><td style="background-color:#c0c0c0; text-align:right; ">1</td><td style="background-color:#c0c0c0; ">Zucker</td><td style="background-color:#c0c0c0; text-align:right; ">10,50</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc99; text-align:right; ">8</td><td style="background-color:#ffcc99; ">Leberk&auml;s</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">4,23</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">8,46</td><td style="background-color:#c0c0c0; text-align:right; ">5</td><td style="background-color:#c0c0c0; ">&Auml;pfel</td><td style="background-color:#c0c0c0; text-align:right; ">3,85</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffcc99; text-align:right; ">12</td><td style="background-color:#ffcc99; ">Bier</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">10,2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">20,4</td><td style="background-color:#c0c0c0; text-align:right; ">6</td><td style="background-color:#c0c0c0; ">Birnen</td><td style="background-color:#c0c0c0; text-align:right; ">3,11</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffcc99; text-align:right; ">123</td><td style="background-color:#ffcc99; ">Pommes</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">1,12</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">2,24</td><td style="background-color:#c0c0c0; text-align:right; ">3</td><td style="background-color:#c0c0c0; ">Wurst</td><td style="background-color:#c0c0c0; text-align:right; ">5,22</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffcc99; text-align:right; ">3</td><td style="background-color:#ffcc99; ">Wurst</td><td style="background-color:#ffcc99; text-align:center; ">2</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">5,22</td><td style="background-color:#ffcc99; color:#333333; text-align:center; ">10,44</td><td style="background-color:#c0c0c0; text-align:right; ">8</td><td style="background-color:#c0c0c0; ">Leberk&auml;s</td><td style="background-color:#c0c0c0; text-align:right; ">4,23</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; ">&nbsp;</td><td style="background-color:#c0c0c0; text-align:right; ">123</td><td style="background-color:#c0c0c0; ">Pommes</td><td style="background-color:#c0c0c0; text-align:right; ">1,12</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; ">&nbsp;</td><td style="background-color:#ffcc99; color:#333333; ">&nbsp;</td><td style="background-color:#c0c0c0; text-align:right; ">9</td><td style="background-color:#c0c0c0; ">Wein</td><td style="background-color:#c0c0c0; text-align:right; ">2,48</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#c0c0c0; text-align:right; ">12</td><td style="background-color:#c0c0c0; ">Bier</td><td style="background-color:#c0c0c0; text-align:right; ">10,20</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B4</td><td >=WENN(A4="";"";SVERWEIS<span style=' color:008000; '>($A4;$F$4:$H$13;SPALTE<span style=' color:#0000ff; '>(B1)</span>;0)</span>)</td></tr><tr><td >D4</td><td >=WENN(A4="";"";SVERWEIS<span style=' color:008000; '>($A4;$F$4:$H$13;SPALTE<span style=' color:#0000ff; '>(C1)</span>;0)</span>)</td></tr><tr><td >E4</td><td >=WENN(A4="";"";C4*D4)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Gruß
Wilfried

toby_fi
22.07.2014, 16:31
Hallo,

danke das ist eine echt tolle Lösung.
Da ich in VBA so gar nicht bewandelt bin eine Verständnisfrage, woran kann man erkennen, welche Daten dann ausgeählt werden bzw. mit welchen Spalten gearbeitet wird? Würde mich echt interessieren, wie sich das Ding zusammensetzt.
Vielen Dank für die Erläuterungen

xlph
22.07.2014, 16:36
Hallo,

gar nicht, war auch nicht gefordert.