ich muss einen riesigen String trennen. Die Länge des Strings soll >=1200 Zeichen sein.
Dann zeigt Excel die betroffenen Zellen bei einem Klick in die Formel an.
Der String:
B1+B3+B5+B7+B9+B11+B13+B15+B17+B19+B21+B23+B25+B27+B29+B31+B33+B35+B37+B39+B41+B43+B45+B47+B49+B51+B53+B55+B57+B59+B61+B63+B65+B67+B69+B71+B73+B75+B77+B79+B81+B83+B85+B87+B89+B91+B93+B95+B97+B99+B101+B103+B105+B107+B109+B111+B113+B115+B117+B119+B121+B123+B125+B127+B129+B131+B133+B135+B137+B139+B141+B143+B145+B147+B149+B151+B153+B155+B157+B159+B161+B163+B165+B167+B169+B171+B173+B175+B177+B179+B181+B183+B185+B187+B189+B191+B193+B195+B197+B199+B201+B203+B205+B207+B209+B211+B213+B215+B217+B219+B221+B223+B225+B227+B229+B231+B233+B235+B237+B239+B241+B243+B245+B247+B249+B251+B253+B255+B257+B259+B261+B263+B265+B267+B269+B271+B273+B275+B277+B279+B281+B283+B285+B287+B289+B291+B293+B295+B297+B299+B301+B303+B305+B307+B309+B311+B313+B315+B317+B319+B321+B323+B325+B327+B329+B331+B333+B335+B337+B339+B341+B343+B345+B347+B349+B351+B353+B355+B357+B359+B361+B363+B365+B367+B369+B371+B373+B375+B377+B379+B381+B383+B385+B387+B389+B391+B393+B395+B397+B399+B401+B403+B405+B407+B409+B411+B413+B415+B417+B419+B421+B423+B425+B427+B429+B431+B433+B435+B437+B439+B441+B443+B445+B447+B449+B451+B453+B455+B457+B459+B461+B463+B465+B467+B469+B471+B473+B475+B477+B479+B481+B483+B485+B487+B489+B491+B493+B495+B497+B499+B501+B503+B505+B507+B509+B511+B513+B515+B517+B519+B521+B523+B525+B527+B529+B531+B533+B535+B537+B539+B541+B543+B545+B547+B549+B551+B553+B555+B557+B559+B561+B563+B565+B567+B569+B571+B573+B575+B577+B579+B581+B583+B585+B587+B589+B591+B593+B595+B597+B599+B601+B603+B605+B607+B609+B611+B613+B615+B617+B619+B621+B623+B625+B627+B629+B631+B633+B635+B637+B639+B641+B643+B645+B647+B649+B651+B653+B655+B657+B659+B661+B663+B665+B667+B669+B671+B673+B675+B677+B679+B681+B683+B685+B687+B689+B691+B693+B695+B697+B699+B701+B703+B705+B707+B709+B711+B713+B715+B717+B719+B721+B723+B725+B727+B729+B731+B733+B735+B737+B739+B741+B743+B745+B747+B749+B751+B753+B755+B757+B759+B761+B763+B765+B767+B769+B771+B773+B775+B777+B779+B781+B783+B785+B787+B789+B791+B793+B795+B797+B799+B801+B803+B805+B807+B809+B811+B813+B815+B817+B819+B821+B823+B825+B827+B829+B831+B833+B835+B837+B839+B841+B843+B845+B847+B849+B851+B853+B855+B857+B859+B861+B863+B865+B867+B869+B871+B873+B875+B877+B879+B881+B883+B885+B887+B889+B891+B893+B895+B897+B899+B901+B903+B905+B907+B909+B911+B913+B915+B917+B919+B921+B923+B925+B927+B929+B931+B933+B935+B937+B939+B941+B943+B945+B947+B949+B951+B953+B955+B957+B959+B961+B963+B965+B967+B969+B971+B973+B975+B977+B979+B981+B983+B985+B987+B989+B991+B993+B995+B997+B999+B1001+B1003+B1005+B1007+B1009+B1011+B1013+B1015+B1017+B1019+B1021+B1023+B1025+B1027+B1029+B1031+B1033+B1035+B1037+B1039+B1041+B1043+B1045+B1047+B1049+B1051+B1053+B1055+B1057+B1059+B1061+B1063+B1065+B1067+B1069+B1071+B1073+B1075+B1077+B1079+B1081+B1083+B1085+B1087+B1089+B1091+B1093+B1095+B1097+B1099+B1101+B1103+B1105+B1107+B1109+B1111+B1113+B1115+B1117+B1119+B1121+B1123+B1125+B1127+B1129+B1131+B1133+B1135+B1137+B1139+B1141+B1143+B1145+B1147+B1149+B1151+B1153+B1155+B1157+B1159+B1161+B1163+B1165+B1167+B1169+B1171+B1173+B1175+B1177+B1179+B1181+B1183+B1185+B1187+B1189+B1191+B1193+B1195+B1197+B1199+B1201+B1203+B1205+B1207+B1209+B1211+B1213+B1215+B1217+B1219+B1221+B1223+B1225+B1227+B1229+B1231+B1233+B1235+B1237+B1239+B1241+B1243+B1245+B1247+B1249+B1251+B1253+B1255+B1257+B1259+B1261+B1263+B1265+B1267+B1269+B1271+B1273+B1275+B1277+B1279+B1281+B1283+B1285+B1287+B1289+B1291+B1293+B1295+B1297+B1299+B1301+B1303+B1305+B1307+B1309+B1311+B1313+B1315+B1317+B1319+B1321+B1323+B1325+B1327+B1329+B1331+B1333+B1335+B1337+B1339+B1341+B1343+B1345+B1347+B1349+B1351+B1353+B1355+B1357+B1359+B1361+B1363+B1365+B1367+B1369+B1371+B1373+B1375+B1377+B1379+B1381+B1383+B1385+B1387+B1389+B1391+B1393+B1395+B1397+B1399+B1401+B1403+B1405+B1407+B1409+B1411+B1413+B1415+B1417+B1419+B1421+B1423+B1425+B1427+B1429+B1431+B1433+B1435+B1437+B1439+B1441+B1443+B1445+B1447+B1449+B1451+B1453+B1455+B1457+B1459+B1461+B1463+B1465+B1467+B1469+B1471+B1473+B1475+B1477+B1479+B1481+B1483+B1485+B1487+B1489+B1491+B1493+B1495+B1497+B1499+B1501+B1503+B1505+B1507+B1509+B1511+B1513+B1515+B1517+B1519+B1521+B1523+B1525+B1527+B1529+B1531+B1533+B1535+B1537+B1539+B1541+B1543+B1545+B1547+B1549+B1551+B1553+B1555+B1557+B1559+B1561+B1563+B1565+B1567+B1569+B1571+
Der String kann durchaus noch länger sein.
Mein kläglicher Versuch nachstehend:
Sub SplittenDerAddition()
Dim Formeltext3 As String
Zellname2 = 3
Formeltext2 = Range("M982").Value
'das letzte + Zeichen entfernen
Formeltext3 = Left(Formeltext2, Len(Formeltext2) - 1)
'Länge des Strings ermitteln
MyLen = Len(Formeltext3)
'Schleifenanzahl festlegen
Schleife = WorksheetFunction.RoundUp(Len(Formeltext3) / 1200, 0)
'festlegen ab welcher Stelle innerhalb des Strings gesucht werden soll
Trennzahl = Round(Len(Formeltext3) / Schleife, 0)
For t = 1 To Schleife
Zellname2 = Zellname2 + 1
If MyLen > 1200 Then
TrennPos1 = InStr(Trennzahl, Formeltext3, "B", 1)
Addition = Right(Formeltext3, TrennPos1)
Range("M" & Zellname2 + 3).Value = "=" & Addition
Formeltext4 = Left(Formeltext3, Len(Formeltext3) - (Len(Addition)) - 1)
Formeltext3 = Formeltext4
MyLen = Len(Formeltext3) - Len(Addition)
Else
Formeltext4 = Left(Formeltext3, Len(Formeltext3) - (Len(Addition)))
Formeltext3 = Formeltext4
Range("M" & Zellname2 + 3).Value = "=" & Formeltext4
End If
Next t
End Sub
Vieleicht sitze ich schon zu lange dran und habe eine Denkblockade
.
Es will mir nicht gelingen den String so zu trennen, dass eine sauber Formeladdition möglich ist.
Gruß SP