1

Тема: VBA: сортировать ячейки excel по содержимому

Здравствуйте!
Помогите, пожалуйста сделать макрос, который бы сортировал ячейки по их содержимому. Суть задачи такова: есть файл, в первом столбце которого много-много ячеек с наименованиями. Хотелось бы, чтобы макрос сканировал все непустые ячейки этого столбца (ну, или просто ячейки от первой до произвольной; или до 65535) и при совпадении хотя бы части содержимого с шаблоном (например, "гайка" или, лучше, "гайк*"), переносил всё содержимое ячейки в соседнюю (т.е. в ячейку соседнего столбца, имеющую тот же номер, что и исходная). Совсем здорово было бы, если бы макрос сканировал ячейки на совпадение с несколькими шаблонами, например "гайк*", "шуру*" и "гвозд*" и брал бы эти значения шаблонов из опредленных, специально предназначенных для этого, ячеек; а переносил не только в соседнюю ячейку, а в ячейку с заданным смещением (для каждого шаблона - свое смещение; гайки - в соседнюю, шурупы - в "через одну" и т.д.).

2

Re: VBA: сортировать ячейки excel по содержимому

На первый взгляд, Ваша задача совсем несложно решается с помощью стандартных функций рабочего листа.
Почему решили прибегнуть именно к макросу?
Кстати, чтобы наводящих вопросов было меньше, а разговор по теме имел бы более предметный характер, полезно посмотреть на образец исходной таблицы (если не секрет, конечно).

3 (изменено: fsc_psd, 2011-03-23 06:53:10)

Re: VBA: сортировать ячейки excel по содержимому

Dmitrii пишет:

На первый взгляд, Ваша задача совсем несложно решается

Я искренне надеюсь, что это так. Но я настолько плохо знаю VBA, что даже не представляю, как можно найти информацию о нужных мне функциях.

Dmitrii пишет:

образец исходной таблицы

Да нет пока никакой таблицы. Просто столбец ячеек с текстовыми значениями. Самый, так сказать, теоретический минимум.
Проверяем все ячейки, совпала часть содержимого с шаблоном - переносим соответствующую ячейку в соседнюю.

4

Re: VBA: сортировать ячейки excel по содержимому

fsc_psd пишет:

... плохо знаю VBA, что даже не представляю, как можно найти информацию о нужных мне функциях...

1. VBA и формулы, составленные из стандартных функций рабочего листа,- разные инструменты.
2. Встроенная справка Excel (как по функциям, так и по VBA) - очень хороший источник базовой информации.

fsc_psd пишет:

... Просто столбец ячеек с текстовыми значениями...

Именно об этом и шла речь. Ну да ладно, придётся пока довольствоваться моей "стряпнёй".
Здесь выложен файл с примером книги, данные на рабочих листах которой обрабатываются с помощью формул:
http://fayloobmennik.net/492785

5

Re: VBA: сортировать ячейки excel по содержимому

Dmitrii пишет:

придётся пока довольствоваться моей "стряпнёй"

Она прекрасно работает, большое вам спасибо!
Единственно только, она не "переносит" значения, а копирует их. Подскажите пожалуйста, как дополнить формулу, чтобы именно переносила?

И не сочтите за наглость, но тут пришло понимание того, почему хочется именно макрос: ваши формулы, пр всей их действенности, нужно предварительно разместить в соответствующих ячейках листа, и делать это нужно всякий раз, когда захочется посортировать новый файл. При больших размерах файла, и частой его смене, это может быть утомительно. Макрос же в лучшем случае нужно просто запустить. В худшем - чуток подредактировать под новый формат файла. Я прошу прошения за назойливость, но, может быть, подсобите-таки и с макросом?

6 (изменено: Dmitrii, 2011-03-28 07:17:04)

Re: VBA: сортировать ячейки excel по содержимому

fsc_psd пишет:

... не "переносит" значения, а копирует их <...> как дополнить формулу, чтобы именно переносила?..

Если речь об удалении исходных данных, то с помощью формул этого сделать нельзя.
Здесь надо поступать так:
- выделить диапазон ячеек с результирующими данными;
- скопировать его содержимое в буфер обмена;
- не снимая выделения, указать курсором "мыши" внутрь этого диапазона и щёлкнуть правой кнопкой;
- в появившемся контекстном меню указать на команду "Специальная вставка..." и щёлкнуть любой кнопкой;
- в появившемся окне, в разделе "Вставить", выбрать вариант "Значения" и нажать кнопку "OK";
- очистить диапазон ячеек с исходными данными.

fsc_psd пишет:

... формулы <...> нужно предварительно разместить в соответствующих ячейках листа, и делать это нужно всякий раз, когда захочется посортировать новый файл. При больших размерах файла, и частой его смене, это может быть утомительно...

Это ничуть не утомительнее, чем описанная выше процедура замены формул на вычисленные ими значения. Инструмент используется тот же. Только вставлять надо не значения, а формулы.

fsc_psd пишет:

... Макрос же в лучшем случае нужно просто запустить. В худшем - чуток подредактировать под новый формат файла...

1. Макрос, так же, как и набор формул, должен в новой книге откуда-то появиться.
2. В таких случаях нередко удобнее поступать наоборот: не формулы копировать из старой книги в новую, а исходные данные из новой книги - в копию старой книги с уже настроенными формулами и форматами результирующих таблиц.

Впрочем, дело хозяйское: макрос, так макрос.
Новый вариант книги ищите здесь: http://fayloobmennik.net/494032

Для сравнения я сохранил листы с формулами, при этом универсализировав их (формулы) и несколько расширив их возможности.
Пример обработки данных макросом представлен на листе "Макрос".
Исходные данные я не стал удалять для наглядности результата.

Примечание: формулы, составленные из стандартных функций рабочего листа,- более быстрый инструмент, чем самый лучший макрос, выполняющий аналогичную работу.

7

Re: VBA: сортировать ячейки excel по содержимому

Попытался сделать то, что вы хотели. Условия: таблица из трёх столбцов с заголовками (не обязательно такими, но обязательно с заголовками):

Наименования|    Найденные|    Что искать|
Гайка             |                    |     гайк         |
Гайка             |                    |     шуруп      |
шуруп            |                    |                     |

В первом столбце ваш список, в третьем - шаблоны поиска(условие: в 1 и 3 столбце не должно быть пустых ячеек между заполненными). Сам макрос:

Sub gaika()

f = 1
r = "A" & f
v = "nil"
Do While v <> ""
Range(r).Select
v = ActiveCell.Text
f = f + 1
r = "A" & f
Loop

b = 1
a = "A" & b
c = "nil"
Do While c <> ""
Range(a).Select
c = ActiveCell.Text
b = b + 1
a = "A" & b
Loop
For i = 2 To b - 2
    srch = Range("C" & i).Text
     Columns("a:a").Select
       For q = 1 To f - 2
        On Error Resume Next
        Selection.Find(What:=srch, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         Range("b" & ActiveCell.Row).FormulaR1C1 = ActiveCell.Text
    Next q
    Next i
End Sub

Переносить текст не получилось, так как у меня возникли проблемы с обработкой ошибок. Может кто подкорректирует.

8

Re: VBA: сортировать ячейки excel по содержимому

Всем огромное спасибо! Вы мне очень помогли!

9

Re: VBA: сортировать ячейки excel по содержимому

User419e пишет:

Попытался сделать то, что вы хотели. Условия: таблица из трёх столбцов с заголовками (не обязательно такими, но обязательно с заголовками):

Наименования|    Найденные|    Что искать|
Гайка             |                    |     гайк         |
Гайка             |                    |     шуруп      |
шуруп            |                    |                     |

В первом столбце ваш список, в третьем - шаблоны поиска(условие: в 1 и 3 столбце не должно быть пустых ячеек между заполненными). Сам макрос:

Sub gaika()

f = 1
r = "A" & f
v = "nil"
Do While v <> ""
Range(r).Select
v = ActiveCell.Text
f = f + 1
r = "A" & f
Loop

b = 1
a = "A" & b
c = "nil"
Do While c <> ""
Range(a).Select
c = ActiveCell.Text
b = b + 1
a = "A" & b
Loop
For i = 2 To b - 2
    srch = Range("C" & i).Text
     Columns("a:a").Select
       For q = 1 To f - 2
        On Error Resume Next
        Selection.Find(What:=srch, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         Range("b" & ActiveCell.Row).FormulaR1C1 = ActiveCell.Text
    Next q
    Next i
End Sub

Переносить текст не получилось, так как у меня возникли проблемы с обработкой ошибок. Может кто подкорректирует.

Ето ка раз то што я искал