Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Из ячейки с набором текста через разделитель в диапазон строк
 
Здравствуйте уважаемые мастера пера по VBA!
Расклад такой (файл прилагается):
а) Имеется куча информации в 1 ячейке (сцепление, выборка, список), разделённой разделителем (каламбур).
б) Нужно разбить эту ячейку по СТРОКАМ, начиная с выделенной, через ЛЮБОЙ ЗАДАННЫЙ (1, 2, 3 символа) разделитель. (чтобы не пришлось ничего перемещать, заменять и удалять).
Желательно: сохранить форматирование исходной ячейки.Мой мозг сгенерировал зловещий план...
1. Я записал рекордером вот такой макрос на основе инструмента "текст по столбцам", и немного исправил, привязав к текущей выделенной ячейке, а не к какой-то конкретной:
Код
Sub TEXTonCOL()
    ActiveCell.Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End Sub
На этом мои познания в области VBA иссякли и, как видно из кода, макрос по-прежнему привязан к конкретному разделителю "|", так что первая проблема
- как сделать, чтобы при запуске макроса вылезал запрос типа "введите символ-разделитель"?
Подпроблема: инструмент "текст по столбцам" позволяет ввести только ОДИН символ разделителя, а хотелось хотя бы 3 (разделитель с 2мя пробелами, к примеру).
2. После применения макроса на ячейке, получаю диапазон столбцов (то есть строку) с текстом.
(В решении 2 виден очевидный минус - если разделитель отделён пробелами, то они, естественно, никуда не деваются и приходится PLEXом подчищать.)
3. Выделяю этот диапазон, кроме исходной ячейки и транспонирую его специальной вставкой под исходную ячейку.
Как видно, способ хоть и увеличивает скорость за счёт макроса, но до рабочего инструмента ой как далеко.
Возможно я вообще решил переплыть реку ВДОЛЬ, да ещё и против течения...то есть существует решение гениально простое и совершенно отличное от этого ))))
Во вкладке "как надо" приложенного файла на заливку чёрным внимания не обращать - это я так разделил исходные ячейки )))

Дополнено ТУТ
Изменено: Jack Famous - 6 Авг 2016 12:19:14
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Цитата
Jack_Famous написал: Возможно я вообще решил переплыть реку ВДОЛЬ
Возможно :)
 
получилось что-то не то....вообще как-то странно всё поделилось
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
можно такой вариант. работает с активной ячейкой
Код
Sub Test()
    Dim Delim As String
    Dim Str As String
    Dim Arr() As String
    Dim j As Integer
    
    Delim = InputBox("Введите символ-разделитель")
    If Delim = "" Then End
    
    Str = ActiveCell
    
    Arr = Split(Str, Delim)
    If UBound(Arr) = 0 Then
        MsgBox "Нет в строке такого разделителя"
        End
    End If

    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(UBound(Arr), 0)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For j = 0 To UBound(Arr)
        ActiveCell.Offset(j, 0) = Arr(j)
    Next j
    
End Sub
 
webley, ПРОСТО СУПЕР !!!! :D 8)ОГРОМНОЕ СПАСИБО !!!!
даже вот это " \/  " - 5 знаков !!!! можно сделать разделителем и он поймёт )))))
Единственное, что у меня не получилось разделить это перенос строки Alt+Enter. Пробовал и Ctrl+J, и Alt+010, и =символ(10).Но я им пользуюсь нечасто...
P.S.: Посоветуйте что-нибудь по программированию в Excel - хотя бы синтаксис поучить...
Изменено: Jack_Famous - 25 Мар 2015 14:54:31
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Как ввести перенос строки в InputBox я не знаю, но можно схитрить
Если в код после строки
Код
 Delim = InputBox("Введите символ-разделитель") 

Добавить строку
Код
 If Delim = "Перенос" Then Delim = Chr(10) 
 
То слово Перенос, указанное в качестве разделителя, будет соответствовать переносу строки

Что касается литературы - я в этом вопросе не силен:)
 
Цитата
Jack_Famous написал:получилось что-то не то....вообще как-то странно всё поделилось
а вы пробовали менять разделитель??)
я ж для демонстрации ввел слово)
Цитата
Единственное, что у меня не получилось разделить это перенос строки Alt+Enter
Кстати - и этот вариант в моем файле работает, если в ячейке D1 указать Alt+Enter :)
Изменено: Dima S - 31 Мар 2015 11:54:10
 
webley, ОТЛИЧНО, УНИВЕРСАЛЬНО, КОРОТКО, ГЕНИАЛЬНО!!! ))))
хоть сейчас в PLEX!!!
СПАСИБО БОЛЬШОЕ!!!
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Dima S, большое спасибо за помощь! Наверное я просто неправильно понял Ваш код...
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Ребята, это снова я ))) тогда не протестировал макрос на работу в "умной" таблице. Сейчас вот добрался - выдаёт ошибку "недопустимая операция. была попытка сдвинуть строки таблицы" - есть возможность обмануть или обойти?...
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
попробовал в "умной" таблице - тоже работает.
подобный эффект может наблюдаться в случае, если таблица занимает все строки на листе. не в этом ли проблема?
 
webley, мда...тупанул я маленько )))) спасибо)))) только проблема не в том, что "таблица занимает все строки на листе", а в том, что ячейка, которую мы разбиваем на строки должна быть НЕ ВЫШЕ последней ячейки "умной" таблицы.
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
webley, помогите пожалуйста разобраться...что изменить в коде, чтобы при запуске макроса вниз сдвигались не только ячейки, а все задействованные строки???... Вот пример файла как объяснение, почему я так заморачиваюсь... До строки №465 клепал вручную с листа №0.... Разделитель "|"
Изменено: Jack_Famous - 16 Апр 2015 14:12:04
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
если я правильно понял вопрос, то надо строку
Код
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(UBound(Arr), 0)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

заменить на
Код
Rows(ActiveCell.Offset(1, 0).Row & ":" & ActiveCell.Offset(UBound(Arr), 0).Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
webley, Вы как всегда всё правильно поняли )))) теперь работает в любом месте "умной" таблицы ))))) ОГРОМНОЕ СПАСИБО !!!!!
P.S. всё-таки функция достойна включения в PLEX
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Добрый день, уважаемые форумчане! Подскажите пожалуйста, как сделать, чтобы этот макрос работал не только с 1 (активной) ячейкой, но также обрабатывал выделенный простой одномерный массив (столбец). Вроде нужно просто добавить цикл, но не знаю как (((
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Цитата
Jack_Famous написал: нужно просто добавить цикл
Код
Sub jjj()
Dim cl As Range
    If TypeName(Selection) = "Range" Then
        For Each cl In Selection
            ' code
        Next cl
    End If
End Sub

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, получилось вот что (код макроса снизу и он не работает). Походу делает он вот что: запрашивает символ разделитель и разбивает первую ячейку из диапазона как надо, но после разбивки Selection охватывает только УЖЕ РАЗБИТЫЕ строки первой ячейки. Затем макрос опять запрашивает разделитель (хотя имеется ввиду, что в диапазоне для разбивки каждой ячейки используется один и тот же разделитель (иначе нет смысла менять макрос). Прикрепляю образец файла с пояснениями, буду рад если поможете. Спасибо за участие!
Код
Sub TextOnRows()
Dim cl As Range
    If TypeName(Selection) = "Range" Then
        For Each cl In Selection
Dim Delim As String
    Dim Str As String
    Dim arr() As String
    Dim j As Integer
     
    Delim = InputBox("Введите символ-разделитель")
    If Delim = "перенос" Then Delim = Chr(10)
    If Delim = "" Then End
     
    Str = ActiveCell
     
    arr = Split(Str, Delim)
    If UBound(arr) = 0 Then
        MsgBox "Нет в строке такого разделителя"
        End
    End If
 
    Rows(ActiveCell.Offset(1, 0).Row & ":" & ActiveCell.Offset(UBound(arr), 0).Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For j = 0 To UBound(arr)
        ActiveCell.Offset(j, 0) = arr(j)
    Next j
        Next cl
    End If
End Sub
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Если поможет с решением, нашёл подобное в надстройке MULTEX у Дмитрия Щербакова (The-Prist) http://www.excel-vba.ru/multex/razbit-po-strokamstolbcam/
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Jack_Famous, заведите ячейку, в которой будете хранить символ-разделитель и используйте её в макросе.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, простите, но разве без этого не обойтись??? У нас есть InputBox, в котором мы единожды для диапазона указываем разделитель, у нас есть макрос, который разбивает всё как надо для одной ячейки. Подскажите просто как сделать цикл типа этого (этот не работает  :D  ). Я тут хотел сделать следующее: добавить диапазон xRng, равный выделенному диапазону с началом в первой (верхней) ячейке (разумеется). Запускается цикл...1. проверка на наличие указанного разделителя в первой ячейке, если нет - пропускаем и берём следующую ячейку в диапазоне, если есть - разбиваем по всем правилам исходного макроса от Webley, затем берём следующую ячейку диапазона (тут как-то нужно определять следующую ячейку, чтобы не путать с уже разбитыми, наверное перед запуском цикла). Вот как-то так... Если нет желания/возможности возиться с таким вариантом, подскажите путь реализации своего видения решения данной проблемы. Заранее спасибо!
Код
Sub TextOnRows()
    Dim Delim As String
    Dim Str As String
    Dim arr() As String
    Dim j As Integer
     
    Delim = InputBox("Введите символ-разделитель")
    If Delim = "перенос" Then Delim = Chr(10)
    If Delim = "" Then End
     
    i = 1
    Set xRng = Selection
    For xCounter = 1 To xRng.Rows.Count
     
    arr = Split(xCounter, Delim)
    If UBound(arr) = 0 Then i = i + 1
    Else
 
    Rows(xCounter.Offset(1, 0).Row & ":" & xCounter.Offset(UBound(arr), 0).Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
    
    For j = 0 To UBound(arr)
        xCounter.Offset(j, 0) = arr(j)
    Next j
     
End Sub
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Jack_Famous, внимательней прочтите свой код из №18. Зачем в цикл пихать запрос на разделитель и объявление переменных??? При чём тут активная ячейка, если работаем с переменной cl. Сместите 4ю строку перед 16й. Уберите Str и ActiveCell и замените их на cl. Если я где-то ошибся, то пользуйтесь отладкой и вникайте в код.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, а если в диапазоне присутствуют ячейки, не нуждающиеся в обработке?
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
JayBhagavan, насчёт переменного разделителя не принципиально. Вот изменённый код для разделителя "перенос строки внутри ячейки". Выполняется до ошибки на всё количество строк 1 столбца... Я не понимаю, что нужно сделать(((( практически совсем не разбираюсь в VBA  :(
Код
Sub TextOnRows()
    Dim cl As Range
    If TypeName(Selection) = "Range" Then
    
    Dim arr() As String
    Dim j As Integer
    
    For Each cl In Selection
    arr = Split(cl, Chr(10))
    
    Rows(cl.Offset(1, 0).Row & ":" & cl.Offset(UBound(arr), 0).Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For j = 0 To UBound(arr)
        cl.Offset(j, 0) = arr(j)
    Next j
        Next cl
    End If
End Sub
Изменено: Jack_Famous - 8 Фев 2016 14:03:17
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Цитата
Jack_Famous написал: если в диапазоне присутствуют ячейки, не нуждающиеся в обработке?
Тогда приложите файл, согласно правил форума, и максимально опишите все исключения.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Прошу посмотреть Пост №21

Файл ещё раз прикрепил. Там вроде бы всё понятно...
Если коротко - есть полностью рабочий макрос, который работает только с 1 ячейкой, а именно разбивает ячейку вниз через указанный символ-разделитель, при этом делает это, вставляя строки (сдвигает вниз). Если в ячейке такого разделителя нет, то выводит сообщение об ошибке.
Всё, что нужно изменить - добавить цикл выполнения данного макроса по выделенному диапазону (при том, что разделитель в диапазоне одинаковый). При этом нужно будет заменить проверку: теперь, если в ячейке отсутствует указанный разделитель - брать следующую ячейку.
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Цитата
...нашёл подобное в надстройке MULTEX у Дмитрия Щербакова
не работает ДАННЫМ образом, а просто, что называется, "в лоб" разносит по строкам, не вставляя строки...
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Скрытый текст

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, Браво! Просто замечательно! ))))) В точности, как и задумывалось - вам удалось даже сохранить возможность менять разделитель, не влезая в код, что очень универсально (буду пытаться разобраться в коде - как)! Огромное вам спасибо - вы очень помогли!!!!  8) :D :idea:
P.S.: отдельное спасибо модератору vikttur за коррекцию моих корявых мыслей на сайте)))))
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
Страницы: 1
Читают тему (гостей: 1)