Hallo Hans,
Hinter den Userform liegt folgender Makro:
Private Sub CommandButton1_Click()
Dim PLZ, PLZ1, Ort, Ort1
Dim Zeile, Zeile1
On Error GoTo Ende
Ort = ListBox1
PLZ = TextBox1
Gewicht = TextBox2
Euro = TextBox3
Gibo = TextBox4
EP = TextBox9Laenge = TextBox5
Breite = TextBox6
Hoehe = TextBox7
Warenwert = TextBox8
If Euro = "" Then Euro = 0
If Gibo = "" Then Gibo = 0
If EP = "" Then EP = 0
If Laenge = "" Then Laenge = 0
If Breite = "" Then Breite = 0
If Hoehe = "" Then Hoehe = 0
If Warenwert = "" Then Warenwert = 0
Zeile = 0
If PLZ > 0 And PLZ < 2000 Then
Zeile = 2
Zeile1 = 1205
End If
If PLZ > 1999 And PLZ < 3000 Then
Zeile = 1206
Zeile1 = 1884
End If
If PLZ > 2999 And PLZ < 4000 Then
Zeile = 1885
Zeile1 = 2311
End If
If PLZ > 3999 And PLZ < 5000 Then
Zeile = 2312
Zeile1 = 3475
End If
If PLZ > 5999 And PLZ < 7000 Then
Zeile = 3476
Zeile1 = 4852
End If
If PLZ > 6999 And PLZ < 8000 Then
Zeile = 4853
Zeile1 = 5916
End If
If PLZ > 7999 And PLZ < 9000 Then
Zeile = 5917
Zeile1 = 6521
End If
If PLZ > 8999 And PLZ < 10000 Then
Zeile = 6522
Zeile1 = 7133
End If
If PLZ > 9999 And PLZ < 11000 Then
Zeile = 7134
Zeile1 = 7283
End If
If PLZ > 11999 And PLZ < 13000 Then
Zeile = 7284
Zeile1 = 7454
End If
If PLZ > 12999 And PLZ < 14000 Then
Zeile = 7455
Zeile1 = 7596
End If
If PLZ > 13999 And PLZ < 15000 Then
Zeile = 7597
Zeile1 = 8275
End If
If PLZ > 14999 And PLZ < 16000 Then
Zeile = 8276
Zeile1 = 9014
End If
If PLZ > 15999 And PLZ < 17000 Then
Zeile = 9015
Zeile1 = 9919
End If
If PLZ > 16999 And PLZ < 18000 Then
Zeile = 9920
Zeile1 = 11369
End If
If PLZ > 17999 And PLZ < 19000 Then
Zeile = 11370
Zeile1 = 12287
End If
If PLZ > 18999 And PLZ < 20000 Then
Zeile = 12288
Zeile = 13355
End If
If PLZ > 19999 And PLZ < 21000 Then
Zeile = 13356
Zeile1 = 13449
End If
If PLZ > 20999 And PLZ < 22000 Then
Zeile = 13450
Zeile1 = 14112
End If
If PLZ > 21999 And PLZ < 23000 Then
Zeile = 14113
Zeile1 = 14460
End If
If PLZ > 22999 And PLZ < 24000 Then
Zeile = 14461
Zeile1 = 15180
End If
If PLZ > 23999 And PLZ < 25000 Then
Zeile = 15181
Zeile1 = 16177
End If
If PLZ > 24999 And PLZ < 26000 Then
Zeile = 16178
Zeile1 = 16912
End If
If PLZ > 25999 And PLZ < 27000 Then
Zeile = 16913
Zeile1 = 17710
End If
If PLZ > 26999 And PLZ < 28000 Then
Zeile = 17711
Zeile1 = 18410
End If
If PLZ > 27999 And PLZ < 29000 Then
Zeile = 18411
Zeile1 = 18640
End If
If PLZ > 28999 And PLZ < 30000 Then
Zeile = 18641
Zeile1 = 19612
End If
If PLZ > 29999 And PLZ < 31000 Then
Zeile = 19613
Zeile1 = 19918
End If
If PLZ > 30999 And PLZ < 32000 Then
Zeile = 19919
Zeile1 = 20757
End If
If PLZ > 31999 And PLZ < 33000 Then
Zeile = 20758
Zeile1 = 21241
End If
If PLZ > 32999 And PLZ < 34000 Then
Zeile = 21242
Zeile1 = 21590
End If
If PLZ > 33999 And PLZ < 35000 Then
Zeile = 21591
Zeile1 = 21272
End If
If PLZ > 34999 And PLZ < 36000 Then
Zeile = 21273
Zeile1 = 22980
End If
If PLZ > 35999 And PLZ < 37000 Then
Zeile = 22981
Zeile1 = 23723
End If
If PLZ > 36999 And PLZ < 38000 Then
Zeile = 23724
Zeile1 = 24506
End If
If PLZ > 37999 And PLZ < 39000 Then
Zeile = 24507
Zeile1 = 25269
End If
If PLZ > 38999 And PLZ < 40000 Then
Zeile = 25270
Zeile1 = 26280
End If
If PLZ > 39999 And PLZ < 41000 Then
Zeile = 26281
Zeile1 = 26502
End If
If PLZ > 40999 And PLZ < 42000 Then
Zeile = 26503
Zeile1 = 26923
End If
If PLZ > 41999 And PLZ < 43000 Then
Zeile = 26924
Zeile1 = 27098
End If
If PLZ > 43999 And PLZ < 45000 Then
Zeile = 27099
Zeile1 = 27489
End If
If PLZ > 44999 And PLZ < 46000 Then
Zeile = 27490
Zeile1 = 27821
End If
If PLZ > 45999 And PLZ < 47000 Then
Zeile = 27822
Zeile1 = 28062
End If
If PLZ > 46999 And PLZ < 48000 Then
Zeile = 28063
Zeile1 = 28488
End If
If PLZ > 47999 And PLZ < 49000 Then
Zeile = 28489
Zeile1 = 28783
End If
If PLZ > 48999 And PLZ < 50000 Then
Zeile = 28784
Zeile1 = 29499
End If
If PLZ > 49999 And PLZ < 51000 Then
Zeile = 29500
Zeile1 = 29749
End If
If PLZ > 50999 And PLZ < 52000 Then
Zeile = 29750
Zeile1 = 30083
End If
If PLZ > 51999 And PLZ < 53000 Then
Zeile = 30084
Zeile1 = 30468
End If
If PLZ > 52999 And PLZ < 54000 Then
Zeile = 30069
Zeile1 = 31319
End If
If PLZ > 53999 And PLZ < 55000 Then
Zeile = 31320
Zeile1 = 32173
End If
If PLZ > 54999 And PLZ < 56000 Then
Zeile = 32174
Zeile1 = 32689
End If
If PLZ > 55999 And PLZ < 57000 Then
Zeile = 32690
Zeile1 = 33568
End If
If PLZ > 56999 And PLZ < 58000 Then
Zeile = 33569
Zeile1 = 34196
End If
If PLZ > 57999 And PLZ < 59000 Then
Zeile = 34197
Zeile1 = 34577
End If
If PLZ > 58999 And PLZ < 60000 Then
Zeile = 34578
Zeile1 = 35096
End If
If PLZ > 59999 And PLZ < 61000 Then
Zeile = 35097
Zeile1 = 35240
End If
If PLZ > 60999 And PLZ < 62000 Then
Zeile = 35241
Zeile1 = 35401
End If
If PLZ > 62999 And PLZ < 64000 Then
Zeile = 35402
Zeile1 = 35852
End If
If PLZ > 63999 And PLZ < 65000 Then
Zeile = 35853
Zeile1 = 36176
End If
If PLZ > 64999 And PLZ < 66000 Then
Zeile = 36177
Zeile1 = 36536
End If
If PLZ > 65999 And PLZ < 67000 Then
Zeile = 36537
Zeile1 = 37236
End If
If PLZ > 66999 And PLZ < 68000 Then
Zeile = 37237
Zeile1 = 37754
End If
If PLZ > 67999 And PLZ < 69000 Then
Zeile = 37755
Zeile1 = 37858
End If
If PLZ > 68999 And PLZ < 70000 Then
Zeile = 37859
Zeile1 = 38012
End If
If PLZ > 69999 And PLZ < 71000 Then
Zeile = 38013
Zeile1 = 38180
End If
If PLZ > 70999 And PLZ < 72000 Then
Zeile = 38181
Zeile1 = 38458
End If
If PLZ > 71999 And PLZ < 73000 Then
Zeile = 38459
Zeile1 = 39050
End If
If PLZ > 72999 And PLZ < 74000 Then
Zeile = 39051
Zeile1 = 39542
End If
If PLZ > 73999 And PLZ < 75000 Then
Zeile = 39543
Zeile1 = 40172
End If
If PLZ > 74999 And PLZ < 76000 Then
Zeile = 40173
Zeile1 = 40405
End If
If PLZ > 75999 And PLZ < 77000 Then
Zeile = 40406
Zeile1 = 40821
End If
If PLZ > 76999 And PLZ < 78000 Then
Zeile = 40822
Zeile1 = 41036
End If
If PLZ > 77999 And PLZ < 79000 Then
Zeile = 41037
Zeile1 = 41422
End If
If PLZ > 78999 And PLZ < 80000 Then
Zeile = 41423
Zeile1 = 42008
End If
If PLZ > 79999 And PLZ < 81000 Then
Zeile = 42009
Zeile1 = 42168
End If
If PLZ > 80999 And PLZ < 82000 Then
Zeile = 42167
Zeile1 = 42322
End If
If PLZ > 81999 And PLZ < 83000 Then
Zeile = 42323
Zeile1 = 42694
End If
If PLZ > 82999 And PLZ < 84000 Then
Zeile = 42695
Zeile1 = 43719
End If
If PLZ > 83999 And PLZ < 85000 Then
Zeile = 43720
Zeile1 = 44702
End If
If PLZ > 84999 And PLZ < 86000 Then
Zeile = 44703
Zeile1 = 45445
End If
If PLZ > 85999 And PLZ < 87000 Then
Zeile = 45446
Zeile1 = 46329
End If
If PLZ > 86999 And PLZ < 88000 Then
Zeile = 46330
Zeile1 = 46734
End If
If PLZ > 87999 And PLZ < 89000 Then
Zeile = 46735
Zeile1 = 47413
End If
If PLZ > 88999 And PLZ < 90000 Then
Zeile = 47414
Zeile1 = 47916
End If
If PLZ > 89999 And PLZ < 91000 Then
Zeile = 47917
Zeile1 = 48283
End If
If PLZ > 90999 And PLZ < 92000 Then
Zeile = 48284
Zeile1 = 49435
End If
If PLZ > 91999 And PLZ < 93000 Then
Zeile = 49436
Zeile1 = 50091
End If
If PLZ > 92999 And PLZ < 94000 Then
Zeile = 50092
Zeile1 = 50846
End If
If PLZ > 93999 And PLZ < 95000 Then
Zeile = 50847
Zeile1 = 51779
End If
If PLZ > 94999 And PLZ < 96000 Then
Zeile = 51780
Zeile1 = 52343
End If
If PLZ > 95999 And PLZ < 97000 Then
Zeile = 52344
Zeile1 = 53071
End If
If PLZ > 96999 And PLZ < 98000 Then
Zeile = 53072
Zeile1 = 54158
End If
If PLZ > 97999 And PLZ < 99000 Then
Zeile = 54159
Zeile1 = 54552
End If
If PLZ > 98999 And PLZ < 100000 Then
Zeile = 54553
Zeile1 = 55533
End If
treffer = 0
While treffer = 0
ze_PLZ = "A" & Zeile
ze_ort = "F" & Zeile
PLZ1 = Sheets("KM-Matrix").Range(ze_PLZ)
Ort1 = Sheets("KM-Matrix").Range(ze_ort)
If PLZ = PLZ1 And Ort = Ort1 Then
zeile_treffer = Zeile
treffer = 1
End If
If Zeile > Zeile1 Then
treffer = 1
MsgBox "Ort nicht gefunden"
End If
Zeile = Zeile + 1
Wend
Ze_KM = "E" & zeile_treffer
ze_bsl = "D" & zeile_treffer
bsl = Sheets("KM-Matrix").Range(ze_bsl)
km = Sheets("KM-Matrix").Range(Ze_KM)
Sheets("Definitionen").Select
Range("B7").Select
ActiveCell.FormulaR1C1 = km
Range("B13").Select
ActiveCell.FormulaR1C1 = bsl
Range("B1").Select
ActiveCell.FormulaR1C1 = PLZ
Range("C1").Select
ActiveCell.FormulaR1C1 = Ort
'Ermittlung Frachtpflichtiges Gewicht
F_euro = Sheets("Definitionen").Range("B21") * 1
F_EP = Sheets("Definitionen").Range("B22") * 1
F_GP = Sheets("Definitionen").Range("B23") * 1
F_LM = Sheets("Definitionen").Range("B24") * 1
F_CBM = Sheets("Definitionen").Range("B25") * 1
KG_FP = Euro * F_euro
KG_GP = Gibo * F_GP
KG_EP = EP * F_EP
KG_Lademittel = KG_FP + KG_EP + KG_GP
If Hoehe = 0 Then
LM = Laenge / 100 * Breite / 100 / 2.4
KG_LM = LM * F_LM
Else
cbm = Laenge / 100 * Breite / 100 * Hoehe / 100
KG_CBM = cbm * F_CBM
End If
Sheets("Berechnung").Select
Range("B20").Select
ActiveCell.FormulaR1C1 = Gewicht
Range("B21").Select
ActiveCell.FormulaR1C1 = KG_Lademittel
Range("B22").Select
ActiveCell.FormulaR1C1 = KG_LM
Range("B23").Select
ActiveCell.FormulaR1C1 = KG_CBM
If OptionButton3 = "Wahr" Then GoTo NL8
If OptionButton4 = "Wahr" Then GoTo NL10
If OptionButton5 = "Wahr" Then GoTo NL12
If OptionButton6 = "Wahr" Then GoTo NL8
If PLZ = 32549 Or PLZ = 37581 Or PLZ = 58513 Or PLZ = 59368 Or PLZ = 71404 Or PLZ = 40880 _
Or PLZ = 71384 Or PLZ = 68161 Or PLZ = 47906 Or PLZ = 89077 Or PLZ = 82131 Or PLZ = 82041 _
Then GoTo spezial1
Euro1 = Euro * 1
Gibo1 = Gibo * 1
VP = Euro1 + Gibo1
If PLZ = "07426" And VP < 5 And VP > 0 Then GoTo spezial2
Kundensatz = Sheets("Berechnung").Range("C2")
Marge = Sheets("Berechnung").Range("C3")
Rolle = Sheets("Berechnung").Range("C8")
If SLVS = 0 Then SLVS_Prämie = "" Else SLVS_Prämie = Sheets("Berechnung").Range("C17")
If OptionButton2 = "Wahr" Then NLND = Sheets("Berechnung").Range("C11") Else NLND = ""
If Kundensatz = "" Then E1 = 0 Else E1 = Kundensatz * 1
If Marge = "" Then E2 = 0 Else E2 = Marge * 1
If Rolle = "" Then E3 = 0 Else E3 = Rolle * 1
If SLVS_Prämie = "" Then E14 = 0 Else E14 = SLVS_Prämie * 1
If NLND = "" Then E15 = 0 Else E15 = NLND * 1
Gesamt = E1 + E2 + E3 + E14 + E15
TextBox20 = Gesamt
PLZ_Ort = PLZ & " " & Ort
If OptionButton2 = "Wahr" Then UserForm3.Show Else UserForm2.Show
GoTo Ende:
NL_NextDay:
MsgBox "NL Next Day"
GoTo Ende
NL8:
If Euro > 3 Or Gibo > 6 Then
MsgBox "Nightline Plus nur bis 3 Euro oder 6 Gibo zulässig. Bitte Preis anfragen"
GoTo Ende
End If
UserForm4.Show
GoTo Ende
NL10:
If Euro > 3 Or Gibo > 6 Then
MsgBox "Nightline Plus nur bis 3 Euro oder 6 Gibo zulässig. Bitte Preis anfragen"
GoTo Ende
End If
UserForm5.Show
GoTo Ende
NL12:
If Euro > 3 Or Gibo > 6 Then
MsgBox "Nightline Plus nur bis 3 Euro oder 6 Gibo zulässig. Bitte Preis anfragen"
GoTo Ende
End If
UserForm6.Show
GoTo Ende
spezial1:
If OptionButton2 = "Wahr" Then NLND = Sheets("Berechnung").Range("C11") Else NLND = "0"
Sheets("Spezial").Select
Range("F5").Select
ActiveCell.FormulaR1C1 = NLND
UserForm7.Show
GoTo Ende
spezial2:
If OptionButton2 = "Wahr" Then NLND = Sheets("Berechnung").Range("C11") Else NLND = 0
Sheets("Spezial").Select
Range("G18").Select
ActiveCell.FormulaR1C1 = NLND
Sheets("Spezial").Select
Range("G16").Select
ActiveCell.FormulaR1C1 = VP
UserForm8.Show
GoTo Ende
Ende:
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
Workbooks("Kalkulation.xls").Close SaveChanges:=False
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim PLZ, PLZ1
Dim Zeile, Zeile1
Dim TestFeld(124, 0)
On Error GoTo Ende
PLZ = TextBox1
Zeile = 0
If PLZ > 0 And PLZ < 2000 Then
Zeile = 2
Zeile1 = 1205
End If
If PLZ > 1999 And PLZ < 3000 Then
Zeile = 1206
Zeile1 = 1884
End If
If PLZ > 2999 And PLZ < 4000 Then
Zeile = 1885
Zeile1 = 2311
End If
If PLZ > 3999 And PLZ < 5000 Then
Zeile = 2312
Zeile1 = 3475
End If
If PLZ > 5999 And PLZ < 7000 Then
Zeile = 3476
Zeile1 = 4852
End If
If PLZ > 6999 And PLZ < 8000 Then
Zeile = 4853
Zeile1 = 5916
End If
If PLZ > 7999 And PLZ < 9000 Then
Zeile = 5917
Zeile1 = 6521
End If
If PLZ > 8999 And PLZ < 10000 Then
Zeile = 6522
Zeile1 = 7133
End If
If PLZ > 9999 And PLZ < 11000 Then
Zeile = 7134
Zeile1 = 7283
End If
If PLZ > 11999 And PLZ < 13000 Then
Zeile = 7284
Zeile1 = 7454
End If
If PLZ > 12999 And PLZ < 14000 Then
Zeile = 7455
Zeile1 = 7596
End If
If PLZ > 13999 And PLZ < 15000 Then
Zeile = 7597
Zeile1 = 8275
End If
If PLZ > 14999 And PLZ < 16000 Then
Zeile = 8276
Zeile1 = 9014
End If
If PLZ > 15999 And PLZ < 17000 Then
Zeile = 9015
Zeile1 = 9919
End If
If PLZ > 16999 And PLZ < 18000 Then
Zeile = 9920
Zeile1 = 11369
End If
If PLZ > 17999 And PLZ < 19000 Then
Zeile = 11370
Zeile1 = 12287
End If
If PLZ > 18999 And PLZ < 20000 Then
Zeile = 12288
Zeile = 13355
End If
If PLZ > 19999 And PLZ < 21000 Then
Zeile = 13356
Zeile1 = 13449
End If
If PLZ > 20999 And PLZ < 22000 Then
Zeile = 13450
Zeile1 = 14112
End If
If PLZ > 21999 And PLZ < 23000 Then
Zeile = 14113
Zeile1 = 14460
End If
If PLZ > 22999 And PLZ < 24000 Then
Zeile = 14461
Zeile1 = 15180
End If
If PLZ > 23999 And PLZ < 25000 Then
Zeile = 15181
Zeile1 = 16177
End If
If PLZ > 24999 And PLZ < 26000 Then
Zeile = 16178
Zeile1 = 16912
End If
If PLZ > 25999 And PLZ < 27000 Then
Zeile = 16913
Zeile1 = 17710
End If
If PLZ > 26999 And PLZ < 28000 Then
Zeile = 17711
Zeile1 = 18410
End If
If PLZ > 27999 And PLZ < 29000 Then
Zeile = 18411
Zeile1 = 18640
End If
If PLZ > 28999 And PLZ < 30000 Then
Zeile = 18641
Zeile1 = 19612
End If
If PLZ > 29999 And PLZ < 31000 Then
Zeile = 19613
Zeile1 = 19918
End If
If PLZ > 30999 And PLZ < 32000 Then
Zeile = 19919
Zeile1 = 20757
End If
If PLZ > 31999 And PLZ < 33000 Then
Zeile = 20758
Zeile1 = 21241
End If
If PLZ > 32999 And PLZ < 34000 Then
Zeile = 21242
Zeile1 = 21590
End If
If PLZ > 33999 And PLZ < 35000 Then
Zeile = 21591
Zeile1 = 21272
End If
If PLZ > 34999 And PLZ < 36000 Then
Zeile = 21273
Zeile1 = 22980
End If
If PLZ > 35999 And PLZ < 37000 Then
Zeile = 22981
Zeile1 = 23723
End If
If PLZ > 36999 And PLZ < 38000 Then
Zeile = 23724
Zeile1 = 24506
End If
If PLZ > 37999 And PLZ < 39000 Then
Zeile = 24507
Zeile1 = 25269
End If
If PLZ > 38999 And PLZ < 40000 Then
Zeile = 25270
Zeile1 = 26280
End If
If PLZ > 39999 And PLZ < 41000 Then
Zeile = 26281
Zeile1 = 26502
End If
If PLZ > 40999 And PLZ < 42000 Then
Zeile = 26503
Zeile1 = 26923
End If
If PLZ > 41999 And PLZ < 43000 Then
Zeile = 26924
Zeile1 = 27098
End If
If PLZ > 43999 And PLZ < 45000 Then
Zeile = 27099
Zeile1 = 27489
End If
If PLZ > 44999 And PLZ < 46000 Then
Zeile = 27490
Zeile1 = 27821
End If
If PLZ > 45999 And PLZ < 47000 Then
Zeile = 27822
Zeile1 = 28062
End If
If PLZ > 46999 And PLZ < 48000 Then
Zeile = 28063
Zeile1 = 28488
End If
If PLZ > 47999 And PLZ < 49000 Then
Zeile = 28489
Zeile1 = 28783
End If
If PLZ > 48999 And PLZ < 50000 Then
Zeile = 28784
Zeile1 = 29499
End If
If PLZ > 49999 And PLZ < 51000 Then
Zeile = 29500
Zeile1 = 29749
End If
If PLZ > 50999 And PLZ < 52000 Then
Zeile = 29750
Zeile1 = 30083
End If
If PLZ > 51999 And PLZ < 53000 Then
Zeile = 30084
Zeile1 = 30468
End If
If PLZ > 52999 And PLZ < 54000 Then
Zeile = 30069
Zeile1 = 31319
End If
If PLZ > 53999 And PLZ < 55000 Then
Zeile = 31320
Zeile1 = 32173
End If
If PLZ > 54999 And PLZ < 56000 Then
Zeile = 32174
Zeile1 = 32689
End If
If PLZ > 55999 And PLZ < 57000 Then
Zeile = 32690
Zeile1 = 33568
End If
If PLZ > 56999 And PLZ < 58000 Then
Zeile = 33569
Zeile1 = 34196
End If
If PLZ > 57999 And PLZ < 59000 Then
Zeile = 34197
Zeile1 = 34577
End If
If PLZ > 58999 And PLZ < 60000 Then
Zeile = 34578
Zeile1 = 35096
End If
If PLZ > 59999 And PLZ < 61000 Then
Zeile = 35097
Zeile1 = 35240
End If
If PLZ > 60999 And PLZ < 62000 Then
Zeile = 35241
Zeile1 = 35401
End If
If PLZ > 62999 And PLZ < 64000 Then
Zeile = 35402
Zeile1 = 35852
End If
If PLZ > 63999 And PLZ < 65000 Then
Zeile = 35853
Zeile1 = 36176
End If
If PLZ > 64999 And PLZ < 66000 Then
Zeile = 36177
Zeile1 = 36536
End If
If PLZ > 65999 And PLZ < 67000 Then
Zeile = 36537
Zeile1 = 37236
End If
If PLZ > 66999 And PLZ < 68000 Then
Zeile = 37237
Zeile1 = 37754
End If
If PLZ > 67999 And PLZ < 69000 Then
Zeile = 37755
Zeile1 = 37858
End If
If PLZ > 68999 And PLZ < 70000 Then
Zeile = 37859
Zeile1 = 38012
End If
If PLZ > 69999 And PLZ < 71000 Then
Zeile = 38013
Zeile1 = 38180
End If
If PLZ > 70999 And PLZ < 72000 Then
Zeile = 38181
Zeile1 = 38458
End If
If PLZ > 71999 And PLZ < 73000 Then
Zeile = 38459
Zeile1 = 39050
End If
If PLZ > 72999 And PLZ < 74000 Then
Zeile = 39051
Zeile1 = 39542
End If
If PLZ > 73999 And PLZ < 75000 Then
Zeile = 39543
Zeile1 = 40172
End If
If PLZ > 74999 And PLZ < 76000 Then
Zeile = 40173
Zeile1 = 40405
End If
If PLZ > 75999 And PLZ < 77000 Then
Zeile = 40406
Zeile1 = 40821
End If
If PLZ > 76999 And PLZ < 78000 Then
Zeile = 40822
Zeile1 = 41036
End If
If PLZ > 77999 And PLZ < 79000 Then
Zeile = 41037
Zeile1 = 41422
End If
If PLZ > 78999 And PLZ < 80000 Then
Zeile = 41423
Zeile1 = 42008
End If
If PLZ > 79999 And PLZ < 81000 Then
Zeile = 42009
Zeile1 = 42168
End If
If PLZ > 80999 And PLZ < 82000 Then
Zeile = 42167
Zeile1 = 42322
End If
If PLZ > 81999 And PLZ < 83000 Then
Zeile = 42323
Zeile1 = 42694
End If
If PLZ > 82999 And PLZ < 84000 Then
Zeile = 42695
Zeile1 = 43719
End If
If PLZ > 83999 And PLZ < 85000 Then
Zeile = 43720
Zeile1 = 44702
End If
If PLZ > 84999 And PLZ < 86000 Then
Zeile = 44703
Zeile1 = 45445
End If
If PLZ > 85999 And PLZ < 87000 Then
Zeile = 45446
Zeile1 = 46329
End If
If PLZ > 86999 And PLZ < 88000 Then
Zeile = 46330
Zeile1 = 46734
End If
If PLZ > 87999 And PLZ < 89000 Then
Zeile = 46735
Zeile1 = 47413
End If
If PLZ > 88999 And PLZ < 90000 Then
Zeile = 47414
Zeile1 = 47916
End If
If PLZ > 89999 And PLZ < 91000 Then
Zeile = 47917
Zeile1 = 48283
End If
If PLZ > 90999 And PLZ < 92000 Then
Zeile = 48284
Zeile1 = 49435
End If
If PLZ > 91999 And PLZ < 93000 Then
Zeile = 49436
Zeile1 = 50091
End If
If PLZ > 92999 And PLZ < 94000 Then
Zeile = 50092
Zeile1 = 50846
End If
If PLZ > 93999 And PLZ < 95000 Then
Zeile = 50847
Zeile1 = 51779
End If
If PLZ > 94999 And PLZ < 96000 Then
Zeile = 51780
Zeile1 = 52343
End If
If PLZ > 95999 And PLZ < 97000 Then
Zeile = 52344
Zeile1 = 53071
End If
If PLZ > 96999 And PLZ < 98000 Then
Zeile = 53072
Zeile1 = 54158
End If
If PLZ > 97999 And PLZ < 99000 Then
Zeile = 54159
Zeile1 = 54552
End If
If PLZ > 98999 And PLZ < 100000 Then
Zeile = 54553
Zeile1 = 55533
End If
ListBox1.ColumnCount = 1
Nummer = 1
treffer = 0
While Zeile <> Zeile1
ze_PLZ = "A" & Zeile
PLZ1 = Sheets("KM-Matrix").Range(ze_PLZ)
If PLZ = PLZ1 And treffer = 1 Then
Zeile_Ende = Zeile
ze_ort = "F" & Zeile
TestFeld(Nummer, 0) = Sheets("KM-Matrix").Range(ze_ort)
Nummer = Nummer + 1
End If
If PLZ = PLZ1 And treffer = 0 Then
zeile_Anfang = Zeile
treffer = 1
ze_ort = "F" & Zeile
TestFeld(Nummer, 0) = Sheets("KM-Matrix").Range(ze_ort)
Nummer = Nummer + 1
End If
Zeile = Zeile + 1
Wend
ListBox1.List() = TestFeld
Ende:
End Sub
Und im Modul1 wird folgendes ausgeführt:
Sub auto_open()
UserForm1.Show
End Sub
Hilft das weiter?
Was ich nicht ganz verstehe ist, dass es auf einem Rechner mit Excel 2000 läuft und auf dem anderen die Fehlermeldung kommt.
Trotzdem besten Dank für die Hilfe.
Gruss
Stefan