AW: Prozentuale Verteilung ueber UserForm wiedergeben
08.07.2009 16:42:38
ransi
HAllo Timmy
v.a. wie ich das auf meine 30000 Zeilen uebertragen koennte...
Das ist kein Problem.
Der Code frisst auch noch mehr.
Wichtig ist das dein Bereich ganz klar abgegrenzt ist.
Komplett leere Zeile drüber und komplett leere Spalte rechts.
Sonst verschluckt sich die CurrentRegion.
Sheet1
| | A | B | C | D | E | F | G |
| 4 | | | | | | | |
| 5 | COMPLAINT_ID | PROBLEM | PUM | ACTION_CODE | COMPLAINT_TYPE | STATUS | |
| 6 | 34029 | Replacement Parts Request | 161 | Notified Customer | SERVICE | CLOSED | |
| 7 | 34081 | Replacement Parts Request | 169 | Sent Replacement Parts | SERVICE | CLOSED | |
| 8 | 34130 | Overage | 162 | Other | SERVICE | CLOSED | |
| 9 | 34130 | Shortage | 162 | Credit | SERVICE | CLOSED | |
| 10 | 34135 | Defective | 165 | Credit & Re-Order | SERVICE | CLOSED | |
| 11 | 34136 | Shipping Charge | 163 | Credit | SERVICE | CLOSED | |
| 12 | 34137 | Defective | 163 | Credit | SERVICE | CLOSED | |
| 13 | 34138 | Missing Parts | 163 | RMA & Call Tag w/New Order | SERVICE | CLOSED | |
| 14 | 34139 | Defective | 176 | Credit | SERVICE | CLOSED | |
| 15 | 34140 | Damage Carrier | 161 | Credit | SERVICE | CLOSED | |
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
2 Kleine Korrekturen:
' **********************************************************************
' Modul: UserForm1 Typ: Userform
' **********************************************************************
Option Explicit
Dim problem As Object
Dim Plum As Object
Dim L As Long
Private Sub ComboBox1_Change()
Label3 = problem(ComboBox1.Value) / (L - 1) * 100 & " %"
End Sub
Private Sub ComboBox2_Change()
Label4 = Plum(ComboBox2.Value) / (L - 1) * 100 & " %"
End Sub
Private Sub UserForm_Initialize()
Dim arr
Dim Pr
Dim pl
Set problem = CreateObject("Scripting.Dictionary")
Set Plum = CreateObject("Scripting.Dictionary")
arr = Tabelle1.Range("A5").CurrentRegion
For L = 2 To UBound(arr)
problem(CStr(arr(L, 2))) = problem(CStr(arr(L, 2))) + 1
Plum(CStr(arr(L, 3))) = Plum(CStr(arr(L, 3))) + 1
Next
ComboBox1.List = problem.keys
ComboBox2.List = Plum.keys
End Sub
ransi