Сравнение двух листов в excel vba
Dim obsii As Range, xx As Range, cc As Range, sort As Range
Переделал скаченную программу под свои нужды. Выполняет сравнение двух диапазонов и при совпадении подставляет данные из одного в другой.Если оба сравниваемых диапазона находятся на одном листе, то все работает правильно. Как только разношу на разные листы, то выдает ошибку в строке
[vba] [/vba]
Если в коде везде указать имя одного листа то работает.
Помогите пожалуйста начинающему разобраться.
[vba]
Dim obsii As Range, xx As Range, cc As Range, sort As Range
[/vba]Если в коде везде указать имя одного листа то работает.
Помогите пожалуйста начинающему разобраться.
[vba]
Dim obsii As Range, xx As Range, cc As Range, sort As Range
[/vba] Автор - 227Дата добавления - 03.05.2015 в 17:48
227, Вот первоначальный код, который я переделывал:
227, Вот первоначальный код, который я переделывал:
[/vba] Автор - 227Дата добавления - 03.05.2015 в 17:57 227, переделанный макрос берет заначения ячеек из диапазона столбика B на одном листе, и сравнивает их с диапазоном столбика A на другом листе. При совпадениях копирует ячейку правее B у казанный столбик. 227, переделанный макрос берет заначения ячеек из диапазона столбика B на одном листе, и сравнивает их с диапазоном столбика A на другом листе. При совпадениях копирует ячейку правее B у казанный столбик. 227 227, Если кто в этом сможет разобраться то подскажите пожалуйста, просто знаний не хватает((( 227, Если кто в этом сможет разобраться то подскажите пожалуйста, просто знаний не хватает((( 227 [/vba]
вот эти Cells(1, 1), Cells(a, 1) т.к. не указан лист, будут браться с активного листа.
Перед каждым cells нужно указать лист!
В оригинале т.к. всё работало на одном активном листе это роли не играло.
А вообще если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей. [/vba]
вот эти Cells(1, 1), Cells(a, 1) т.к. не указан лист, будут браться с активного листа.
Перед каждым cells нужно указать лист!
В оригинале т.к. всё работало на одном активном листе это роли не играло.
А вообще если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей. Hugo [/vba]
вот эти Cells(1, 1), Cells(a, 1) т.к. не указан лист, будут браться с активного листа.
Перед каждым cells нужно указать лист!
В оригинале т.к. всё работало на одном активном листе это роли не играло.
А вообще если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей. Автор - Hugo
Дата добавления - 03.05.2015 в 18:39
Hugo, помогло, спасибо огромное!
Макрос теперь работает, но ужасно медленно(((
У меня сейчас диапазоны на обоих листах примерно 15К на каждом. На одном, если исключить нули, то примерно половина. И одно сравнение занимает много времени(((
Можно подробнее про:
. если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей.У меня мак, но есть доступ и к виндовым компам. Скорость очень важна!
..я сделал уже несколько вариантов этого алгоритма, этот уже третий. Первый был на простом копировании ячеек, второй на массиве, и этот на диапазонах уже третий.
Но скорость на всех примерно одинаковая ± 5-7 мин. На медленном компе
35 мин, на быстром 19 мин. Очень хотелось бы ускорить этот процесс!
Как не странно, но при простом копировании ячеек получается быстрее на несколько мин(
Hugo, помогло, спасибо огромное!
Макрос теперь работает, но ужасно медленно(((
У меня сейчас диапазоны на обоих листах примерно 15К на каждом. На одном, если исключить нули, то примерно половина. И одно сравнение занимает много времени(((
Можно подробнее про:
. если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей.У меня мак, но есть доступ и к виндовым компам. Скорость очень важна!
..я сделал уже несколько вариантов этого алгоритма, этот уже третий. Первый был на простом копировании ячеек, второй на массиве, и этот на диапазонах уже третий.
Но скорость на всех примерно одинаковая ± 5-7 мин. На медленном компе
35 мин, на быстром 19 мин. Очень хотелось бы ускорить этот процесс!
Как не странно, но при простом копировании ячеек получается быстрее на несколько мин( 227
Можно подробнее про:
. если нужно сравнивать большие диапазоны - делайте на массивах и словаре. Если конечно не Мак - там нет словарей.У меня мак, но есть доступ и к виндовым компам. Скорость очень важна!
..я сделал уже несколько вариантов этого алгоритма, этот уже третий. Первый был на простом копировании ячеек, второй на массиве, и этот на диапазонах уже третий.
Но скорость на всех примерно одинаковая ± 5-7 мин. На медленном компе
35 мин, на быстром 19 мин. Очень хотелось бы ускорить этот процесс!
Как не странно, но при простом копировании ячеек получается быстрее на несколько мин( Автор - 227
Дата добавления - 03.05.2015 в 20:32
Вот скрин с компа побыстрее, но это сути не меняет, возможно ли сделать еще быстрее?
Вот скрин с компа побыстрее, но это сути не меняет, возможно ли сделать еще быстрее?
Автор - 227
Дата добавления - 03.05.2015 в 21:13
AndreTM, я пробовал с массивом, но так получилось даже медленнее на несколько минут чем с ячейками((
Вот вариант с массивом:
Летучие функции это формулы? Ничего такого нет, я все пробую на новой чистой книге.
Скрины исходных данных и таблица куда это все пишется. Один столбик одна сортировка:
Там идет сравнение уникальных ячеек, при точном совпадении переносятся ячейки с целыми числами.
Я далеко не претендую на оптимальность кода, я всего неделю программирую. Если вы можете предложить иной алгоритм сравнения, который повысит скорость, то буду очень благодарен за любой совет как увеличить скорость.
AndreTM, я пробовал с массивом, но так получилось даже медленнее на несколько минут чем с ячейками((
Вот вариант с массивом:
Летучие функции это формулы? Ничего такого нет, я все пробую на новой чистой книге.
Скрины исходных данных и таблица куда это все пишется. Один столбик одна сортировка:
Там идет сравнение уникальных ячеек, при точном совпадении переносятся ячейки с целыми числами.
Я далеко не претендую на оптимальность кода, я всего неделю программирую. Если вы можете предложить иной алгоритм сравнения, который повысит скорость, то буду очень благодарен за любой совет как увеличить скорость. 227
Летучие функции это формулы? Ничего такого нет, я все пробую на новой чистой книге.
Скрины исходных данных и таблица куда это все пишется. Один столбик одна сортировка:
Там идет сравнение уникальных ячеек, при точном совпадении переносятся ячейки с целыми числами.
Я далеко не претендую на оптимальность кода, я всего неделю программирую. Если вы можете предложить иной алгоритм сравнения, который повысит скорость, то буду очень благодарен за любой совет как увеличить скорость. Автор - 227
Дата добавления - 03.05.2015 в 23:08
Вот просто с переносом ячеек пробовал, тут вообще не совсем правильно, тут поиск идет по всему листу а не по диапазону, что должно быть дольше, но как ни странно, этот вариант работает быстрее остальных.
Вот просто с переносом ячеек пробовал, тут вообще не совсем правильно, тут поиск идет по всему листу а не по диапазону, что должно быть дольше, но как ни странно, этот вариант работает быстрее остальных.
[/vba] Автор - 227Дата добавления - 03.05.2015 в 23:15 1.Так пример данных в файле будет? Строк так на 100 в каждом?
2.Это у Вас не Мак случаем? Что-то месиджи странные. 1.Так пример данных в файле будет? Строк так на 100 в каждом?
2.Это у Вас не Мак случаем? Что-то месиджи странные. Hugo 1. Hugo, тестовый файл прикрепляю.
2. мак. неужели маковский эксель так тормозит? 1. Hugo, тестовый файл прикрепляю.
2. мак. неужели маковский эксель так тормозит? 227 В Маке нет словарей, что исключает обработку за пару секунд, как в Винде.
Почему бы не использовать формулу?
Можно записать её применение рекордером, в конце добавить замену формул на полученные результаты. В Маке нет словарей, что исключает обработку за пару секунд, как в Винде.
Почему бы не использовать формулу?
Можно записать её применение рекордером, в конце добавить замену формул на полученные результаты. Hugo
Можно записать её применение рекордером, в конце добавить замену формул на полученные результаты. Автор - Hugo
Дата добавления - 04.05.2015 в 01:48
В принципе да, здесь должно хватать и обычного ВПР() (или его заменителя).
Особенно, если таблицу на листе "Сортировка" есть возможность сразу предварительно отсортировать по первому столбцу-коду.
Или нужно это проделывать именно и только кодом, на VBA?
Тогда наибольшее быстродействие на больших объемах показывать будет запрос к данным. Например, с использованием MSQuery. Проще было бы, конечно, используя ADO или DAO,но я не в курсе, поддерживаются ли эти провайдеры на маках.
Да и даже свой алгоритм можно сделать, достаточно простой, если использовать заранее отсортированные списки. Изабыть на время об использовании .Find
[vba] [/vba]
По поводу второго вашего куска кода тоже могу дать намёк:
[vba]
[/vba]не кажется ли, что вы два раза ищете одно и тоже?
Не говоря уже о том, что вам был дан нулевой намёк ранее - отключите пересчет и обновление листа на время исполнения кода.
В принципе да, здесь должно хватать и обычного ВПР() (или его заменителя).
Особенно, если таблицу на листе "Сортировка" есть возможность сразу предварительно отсортировать по первому столбцу-коду.
Или нужно это проделывать именно и только кодом, на VBA?
Тогда наибольшее быстродействие на больших объемах показывать будет запрос к данным. Например, с использованием MSQuery. Проще было бы, конечно, используя ADO или DAO,но я не в курсе, поддерживаются ли эти провайдеры на маках.
Да и даже свой алгоритм можно сделать, достаточно простой, если использовать заранее отсортированные списки. Изабыть на время об использовании .Find
[vba] [/vba]
По поводу второго вашего куска кода тоже могу дать намёк:
[vba]
[/vba]не кажется ли, что вы два раза ищете одно и тоже?
Не говоря уже о том, что вам был дан нулевой намёк ранее - отключите пересчет и обновление листа на время исполнения кода.
Кроме того, я вам могу намекнуть ещё на то, что "пробовали вы с массивами" вообще как-то этак. Вам посоветовали "все данные занести в массивы и работать уже только с ними (т.е. сравнение вести уже в них), а вы что сделали? Ну и открою вам великую тайну - в Excel данные любого диапазона ячеек - и так массив, поэтому чтение с листа (или запись на него) не требует использования циклов:[vba] [/vba]
По поводу второго вашего куска кода тоже могу дать намёк:
[vba]
[/vba]не кажется ли, что вы два раза ищете одно и тоже?
Не говоря уже о том, что вам был дан нулевой намёк ранее - отключите пересчет и обновление листа на время исполнения кода.
Автор - AndreTMДата добавления - 04.05.2015 в 03:18
AndreTM, сейчас так и делаем, сортировка а-я, выделенине цветом дубликатов, сортировка по цвету, перестановка и тд. Довольно много рутинных действий, которые хотелось бы автоматизировать.
Я не совсем корректно сделал тестовый файл, просто сделал схематически. Вообще на эталонном листе Общий имеется много столбиков с разной инфой не по алфавиту, и порядок и количество артикулов на листе Сортировка не совпадает с Общий, по этому и поиск. От этой сортировки требуется только добавить дополнительный столбик на первый лист.
MSQuery для меня пока это сложновато(
AndreTM, сейчас так и делаем, сортировка а-я, выделенине цветом дубликатов, сортировка по цвету, перестановка и тд. Довольно много рутинных действий, которые хотелось бы автоматизировать.
Я не совсем корректно сделал тестовый файл, просто сделал схематически. Вообще на эталонном листе Общий имеется много столбиков с разной инфой не по алфавиту, и порядок и количество артикулов на листе Сортировка не совпадает с Общий, по этому и поиск. От этой сортировки требуется только добавить дополнительный столбик на первый лист.
MSQuery для меня пока это сложновато( 227
AndreTM, сейчас так и делаем, сортировка а-я, выделенине цветом дубликатов, сортировка по цвету, перестановка и тд. Довольно много рутинных действий, которые хотелось бы автоматизировать.
Я не совсем корректно сделал тестовый файл, просто сделал схематически. Вообще на эталонном листе Общий имеется много столбиков с разной инфой не по алфавиту, и порядок и количество артикулов на листе Сортировка не совпадает с Общий, по этому и поиск. От этой сортировки требуется только добавить дополнительный столбик на первый лист.
MSQuery для меня пока это сложновато( Автор - 227
Дата добавления - 04.05.2015 в 10:57
В предыдущей статье Сравнение таблиц в Excel мы рассмотрели подход к сравнению сложных таблиц с использованием формул и без программирования.
В данной статье рассмотрим способ сравнения таблиц Excel с помощью VBA макросов на примере тех же исходных данных.
Проиллюстрируем задачу картинкой из первой статьи.
Для начала напишем алгоритм наших действий по сравнению таблиц.
- Определим диапазоны данных первой и второй таблицы, то есть найдем последние значимые строки и сохраним их номера в переменных (последняя строка таблицы 1 - last_i и последняя строка таблицы 2 - last_j).
- Начнем проходить по каждой строке таблицы 2 (внешний цикл), данные из которой нужно перенести в таблицу 1. С первой строки данных (в примере это строка 3) до последней строки таблицы 2.
- Для каждой строки таблицы 2 определим идентификатор строки, путем формирования строки, содержащей полный адрес квартиры (значения из нескольких колонок, разделенные дефисами).
- Начнем проходить по каждой строке таблицы 1 (внутренний цикл) с первой строки данных (в примере это строка 3) до последней строки таблицы 1, определяя при этом идентификатор строки.
- Сравним значения идентификаторов строк таблицы 1 и таблицы 2.
- Если идентификаторы равны, перепишем ФИО покупателя из ячейки таблицы 2 в соответствующую ячейку таблицы 1; прервем внутренний цикл по таблице 1 и перейдем к следующей строке таблицы 2 (переход к п.2).
Теперь остается реализовать алгоритм в виде программного кода макроса.
Для этого откроем вкладку Вид ленты функций Excel. Щелкнем на нижнюю часть со стрелкой кнопки Макросы. В открывшемся подменю выберем Запись макроса. В результате начнется запись нового макроса. Поскольку код мы будем формировать вручную, то еще раз зайдем в подменю макросов и выберем Остановить запись. Далее еще раз войдем в подменю макросов и выберем Макросы.
В появившемся диалоге выделим наш макрос и нажмем Изменить.
На экране откроется окно редактора макросов Visual Basic for Applications. В области кода (правая верхняя область) отображается код только что созданного пустого макроса.
В процедуру Макрос1 (между объявлениями начала и конца процедуры: Sub и End Sub) необходимо вставить код, решающий поставленную задачу. Образец кода представлен ниже.
Результат решения задачи:
Читайте также: