ich habe eine Userform geschrieben, auf welcher 2 Textfelder und 4 Comboboxen und eine Listbox vorhanden sind.Mittels der Textfelder bzw. Komboboxen kann man nach Aktien suchen, welche ich gehandelt habe.Nun prüft der Code im Falle einer Suche, ob zunächst Textfelder ausgewählt wurden oder eine der Comboboxen anhand des Listindex. Nun stehe ich vor dem Problem, dass das Programm den korrekten Listindex aus der Combobox zurück gibt, in der IF Schleife der Falsche Wert aber genutzt wird. Hat jemand eine Ahnung woran das liegen könnte? Herzlichen Dank.
Hier der Code:
Public wb_TJ As Workbook
Public letzteZeileArray As Integer
Private Sub CommandButton5_Click()
Dim vArr As Variant
Dim LoeschArray As Variant
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim m As Integer
Dim strT As String
Dim strW As String
Dim k As Integer
Dim vOut As Variant
Dim letzteZeileNeuesArray As Long
Dim letzteSuchZeilenArrayLoeschen As Long
Dim SAktie As Integer
Dim SWkn As Integer
Dim CMAktie As String
Dim CMOption As String
Dim CMB1 As Long
Dim Strategie1 As String
Dim Strategie1a As String
Dim Strategie1b As String
Dim Strategie2 As String
strT = Me.TextBox12.Value
strW = Me.TextBox2.Value
CMAktie = "Aktie"
CMOption = "Option"
Strategie1 = "1"
Strategie1a = "1a"
Strategie1b = "1b"
Strategie2 = "2"
With Me.TextBox12.Value
If Len(strT) 0 Then
SAktie = 1
Else
SAktie = 0
End If
End With
With Me.TextBox2.Value
If Len(strW) 0 Then
SWkn = 1
Else
SWkn = 0
End If
End With
'***** 1. Fall Aktie J / WKN N
If SAktie = 1 And SWkn = 0 And _
Me.ComboBox1.ListIndex = 0 And _
Me.ComboBox2.ListIndex = 0 And _
Me.ComboBox3.ListIndex = 0 And _
Me.ComboBox4.ListIndex = 0 Then
vArr = wb_TJ.Worksheets("Aktien").Range("A2:M" & letzteZeileArray).Value
ReDim vOut(0 To UBound(vArr, 2) - 1, 0 To UBound(vArr, 1) - 1)
For i = 1 To UBound(vArr, 1)
If vArr(i, 2) = strT Then
For j = 1 To UBound(vArr, 2)
vOut(j - 1, k) = vArr(i, j)
Next j
k = k + 1
End If
Next i
'*** Zuvor erstelltes Array, welches auf Tabelle1 geschrieben wurde, loeschen
Call LoeschenueberDatenfeld
'*** Inhalt der Listbox im Falle von vorheriger Suchen loeschen
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
'*** Listbox Inhalt aufarbeiten und in Tabelle 1 auswerfen
With ListBox1
If vOut(0, 0) "" Then
ReDim Preserve vOut(0 To UBound(vArr, 2) - 1, 0 To k - 1)
With wb_TJ.Worksheets("Tabelle1")
For z = 1 To k
For s = 1 To UBound(vArr, 2)
.Cells(z + 1, s).Value = vOut(0 + m, 0 + n)
m = m + 1
Next s
m = 0
n = n + 1
Next z
End With
'*** Einlesen des neuen Array aus Tabelle 1 und anzeige in der Listbox1
letzteZeileNeuesArray = wb_TJ.Worksheets("Tabelle1").Range("A" & Rows.Count).End(xlUp).Row
vArr = wb_TJ.Worksheets("Tabelle1").Range("A1:M" & letzteZeileNeuesArray).Value
.ColumnCount = 13
.ColumnHeads = True
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M" & letzteZeileNeuesArray
End If
End With
''***** 2. Fall Aktie N / WKN J
ElseIf SAktie = 0 And SWkn = 1 And _
Me.ComboBox1.ListIndex = 0 And _
Me.ComboBox2.ListIndex = 0 And _
Me.ComboBox3.ListIndex = 0 And _
Me.ComboBox4.ListIndex = 0 Then
vArr = wb_TJ.Worksheets("Aktien").Range("A2:M" & letzteZeileArray).Value
ReDim vOut(0 To UBound(vArr, 2) - 1, 0 To UBound(vArr, 1) - 1)
For i = 1 To UBound(vArr, 1)
If vArr(i, 3) = strW Then
For j = 1 To UBound(vArr, 2)
vOut(j - 1, k) = vArr(i, j)
Next j
k = k + 1
End If
Next i
Call LoeschenueberDatenfeld
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
With ListBox1
If vOut(0, 0) "" Then
ReDim Preserve vOut(0 To UBound(vArr, 2) - 1, 0 To k - 1)
With wb_TJ.Worksheets("Tabelle1")
For z = 1 To k
For s = 1 To UBound(vArr, 2)
.Cells(z + 1, s).Value = vOut(0 + m, 0 + n)
m = m + 1
Next s
m = 0
n = n + 1
Next z
End With
letzteZeileNeuesArray = wb_TJ.Worksheets("Tabelle1").Range("A" & Rows.Count).End(xlUp).Row
vArr = wb_TJ.Worksheets("Tabelle1").Range("A1:M" & letzteZeileNeuesArray).Value
.ColumnCount = 13
.ColumnHeads = True
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M" & letzteZeileNeuesArray
End If
End With
'***** 3. Fall Aktie J WKN N / Aktie J Option N
ElseIf SAktie = 1 And SWkn = 0 And _
Me.ComboBox1.ListIndex = 1 And _
Me.ComboBox2.ListIndex = 0 And _
Me.ComboBox3.ListIndex = 0 And _
Me.ComboBox4.ListIndex = 0 Then
vArr = wb_TJ.Worksheets("Aktien").Range("A2:M" & letzteZeileArray).Value
ReDim vOut(0 To UBound(vArr, 2) - 1, 0 To UBound(vArr, 1) - 1)
For i = 1 To UBound(vArr, 1)
If vArr(i, 2) = strT And vArr(i, 4) = CMAktie Then
For j = 1 To UBound(vArr, 2)
vOut(j - 1, k) = vArr(i, j)
Next j
k = k + 1
End If
Next i
Call LoeschenueberDatenfeld
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
With ListBox1
If vOut(0, 0) "" Then
ReDim Preserve vOut(0 To UBound(vArr, 2) - 1, 0 To k - 1)
With wb_TJ.Worksheets("Tabelle1")
For z = 1 To k
For s = 1 To UBound(vArr, 2)
.Cells(z + 1, s).Value = vOut(0 + m, 0 + n)
m = m + 1
Next s
m = 0
n = n + 1
Next z
End With
letzteZeileNeuesArray = wb_TJ.Worksheets("Tabelle1").Range("A" & Rows.Count).End(xlUp).Row
vArr = wb_TJ.Worksheets("Tabelle1").Range("A1:M" & letzteZeileNeuesArray).Value
.ColumnCount = 13
.ColumnHeads = True
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M" & letzteZeileNeuesArray
End If
End With
'***** 4. Fall Aktie N WKN J / Aktie J Option N
ElseIf SAktie = 0 Or SWkn = 1 And _
Me.ComboBox2.ListIndex = 0 And _
Me.ComboBox3.ListIndex = 0 And _
Me.ComboBox4.ListIndex = 0 And _
Me.ComboBox1.ListIndex = 1 Then ' Hier ensteht der Fehler im Falle von 2
vArr = wb_TJ.Worksheets("Aktien").Range("A2:M" & letzteZeileArray).Value
ReDim vOut(0 To UBound(vArr, 2) - 1, 0 To UBound(vArr, 1) - 1)
For i = 1 To UBound(vArr, 1)
If vArr(i, 3) = strW And vArr(i, 4) = CMAktie Then
For j = 1 To UBound(vArr, 2)
vOut(j - 1, k) = vArr(i, j)
Next j
k = k + 1
End If
Next i
Call LoeschenueberDatenfeld
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
With ListBox1
If vOut(0, 0) "" Then
ReDim Preserve vOut(0 To UBound(vArr, 2) - 1, 0 To k - 1)
With wb_TJ.Worksheets("Tabelle1")
For z = 1 To k
For s = 1 To UBound(vArr, 2)
.Cells(z + 1, s).Value = vOut(0 + m, 0 + n)
m = m + 1
Next s
m = 0
n = n + 1
Next z
End With
letzteZeileNeuesArray = wb_TJ.Worksheets("Tabelle1").Range("A" & Rows.Count).End(xlUp).Row
vArr = wb_TJ.Worksheets("Tabelle1").Range("A1:M" & letzteZeileNeuesArray).Value
.ColumnCount = 13
.ColumnHeads = True
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M" & letzteZeileNeuesArray
End If
End With
'***** 5. Fall Aktie N WKN J / Aktie N Option J
ElseIf SAktie = 0 And SWkn = 1 And _
Me.ComboBox1.ListIndex = 2 And _
Me.ComboBox2.ListIndex = 0 And _
Me.ComboBox3.ListIndex = 0 And _
Me.ComboBox4.ListIndex = 0 Then
vArr = wb_TJ.Worksheets("Aktien").Range("A2:M" & letzteZeileArray).Value
ReDim vOut(0 To UBound(vArr, 2) - 1, 0 To UBound(vArr, 1) - 1)
For i = 1 To UBound(vArr, 1)
If vArr(i, 3) = strW And vArr(i, 4) = CMOption Then
For j = 1 To UBound(vArr, 2)
vOut(j - 1, k) = vArr(i, j)
Next j
k = k + 1
End If
Next i
Call LoeschenueberDatenfeld
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
With ListBox1
If vOut(0, 0) "" Then
ReDim Preserve vOut(0 To UBound(vArr, 2) - 1, 0 To k - 1)
With wb_TJ.Worksheets("Tabelle1")
For z = 1 To k
For s = 1 To UBound(vArr, 2)
.Cells(z + 1, s).Value = vOut(0 + m, 0 + n)
m = m + 1
Next s
m = 0
n = n + 1
Next z
End With
letzteZeileNeuesArray = wb_TJ.Worksheets("Tabelle1").Range("A" & Rows.Count).End(xlUp).Row
vArr = wb_TJ.Worksheets("Tabelle1").Range("A1:M" & letzteZeileNeuesArray).Value
.ColumnCount = 13
.ColumnHeads = True
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M" & letzteZeileNeuesArray
End If
End With
Else
Call LoeschenueberDatenfeld
With Me.ListBox1
.RowSource = "'[Trading_Journal.xlsx]Tabelle1'!A2:M2"
End With
End If
Im Falle einer WKN Eingabe und der Combobox Auswahl 2 "Option", löst die IF Prüfung den Fall 4 aus, obwohl der combox.Listindex 2 lautet. Korrekterweise werden dann nur alle Aktienwerte ausgewiesen. Gewollt sind aber die Optionen.Kann sich jemand erklären woran das liegt?
Danke & VG