Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск значений на листах и заливка исходных ячеек - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Поиск значений на листах и заливка исходных ячеек (Макросы/Sub)
Поиск значений на листах и заливка исходных ячеек
giovanni Дата: Воскресенье, 16.10.2016, 16:03 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Добрый день!

В макросах совсем не силен, но кое что уже научился понимать :-)
Возможно, для знатоков VBA задача покажется легкой, но для моего уровня познаний в VBA - она сложна.

Ситуация следующая. Есть книга в эксель с несколькими листами (в примере их 4). На каждом листе в одинаковых столбцах (столбец "C") находятся данные.
На отдельном листе (назовем его "Исходный") в столбце "F" находятся определенные значения.
Задача в том, чтобы поочередно, начиная с первой ячейки искать данные из листа "Исходный", искать по остальным четырем листам.
Искомое значение в книге может встречаться только один раз на каком-то из листов. В результате нахождения искомого значения, необходимо определить
на каком из листов данное значение найдено и в зависимости от имени листа, на котором найдено значение, закрасить ячейку на листе "Исходный", из которой взят текст для поиска, в определенный цвет.
К примеру, если значение из ячейки "F1" на листе "Исходный" найдено на Листе3, то ячейка "F1" листа "Исходный", закрашивается в синий цвет.
Прочитав записи на различных форумах VBA написал код, с помощью которого производится поиск одной ячейки, но как распространить поиск на все ячейки столбца "F" листа "Исходный", так и не понял :-(
Так же, понимаю, как залить ячейку средствами VBA, но не смог понять, как залить ячейку по результатам поиска :-(

Надеюсь, что написал не слишком непонятно. Приложил файл с примером.
Помогите, пожалуйста в данном вопросе. Либо подскажите примерно как можно сконструировать нужный макрос, постараюсь сам додумать)

Спасибо!
К сообщению приложен файл: 9051109.xlsm(20Kb)
 
Ответить
СообщениеДобрый день!

В макросах совсем не силен, но кое что уже научился понимать :-)
Возможно, для знатоков VBA задача покажется легкой, но для моего уровня познаний в VBA - она сложна.

Ситуация следующая. Есть книга в эксель с несколькими листами (в примере их 4). На каждом листе в одинаковых столбцах (столбец "C") находятся данные.
На отдельном листе (назовем его "Исходный") в столбце "F" находятся определенные значения.
Задача в том, чтобы поочередно, начиная с первой ячейки искать данные из листа "Исходный", искать по остальным четырем листам.
Искомое значение в книге может встречаться только один раз на каком-то из листов. В результате нахождения искомого значения, необходимо определить
на каком из листов данное значение найдено и в зависимости от имени листа, на котором найдено значение, закрасить ячейку на листе "Исходный", из которой взят текст для поиска, в определенный цвет.
К примеру, если значение из ячейки "F1" на листе "Исходный" найдено на Листе3, то ячейка "F1" листа "Исходный", закрашивается в синий цвет.
Прочитав записи на различных форумах VBA написал код, с помощью которого производится поиск одной ячейки, но как распространить поиск на все ячейки столбца "F" листа "Исходный", так и не понял :-(
Так же, понимаю, как залить ячейку средствами VBA, но не смог понять, как залить ячейку по результатам поиска :-(

Надеюсь, что написал не слишком непонятно. Приложил файл с примером.
Помогите, пожалуйста в данном вопросе. Либо подскажите примерно как можно сконструировать нужный макрос, постараюсь сам додумать)

Спасибо!

Автор - giovanni
Дата добавления - 16.10.2016 в 16:03
KuklP Дата: Воскресенье, 16.10.2016, 16:49 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 2000
Репутация: 436 ±
Замечаний: 0% ±

[vba]
Код
Sub ShowAdrCells()
    Dim TxtForFind As String, RngForFind As Range, i As Integer, c As Range
    For Each c In Intersect(Sheets("Исходный").UsedRange, Sheets("Исходный").Columns(6)).Cells
        If c.Value <> "" Then
            TxtForFind = c.Value
            For i = 2 To Sheets.Count
                With ThisWorkbook.Sheets(i).Columns("C")
                    Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not RngForFind Is Nothing Then
                        c.Interior.ColorIndex = i + 2
                        ' MsgBox "Искомое значение найдено в ячейке: " & RngForFind.Address(0, 0) & " на листе: " & Sheets(i).Name, 64, "Для сведения..."
                        Exit For
                    End If
                End With
            Next
        End If
    Next
End Sub
[/vba]


Ну, с НДС и мы чего-то стoим! kuklp@mail.ru
WM Z206653985942, R334086032478, U238399322728
 
Ответить
Сообщение[vba]
Код
Sub ShowAdrCells()
    Dim TxtForFind As String, RngForFind As Range, i As Integer, c As Range
    For Each c In Intersect(Sheets("Исходный").UsedRange, Sheets("Исходный").Columns(6)).Cells
        If c.Value <> "" Then
            TxtForFind = c.Value
            For i = 2 To Sheets.Count
                With ThisWorkbook.Sheets(i).Columns("C")
                    Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not RngForFind Is Nothing Then
                        c.Interior.ColorIndex = i + 2
                        ' MsgBox "Искомое значение найдено в ячейке: " & RngForFind.Address(0, 0) & " на листе: " & Sheets(i).Name, 64, "Для сведения..."
                        Exit For
                    End If
                End With
            Next
        End If
    Next
End Sub
[/vba]

Автор - KuklP
Дата добавления - 16.10.2016 в 16:49
Udik Дата: Воскресенье, 16.10.2016, 17:12 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1215
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
ещё вариант
[vba]
Код

Sub ShowAdrCells()
Dim TxtForFind As String, RngForFind As Range, FirstAddress As String, i As Integer
Dim rowLast As Integer, j%
Dim arrColor(1 To 4) As Long

arrColor(1) = 5296274
arrColor(2) = 15773696
arrColor(3) = 49407
arrColor(4) = 12566463

rowLast = ThisWorkbook.Worksheets("Исходный").Cells(Rows.Count, 6).End(xlUp).Row
For j = 1 To rowLast
TxtForFind = ThisWorkbook.Worksheets("Исходный").Cells(j, 6)
For i = 2 To Sheets.Count
With ThisWorkbook.Sheets(i).Columns("C")
Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngForFind Is Nothing Then
ThisWorkbook.Worksheets("Исходный").Cells(j, 6).Interior.Color = arrColor(i - 1)
FirstAddress = RngForFind.Address
Do
MsgBox "Искомое значение найдено в ячейке: " & RngForFind.Address(0, 0) & " на листе: " & Sheets(i).Name, 64, "Для сведения..."

Loop While RngForFind.Address <> FirstAddress
End If
End With
Next i
Next j
End Sub

[/vba]
[p.s.] чёт у меня предубеждение против метода Find - то не находит имеющиеся значения, то не ищет в 1 ячейке диапазона.
К сообщению приложен файл: 0t.xlsm(21Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 16.10.2016, 17:20
 
Ответить
Сообщениеещё вариант
[vba]
Код

Sub ShowAdrCells()
Dim TxtForFind As String, RngForFind As Range, FirstAddress As String, i As Integer
Dim rowLast As Integer, j%
Dim arrColor(1 To 4) As Long

arrColor(1) = 5296274
arrColor(2) = 15773696
arrColor(3) = 49407
arrColor(4) = 12566463

rowLast = ThisWorkbook.Worksheets("Исходный").Cells(Rows.Count, 6).End(xlUp).Row
For j = 1 To rowLast
TxtForFind = ThisWorkbook.Worksheets("Исходный").Cells(j, 6)
For i = 2 To Sheets.Count
With ThisWorkbook.Sheets(i).Columns("C")
Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngForFind Is Nothing Then
ThisWorkbook.Worksheets("Исходный").Cells(j, 6).Interior.Color = arrColor(i - 1)
FirstAddress = RngForFind.Address
Do
MsgBox "Искомое значение найдено в ячейке: " & RngForFind.Address(0, 0) & " на листе: " & Sheets(i).Name, 64, "Для сведения..."

Loop While RngForFind.Address <> FirstAddress
End If
End With
Next i
Next j
End Sub

[/vba]
[p.s.] чёт у меня предубеждение против метода Find - то не находит имеющиеся значения, то не ищет в 1 ячейке диапазона.

Автор - Udik
Дата добавления - 16.10.2016 в 17:12
giovanni Дата: Вторник, 18.10.2016, 01:04 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
ещё вариант


Доброй ночи!

Оба представленных вами макроса работают отлично, именно то, что надо) Огромное спасибо за помощь!

Если можно, подскажите еще по одному моменту в данном макросе.
В описании к вопросу мною было указано, что искомый текст встречается только один раз во всех листах книги, но тут я немного ошибся)
На самом деле искомый текст может встречаться на двух и более листах книги эксель.
В настоящее время, в файле, приложенном в Вашем посте, насколько я понял, макрос выбирает значение из последнего листа, если они повторяются на нескольких листах.
Подскажите, пожалуйста, можно ли сделать так, чтобы при нахождении методом Find искомого значения, при первом совпадении закрашивалась искомая ячейка, при втором совпадении искомое значение копировалось на один столбец правее искомого значения, при третьем совпадении искомое значение копировалось на еще один столбец правее и закрашивалось и т.д. К примеру, в приложенном файле-примере значение "1021" встречается на листах 1,3 и 4. Реально ли построить такой макрос?
Приложил свой пример на основе Вашего файла с примером.

Помогите, пожалуйста, если это реально.
Спасибо!

UPD: по сути, никак не могу понять, как обрабатывать поочередно несколько одинаковых значений, найденных на нескольких листах одрой книги и интегрировать это в макрос, который находится в приложенном файле
К сообщению приложен файл: 0t-1-.xlsm(22Kb)


Сообщение отредактировал giovanni - Вторник, 18.10.2016, 11:59
 
Ответить
Сообщение
ещё вариант


Доброй ночи!

Оба представленных вами макроса работают отлично, именно то, что надо) Огромное спасибо за помощь!

Если можно, подскажите еще по одному моменту в данном макросе.
В описании к вопросу мною было указано, что искомый текст встречается только один раз во всех листах книги, но тут я немного ошибся)
На самом деле искомый текст может встречаться на двух и более листах книги эксель.
В настоящее время, в файле, приложенном в Вашем посте, насколько я понял, макрос выбирает значение из последнего листа, если они повторяются на нескольких листах.
Подскажите, пожалуйста, можно ли сделать так, чтобы при нахождении методом Find искомого значения, при первом совпадении закрашивалась искомая ячейка, при втором совпадении искомое значение копировалось на один столбец правее искомого значения, при третьем совпадении искомое значение копировалось на еще один столбец правее и закрашивалось и т.д. К примеру, в приложенном файле-примере значение "1021" встречается на листах 1,3 и 4. Реально ли построить такой макрос?
Приложил свой пример на основе Вашего файла с примером.

Помогите, пожалуйста, если это реально.
Спасибо!

UPD: по сути, никак не могу понять, как обрабатывать поочередно несколько одинаковых значений, найденных на нескольких листах одрой книги и интегрировать это в макрос, который находится в приложенном файле

Автор - giovanni
Дата добавления - 18.10.2016 в 01:04
Udik Дата: Вторник, 18.10.2016, 13:24 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1215
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
как обрабатывать поочередно несколько одинаковых значений, найденных на нескольких листах одрой книги

Ну так можно видоизменить
[vba]
Код

Sub ShowAdrCells()
Dim TxtForFind As String, RngForFind As Range, FirstAddress As String, i As Integer
Dim rowLast As Integer, j%, k%
Dim arrColor(1 To 4) As Long

arrColor(1) = 5296274
arrColor(2) = 15773696
arrColor(3) = 49407
arrColor(4) = 12566463

rowLast = ThisWorkbook.Worksheets("Исходный").Cells(Rows.Count, 6).End(xlUp).Row
For j = 1 To rowLast
TxtForFind = ThisWorkbook.Worksheets("Исходный").Cells(j, 6)
k = 0
For i = 2 To Sheets.Count
With ThisWorkbook.Sheets(i).Columns("C")
Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngForFind Is Nothing Then
ThisWorkbook.Worksheets("Исходный").Cells(j, 6 + k * 2).Interior.Color = arrColor(i - 1)
ThisWorkbook.Worksheets("Исходный").Cells(j, 7 + k * 2).Value = ThisWorkbook.Sheets(i).Name
ThisWorkbook.Worksheets("Исходный").Cells(j, 6 + k * 2).Value = TxtForFind
k = k + 1
End If
End With
Next i
Next j
End Sub

[/vba]
К сообщению приложен файл: 8167577.xlsm(22Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
как обрабатывать поочередно несколько одинаковых значений, найденных на нескольких листах одрой книги

Ну так можно видоизменить
[vba]
Код

Sub ShowAdrCells()
Dim TxtForFind As String, RngForFind As Range, FirstAddress As String, i As Integer
Dim rowLast As Integer, j%, k%
Dim arrColor(1 To 4) As Long

arrColor(1) = 5296274
arrColor(2) = 15773696
arrColor(3) = 49407
arrColor(4) = 12566463

rowLast = ThisWorkbook.Worksheets("Исходный").Cells(Rows.Count, 6).End(xlUp).Row
For j = 1 To rowLast
TxtForFind = ThisWorkbook.Worksheets("Исходный").Cells(j, 6)
k = 0
For i = 2 To Sheets.Count
With ThisWorkbook.Sheets(i).Columns("C")
Set RngForFind = .Find(TxtForFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngForFind Is Nothing Then
ThisWorkbook.Worksheets("Исходный").Cells(j, 6 + k * 2).Interior.Color = arrColor(i - 1)
ThisWorkbook.Worksheets("Исходный").Cells(j, 7 + k * 2).Value = ThisWorkbook.Sheets(i).Name
ThisWorkbook.Worksheets("Исходный").Cells(j, 6 + k * 2).Value = TxtForFind
k = k + 1
End If
End With
Next i
Next j
End Sub

[/vba]

Автор - Udik
Дата добавления - 18.10.2016 в 13:24
giovanni Дата: Среда, 19.10.2016, 16:16 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Ну так можно видоизменить


Здравствуйте!

Огромное спасибо Вам за помощь! Макрос работает отлично!)

Если не сложно, подскажите, пожалуйста, по еще одному вопросу. Изучал Ваш макрос, многое мне понятно, хоть и не все :)
Хотел к макросу еще прикрутить получение дополнительного значения с помощью свойства Offset, но не понял как это реализовать.
Вопрос в следующем. Если в таблице искомое значение встречается несколько раз, то макрос показывает это на листе "Исходный",
добавляя найденное значение столько раз, сколько оно встречается.
Хотел реализовать следующее: к примеру, если макрос находит в столбце "J" искомый текст, то сделав от него Offset(x,y) на необходимое количество столбцов, скопировать значение ячейки (на которую произведено перемещение после Offset-а), на лист "Исходный", в необходимую ячейку.
Как реализовать Offset и скопировать значение из ячейки мне понятно, но не могу понять то, к чему применять Offset :)
Другими словами, если искомый текст встречается 3 раза в на разных листах книги, в какой переменной хранится адрес найденной ячейки,
каждый из трех раз?
Например, в файле с примером производится поиск значения "1021", оно встречается поочередно на листах 1,3 и 4. Как применить свойство Offset
к найденному значению "1021" на каждом листе?

Извиняюсь за большое количество вопросов в одной теме)

Спасибо!
 
Ответить
Сообщение
Ну так можно видоизменить


Здравствуйте!

Огромное спасибо Вам за помощь! Макрос работает отлично!)

Если не сложно, подскажите, пожалуйста, по еще одному вопросу. Изучал Ваш макрос, многое мне понятно, хоть и не все :)
Хотел к макросу еще прикрутить получение дополнительного значения с помощью свойства Offset, но не понял как это реализовать.
Вопрос в следующем. Если в таблице искомое значение встречается несколько раз, то макрос показывает это на листе "Исходный",
добавляя найденное значение столько раз, сколько оно встречается.
Хотел реализовать следующее: к примеру, если макрос находит в столбце "J" искомый текст, то сделав от него Offset(x,y) на необходимое количество столбцов, скопировать значение ячейки (на которую произведено перемещение после Offset-а), на лист "Исходный", в необходимую ячейку.
Как реализовать Offset и скопировать значение из ячейки мне понятно, но не могу понять то, к чему применять Offset :)
Другими словами, если искомый текст встречается 3 раза в на разных листах книги, в какой переменной хранится адрес найденной ячейки,
каждый из трех раз?
Например, в файле с примером производится поиск значения "1021", оно встречается поочередно на листах 1,3 и 4. Как применить свойство Offset
к найденному значению "1021" на каждом листе?

Извиняюсь за большое количество вопросов в одной теме)

Спасибо!

Автор - giovanni
Дата добавления - 19.10.2016 в 16:16
Pelena Дата: Среда, 19.10.2016, 21:14 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9862
Репутация: 2255 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
giovanni, сдаётся мне, этот вопрос к теме заливки ячеек уже не относится


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеgiovanni, сдаётся мне, этот вопрос к теме заливки ячеек уже не относится

Автор - Pelena
Дата добавления - 19.10.2016 в 21:14
giovanni Дата: Среда, 19.10.2016, 21:24 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
сдаётся мне, этот вопрос к теме заливки ячеек уже не относится. Создайте новую тему


Согласен с Вами, вопрос, конечно, уже из другой плоскости.
Если возможно, разрешите, пожалуйста, оставить данный вопрос в этой теме, т.к. данный вопрос относится к макросу, который был предоставлен как помощь в ответ на мой вопрос-тему.
Более продолжать данную тему не буду, просто очень жду ответа :)

Спасибо!
 
Ответить
Сообщение
сдаётся мне, этот вопрос к теме заливки ячеек уже не относится. Создайте новую тему


Согласен с Вами, вопрос, конечно, уже из другой плоскости.
Если возможно, разрешите, пожалуйста, оставить данный вопрос в этой теме, т.к. данный вопрос относится к макросу, который был предоставлен как помощь в ответ на мой вопрос-тему.
Более продолжать данную тему не буду, просто очень жду ответа :)

Спасибо!

Автор - giovanni
Дата добавления - 19.10.2016 в 21:24
Kuzmich Дата: Среда, 19.10.2016, 21:35 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 216
Репутация: 40 ±
Замечаний: 0% ±

Excel 2003
Цитата
не могу понять то, к чему применять Offset

RngForFind это ячейка, в которой нашли текст TxtForFind. Эта ячейка обладает
всеми свойствами объекта Range. Вот к нему и применяйте.
 
Ответить
Сообщение
Цитата
не могу понять то, к чему применять Offset

RngForFind это ячейка, в которой нашли текст TxtForFind. Эта ячейка обладает
всеми свойствами объекта Range. Вот к нему и применяйте.

Автор - Kuzmich
Дата добавления - 19.10.2016 в 21:35
giovanni Дата: Суббота, 29.10.2016, 21:04 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
RngForFind это ячейка, в которой нашли текст TxtForFind. Эта ячейка обладает
всеми свойствами объекта Range. Вот к нему и применяйте.


Огромное спасибо за помощь!
 
Ответить
Сообщение
RngForFind это ячейка, в которой нашли текст TxtForFind. Эта ячейка обладает
всеми свойствами объекта Range. Вот к нему и применяйте.


Огромное спасибо за помощь!

Автор - giovanni
Дата добавления - 29.10.2016 в 21:04
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Поиск значений на листах и заливка исходных ячеек (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!