geht das kürzer?
10.01.2005 18:43:15
Sebastian
Bis jetzt sieht der Code folgendermaßen aus:
If txtPreiscode = Range("BD3") Then
Range("BD5") = txt11
Range("BD6") = txt12
Range("BD7") = txt13
Range("BD8") = txt14
Range("BD9") = txt15
Range("BE5") = txt21
Range("BE6") = txt22
Range("BE7") = txt23
Range("BE8") = txt24
Range("BE9") = txt25
Range("BF5") = txt31
Range("BF6") = txt32
Range("BF7") = txt33
Range("BF8") = txt34
Range("BF9") = txt35
Range("BG5") = txt41
Range("BG6") = txt42
Range("BG7") = txt43
Range("BG8") = txt44
Range("BG9") = txt45
Range("BH5") = txt51
Range("BH6") = txt52
Range("BH7") = txt53
Range("BH8") = txt54
Range("BH9") = txt55
Range("BI5") = txt61
Range("BI6") = txt62
Range("BI7") = txt63
Range("BI8") = txt64
Range("BI9") = txt65
Range("BJ5") = txt71
Range("BJ6") = txt72
Range("BJ7") = txt73
Range("BJ8") = txt74
Range("BJ9") = txt75
Range("BK5") = txt81
Range("BK6") = txt82
Range("BK7") = txt83
Range("BK8") = txt84
Range("BK9") = txt85
Range("BL5") = txt91
Range("BL6") = txt92
Range("BL7") = txt93
Range("BL8") = txt94
Range("BL9") = txt95
Range("BM5") = txt101
Range("BM6") = txt102
Range("BM7") = txt103
Range("BM8") = txt104
Range("BM9") = txt105
End If
If txtPreiscode = Range("BD12") Then
Range("BD14") = txt11
Range("BD15") = txt12
Range("BD16") = txt13
Range("BD17") = txt14
Range("BD18") = txt15
Range("BE14") = txt21
Range("BE15") = txt22
Range("BE16") = txt23
Range("BE17") = txt24
Range("BE18") = txt25
Range("BF14") = txt31
Range("BF15") = txt32
Range("BF16") = txt33
Range("BF17") = txt34
Range("BF18") = txt35
Range("BG14") = txt41
Range("BG15") = txt42
Range("BG16") = txt43
Range("BG17") = txt44
Range("BG18") = txt45
Range("BH14") = txt51
Range("BH15") = txt52
Range("BH16") = txt53
Range("BH17") = txt54
Range("BH18") = txt55
Range("BI14") = txt61
Range("BI15") = txt62
Range("BI16") = txt63
Range("BI17") = txt64
Range("BI18") = txt65
Range("BJ14") = txt71
Range("BJ15") = txt72
Range("BJ16") = txt73
Range("BJ17") = txt74
Range("BJ18") = txt75
Range("BK14") = txt81
Range("BK15") = txt82
Range("BK16") = txt83
Range("BK17") = txt84
Range("BK18") = txt85
Range("BL14") = txt91
Range("BL15") = txt92
Range("BL16") = txt93
Range("BL17") = txt94
Range("BL18") = txt95
Range("BM14") = txt101
Range("BM15") = txt102
Range("BM16") = txt103
Range("BM17") = txt104
Range("BM18") = txt105
End If
If txtPreiscode = Range("BD21") Then
Range("BD23") = txt11
Range("BD24") = txt12
Range("BD25") = txt13
Range("BD26") = txt14
Range("BD27") = txt15
Range("BE23") = txt21
Range("BE24") = txt22
Range("BE25") = txt23
Range("BE26") = txt24
Range("BE27") = txt25
Range("BF23") = txt31
Range("BF24") = txt32
Range("BF25") = txt33
Range("BF26") = txt34
Range("BF27") = txt35
Range("BG23") = txt41
Range("BG24") = txt42
Range("BG25") = txt43
Range("BG26") = txt44
Range("BG27") = txt45
Range("BH23") = txt51
Range("BH24") = txt52
Range("BH25") = txt53
Range("BH26") = txt54
Range("BH27") = txt55
Range("BI23") = txt61
Range("BI24") = txt62
Range("BI25") = txt63
Range("BI26") = txt64
Range("BI27") = txt65
Range("BJ23") = txt71
Range("BJ24") = txt72
Range("BJ25") = txt73
Range("BJ26") = txt74
Range("BJ27") = txt75
Range("BK23") = txt81
Range("BK24") = txt82
Range("BK25") = txt83
Range("BK26") = txt84
Range("BK27") = txt85
Range("BL23") = txt91
Range("BL24") = txt92
Range("BL25") = txt93
Range("BL26") = txt94
Range("BL27") = txt95
Range("BM23") = txt101
Range("BM24") = txt102
Range("BM25") = txt103
Range("BM26") = txt104
Range("BM27") = txt105
End If
If txtPreiscode = Range("BD30") Then
Range("BD32") = txt11
Range("BD33") = txt12
Range("BD34") = txt13
Range("BD35") = txt14
Range("BD36") = txt15
Range("BE32") = txt21
Range("BE33") = txt22
Range("BE34") = txt23
Range("BE35") = txt24
Range("BE36") = txt25
Range("BF32") = txt31
Range("BF33") = txt32
Range("BF34") = txt33
Range("BF35") = txt34
Range("BF36") = txt35
Range("BG32") = txt41
Range("BG33") = txt42
Range("BG34") = txt43
Range("BG35") = txt44
Range("BG36") = txt45
Range("BH32") = txt51
Range("BH33") = txt52
Range("BH34") = txt53
Range("BH35") = txt54
Range("BH36") = txt55
Range("BI32") = txt61
Range("BI33") = txt62
Range("BI34") = txt63
Range("BI35") = txt64
Range("BI36") = txt65
Range("BJ32") = txt71
Range("BJ33") = txt72
Range("BJ34") = txt73
Range("BJ35") = txt74
Range("BJ36") = txt75
Range("BK32") = txt81
Range("BK33") = txt82
Range("BK34") = txt83
Range("BK35") = txt84
Range("BK36") = txt85
Range("BL32") = txt91
Range("BL33") = txt92
Range("BL34") = txt93
Range("BL35") = txt94
Range("BL36") = txt95
Range("BM32") = txt101
Range("BM33") = txt102
Range("BM34") = txt103
Range("BM35") = txt104
Range("BM36") = txt105
End If
If txtPreiscode = Range("BD39") Then
Range("BD41") = txt11
Range("BD42") = txt12
Range("BD43") = txt13
Range("BD44") = txt14
Range("BD45") = txt15
Range("BE41") = txt21
Range("BE42") = txt22
Range("BE43") = txt23
Range("BE44") = txt24
Range("BE45") = txt25
Range("BF41") = txt31
Range("BF42") = txt32
Range("BF43") = txt33
Range("BF44") = txt34
Range("BF45") = txt35
Range("BG41") = txt41
Range("BG42") = txt42
Range("BG43") = txt43
Range("BG44") = txt44
Range("BG45") = txt45
Range("BH41") = txt51
Range("BH42") = txt52
Range("BH43") = txt53
Range("BH44") = txt54
Range("BH45") = txt55
Range("BI41") = txt61
Range("BI42") = txt62
Range("BI43") = txt63
Range("BI44") = txt64
Range("BI45") = txt65
Range("BJ41") = txt71
Range("BJ42") = txt72
Range("BJ43") = txt73
Range("BJ44") = txt74
Range("BJ45") = txt75
Range("BK41") = txt81
Range("BK42") = txt82
Range("BK43") = txt83
Range("BK44") = txt84
Range("BK45") = txt85
Range("BL41") = txt91
Range("BL42") = txt92
Range("BL43") = txt93
Range("BL44") = txt94
Range("BL45") = txt95
Range("BM41") = txt101
Range("BM42") = txt102
Range("BM43") = txt103
Range("BM44") = txt104
Range("BM45") = txt105
End If
If txtPreiscode = Range("BD48") Then
Range("BD50") = txt11
Range("BD51") = txt12
Range("BD52") = txt13
Range("BD53") = txt14
Range("BD54") = txt15
Range("BE50") = txt21
Range("BE51") = txt22
Range("BE52") = txt23
Range("BE53") = txt24
Range("BE54") = txt25
Range("BF50") = txt31
Range("BF51") = txt32
Range("BF52") = txt33
Range("BF53") = txt34
Range("BF54") = txt35
Range("BG50") = txt41
Range("BG51") = txt42
Range("BG52") = txt43
Range("BG53") = txt44
Range("BG54") = txt45
Range("BH50") = txt51
Range("BH51") = txt52
Range("BH52") = txt53
Range("BH53") = txt54
Range("BH54") = txt55
Range("BI50") = txt61
Range("BI51") = txt62
Range("BI52") = txt63
Range("BI53") = txt64
Range("BI54") = txt65
Range("BJ50") = txt71
Range("BJ51") = txt72
Range("BJ52") = txt73
Range("BJ53") = txt74
Range("BJ54") = txt75
Range("BK50") = txt81
Range("BK51") = txt82
Range("BK52") = txt83
Range("BK53") = txt84
Range("BK54") = txt85
Range("BL50") = txt91
Range("BL51") = txt92
Range("BL52") = txt93
Range("BL53") = txt94
Range("BL54") = txt95
Range("BM50") = txt101
Range("BM51") = txt102
Range("BM52") = txt103
Range("BM53") = txt104
Range("BM54") = txt105
End If
If txtPreiscode = Range("BD57") Then
Range("BD59") = txt11
Range("BD60") = txt12
Range("BD61") = txt13
Range("BD62") = txt14
Range("BD63") = txt15
Range("BE59") = txt21
Range("BE60") = txt22
Range("BE61") = txt23
Range("BE62") = txt24
Range("BE63") = txt25
Range("BF59") = txt31
Range("BF60") = txt32
Range("BF61") = txt33
Range("BF62") = txt34
Range("BF63") = txt35
Range("BG59") = txt41
Range("BG60") = txt42
Range("BG61") = txt43
Range("BG62") = txt44
Range("BG63") = txt45
Range("BH59") = txt51
Range("BH60") = txt52
Range("BH61") = txt53
Range("BH62") = txt54
Range("BH63") = txt55
Range("BI59") = txt61
Range("BI60") = txt62
Range("BI61") = txt63
Range("BI62") = txt64
Range("BI63") = txt65
Range("BJ59") = txt71
Range("BJ60") = txt72
Range("BJ61") = txt73
Range("BJ62") = txt74
Range("BJ63") = txt75
Range("BK59") = txt81
Range("BK60") = txt82
Range("BK61") = txt83
Range("BK62") = txt84
Range("BK63") = txt85
Range("BL59") = txt91
Range("BL60") = txt92
Range("BL61") = txt93
Range("BL62") = txt94
Range("BL63") = txt95
Range("BM59") = txt101
Range("BM60") = txt102
Range("BM61") = txt103
Range("BM62") = txt104
Range("BM63") = txt105
End If
If txtPreiscode = Range("BD66") Then
Range("BD68") = txt11
Range("BD69") = txt12
Range("BD70") = txt13
Range("BD71") = txt14
Range("BD72") = txt15
Range("BE68") = txt21
Range("BE69") = txt22
Range("BE70") = txt23
Range("BE71") = txt24
Range("BE72") = txt25
Range("BF68") = txt31
Range("BF69") = txt32
Range("BF70") = txt33
Range("BF71") = txt34
Range("BF72") = txt35
Range("BG68") = txt41
Range("BG69") = txt42
Range("BG70") = txt43
Range("BG71") = txt44
Range("BG72") = txt45
Range("BH68") = txt51
Range("BH69") = txt52
Range("BH70") = txt53
Range("BH71") = txt54
Range("BH72") = txt55
Range("BI68") = txt61
Range("BI69") = txt62
Range("BI70") = txt63
Range("BI71") = txt64
Range("BI72") = txt65
Range("BJ68") = txt71
Range("BJ69") = txt72
Range("BJ70") = txt73
Range("BJ71") = txt74
Range("BJ72") = txt75
Range("BK68") = txt81
Range("BK69") = txt82
Range("BK70") = txt83
Range("BK71") = txt84
Range("BK72") = txt85
Range("BL68") = txt91
Range("BL69") = txt92
Range("BL70") = txt93
Range("BL71") = txt94
Range("BL72") = txt95
Range("BM68") = txt101
Range("BM69") = txt102
Range("BM70") = txt103
Range("BM71") = txt104
Range("BM72") = txt105
End If
If txtPreiscode = Range("BD75") Then
Range("BD77") = txt11
Range("BD78") = txt12
Range("BD79") = txt13
Range("BD80") = txt14
Range("BD81") = txt15
Range("BE77") = txt21
Range("BE78") = txt22
Range("BE79") = txt23
Range("BE80") = txt24
Range("BE81") = txt25
Range("BF77") = txt31
Range("BF78") = txt32
Range("BF79") = txt33
Range("BF80") = txt34
Range("BF81") = txt35
Range("BG77") = txt41
Range("BG78") = txt42
Range("BG79") = txt43
Range("BG80") = txt44
Range("BG81") = txt45
Range("BH77") = txt51
Range("BH78") = txt52
Range("BH79") = txt53
Range("BH80") = txt54
Range("BH81") = txt55
Range("BI77") = txt61
Range("BI78") = txt62
Range("BI79") = txt63
Range("BI80") = txt64
Range("BI81") = txt65
Range("BJ77") = txt71
Range("BJ78") = txt72
Range("BJ79") = txt73
Range("BJ80") = txt74
Range("BJ81") = txt75
Range("BK77") = txt81
Range("BK78") = txt82
Range("BK79") = txt83
Range("BK80") = txt84
Range("BK81") = txt85
Range("BL77") = txt91
Range("BL78") = txt92
Range("BL79") = txt93
Range("BL80") = txt94
Range("BL81") = txt95
Range("BM77") = txt101
Range("BM78") = txt102
Range("BM79") = txt103
Range("BM80") = txt104
Range("BM81") = txt105
End If
Ich brauche aber 30 If-Schleifen und das wird ziemlich mühsam. Ist das kürzer aus möglich? Hier ein paar Eckdaten:
Die Zellen, mit denen txtPreiscode verglichen wird und die dazugehörigen Zeilen, in denen eingefügt wird sind (die Spalten lauten:BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM):
BD3 -> 5-9
BD12 -> 14-18
BD21 -> 23-27
BD30 -> 32-36
BD39 -> 41-45
BD48 -> 50-54
BD57 -> 59-63
BD66 -> 68-72
BD75 -> 77-81
BD84 -> 86-90
BD93 -> 95-99
BD102 -> 104-108
BD111 -> 113-117
BD120 -> 122-126
BD129 -> 131-135
BD138 -> 140-144
BD147 -> 149-153
BD156 -> 158-162
BD165 -> 167-171
BD174 -> 176-180
BD183 -> 185-189
BD192 -> 194-198
BD201 -> 203-207
BD210 -> 212-216
BD219 -> 221-225
BD228 -> 230-234
BD237 -> 239-243
BD246 -> 248-252
BD255 -> 257-261
BD264 -> 266-270
Folgende Einträge gehören in folgende Spalten:
Spalte BD -> Einträge txt11-txt15
Spalte BE -> Einträge txt21-txt25
Spalte BF -> Einträge txt31-txt35
Spalte BG -> Einträge txt41-txt45
Spalte BH -> Einträge txt51-txt55
Spalte BI -> Einträge txt61-txt65
Spalte BJ -> Einträge txt71-txt75
Spalte BK -> Einträge txt81-txt85
Spalte BL -> Einträge txt91-txt95
Spalte BM -> Einträge txt101-txt105
Danke für eure Hilfe schon im Vorraus.
MfG
Sebastian
P.S.: Beispieldatei geht leider nicht. Habe Datei nicht da. Sorry.