Овај водич ће показати како се користе методе Финд анд Реплаце у програму Екцел ВБА.
ВБА Финд
Екцел има одлично уграђено Финд и Пронађи и замени алата.
Могу се активирати помоћу пречица ЦТРЛ + Ф. (Пронађи) или ЦТРЛ + Х (Замените) или преко траке: Почетна> Уређивање> Пронађи и изабери.
Кликом Опције, можете видети напредне опције претраживања:
Можете лако приступити методама Финд анд Реплаце користећи ВБА. Ове уграђене методе су далеко брже од свега што бисте сами могли написати у ВБА.
Пронађите пример ВБА
Да бисмо демонстрирали функцију Финд, креирали смо следећи скуп података у Схеет1.
Ако желите да пратите, унесите податке у своју радну свеску.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
ВБА Финд без опционих параметара
Када користите методу ВБА Финд, постоји много опционалних параметара које можете поставити.
Препоручујемо да дефинишете све параметре кад год користите методу проналажења!
Ако не дефинишете опционалне параметре, ВБА ће користити тренутно изабране параметре у Екцел -овом прозору Финд. То значи да можда не знате који се параметри претраге користе при покретању кода. Налаз се може покренути у целој радној свесци или на листу. Може тражити формуле или вредности. Нема начина да сазнате, осим ако ручно не проверите шта је тренутно изабрано у Екцел -овом прозору за проналажење.
Ради једноставности, почећемо са примером без дефинисаних опционих параметара.
Пример једноставног проналажења
Погледајмо једноставан пример Финд:
| 123456789 | Суб ТестФинд ()Дим МиРанге Ас РангеСет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("Емплоиее")МсгБок МиРанге.АддрессМсгБок МиРанге.ЦолумнМсгБок МиРанге.РовЕнд Суб |
Овај код тражи „запосленог“ у коришћеном опсегу листа1. Ако пронађе „запосленика“, додијелит ће први пронађени распон варијабли распона МиРанге.
Затим ће се приказати оквири порука са адресом, колоном и редом пронађеног текста.
У овом примеру се користе подразумеване поставке Финд (под претпоставком да нису промењене у Екцел -овом прозору Финд):
- Текст за претрагу се делимично подудара са вредношћу ћелије (није потребно тачно подударање ћелије)
- Претрага не разликује велика и мала слова.
- Финд тражи само један радни лист
Ове поставке се могу мењати различитим опционим параметрима (о којима ће бити речи у наставку).
Пронађите белешке о методама
- Финд не бира ћелију у којој се налази текст. Он само идентификује пронађени опсег, којим можете манипулисати у свом коду.
- Метод Финд ће лоцирати само прву пронађену инстанцу.
- Можете користити џокер знакове (*) нпр. потражите „Е*“
Ништа није пронађено
Ако текст за претрагу не постоји, објекат опсега ће остати празан. Ово узрокује велики проблем када ваш код покуша приказати вредности локације јер оне не постоје. Ово ће довести до поруке о грешци коју не желите.
На срећу, можете да тестирате празан објекат опсега унутар ВБА користећи Ис Оператор:
| 1 | Ако не, МиРанге није ништа |
Додавање кода у наш претходни пример:
| 12345678910111213 | Суб ТестФинд ()Дим МиРанге Ас РангеСет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("Емплоиее")Ако није, онда МиРанге није ништаМсгБок МиРанге.АддрессМсгБок МиРанге.ЦолумнМсгБок МиРанге.РовИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
Пронађи параметре
До сада смо погледали само основни пример коришћења методе Финд. Међутим, на располагању су бројни опционални параметри који ће вам помоћи да прецизирате претрагу
| Параметер | Тип | Опис | Вредности |
| Шта | Потребан | Вредност за тражење | Било који тип података, попут низа или нумеричких |
| После | Опционо | Референца једне ћелије за почетак претраге | Адреса ћелије |
| Погледај у | Опционо | За претрагу користите формуле, вредности, коментаре | клВалуес, клФормулас, клЦомментс |
| Погледати на | Опционо | Ускладите део или целу ћелију | клВхоле, клПарт |
| СеарцхОрдер | Опционо | Налог за претраживање у - редовима или колонама | клБиРовс, клБиЦолуммнс |
| СеарцхДирецтион | Опционо | Смер за претрагу - напред или назад | клНект, клПретходно |
| МатцхЦасе | Опционо | Претраживање разликује велика или мала слова | Тачно или Нетачно |
| МатцхБите | Опционо | Користи се само ако сте инсталирали подршку за двобајтне језике, нпр. кинески језик | Тачно или Нетачно |
| СеарцхФормат | Опционо | Дозволите претраживање према формату ћелије | Тачно или Нетачно |
После параметра и пронађи више вредности
Ви користите Након параметра да бисте навели почетну ћелију за своју претрагу. Ово је корисно ако постоји више од једне инстанце вредности коју тражите.
Ако је претрага већ пронашла једну вриједност и знате да ће бити пронађено још вриједности, тада користите методу Финд са параметром ‘Афтер’ за снимање прве инстанце, а затим ту ћелију користите као полазну тачку за сљедеће претраживање.
Ово можете користити за проналажење више примерака текста за претрагу:
| 123456789101112131415161718192021222324252627282930313233343536 | Суб ТестМултиплеФиндс ()Дим МиРанге ас Ранге, ОлдРанге Ас Ранге, ФиндСтр Ас Стринг„Потражите прву инстанцу„ „Светлост и топлота“Сет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("Лигхт & Хеат")'Ако није пронађен, изађитеАко МиРанге није ништа, изађите из Суб'Прикажи прву адресу која је пронађенаМсгБок МиРанге.Аддресс'Направите копију објекта рангеПостави ОлдРанге = МиРанге'Додајте адресу низу који раздваја са "|" карактерФиндСтр = ФиндСтр & "|" & МиРанге.Аддресс„Поновите опсег тражећи друге случајевеУрадити„Потражите„ Светлост и топлота “користећи претходно пронађену адресу као параметар АфтерСет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("Лигхт & Хеат", Афтер: = Ранге (ОлдРанге.Аддресс))'Ако је адреса већ пронађена, изађите из до петље - ово зауставља непрекидно петљеАко ИнСтр (ФиндСтр, МиРанге.Аддресс), онда изађите из Уради'Прикажи најновију адресуМсгБок МиРанге.Аддресс'Додајте најновију адресу низу адресаФиндСтр = ФиндСтр & "|" & МиРанге.Аддресс'направите копију тренутног опсегаПостави ОлдРанге = МиРангеЛоопЕнд Суб |
Овај код ће се понављати кроз коришћени опсег и приказаће адресу сваки пут када пронађе инстанцу „Светлост и топлота“
Имајте на уму да ће се код понављати све док се у ФиндСтр -у не пронађе дупликат адресе, у том случају ће изаћи из петље До.
ЛоокИн Параметер
Можете користити Параметар ЛоокИн да бисте навели у којој ћелији ћелију желите да претражујете. Можете да наведете вредности, формуле или коментаре у ћелији.
- клВалуес - Претражује вредности ћелија (коначна вредност ћелије након израчуна)
- клФормулас - Претраге унутар саме формуле ћелије (све што је унето у ћелију)
- клЦомментс - Претражује белешке ћелије
- клЦомментсТхреадед - Претраге унутар коментара ћелије
Под претпоставком да је формула унесена на радни лист, помоћу овог примера кода можете пронаћи прву локацију било које формуле:
| 12345678910 | Суб ТестЛоокИн ()Дим МиРанге Ас РангеПодесите МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("=", ЛоокИн: = клФормулас)Ако није, онда МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
Ако је параметар „ЛоокИн“ постављен на клВалуес, код би приказао поруку „Није пронађено“. У овом примеру ће вратити Б10.
Коришћење ЛоокАт параметра
Тхе Параметар ЛоокАт одређује да ли ће финд тражити тачно подударање ћелије или ће тражити било коју ћелију која садржи вредност претраживања.
- клВхоле - Захтева да цела ћелија одговара вредности претраге
- клПарт - Унутар ћелије тражи низ за претрагу
Овај пример кода ће лоцирати прву ћелију која садржи текст „лигхт“. Витх Погледај: = клПарт, вратиће меч за „Лигхт & Хеат“.
| 123456789 | Суб ТестЛоокАт ()Дим МиРанге Ас РангеСет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("лигхт", Лоокат: = клПарт)Ако не, МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
Ако клВхоле је постављено, подударање би се вратило само ако је вредност ћелије „светла“.
Параметар СеарцхОрдер
Тхе Параметар СеарцхОрдер диктира како ће се претрага вршити у читавом опсегу.
- клРовс - Претрага се врши ред по ред
- клКсолумнс - Претрага се врши колона по колона
| 123456789 | Суб ТестСеарцхОрдер ()Дим МиРанге Ас РангеПостави МиРанге = Табеле ("Лист1"). УседРанге.Финд ("запослени", СеарцхОрдер: = клЦолумнс)Ако не, МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
Ово утиче на то који ће се пар први наћи.
Користећи тестне податке унете у радни лист раније, када је редослед претраживања колоне, лоцирана ћелија је А5. Када се параметар налога за претрагу промени у клРовс, ћелија која се налази је Ц4
Ово је важно ако имате дупле вредности у опсегу претраге и желите да пронађете прву инстанцу под одређеним именом колоне.
Параметар СеарцхДирецтион
Тхе Параметар СеарцхДирецтион диктира у ком ће правцу претрага ићи - ефикасно напред или назад.
- клНект - Потражите следећу одговарајућу вредност у опсегу
- клПревиоус - Потражите претходну одговарајућу вредност у опсегу
Опет, ако постоје дуплиране вредности унутар опсега претраге, то може имати утицај на то да се прво пронађе.
| 12345678910 | Суб ТестСеарцхДирецтион ()Дим МиРанге Ас РангеПостави МиРанге = Табеле ("Лист1"). УседРанге.Финд ("топлота", СеарцхДирецтион: = клПретходно)Ако није, онда МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
Користећи овај код на тест подацима, правац претраживања клПревиоус ће вратити локацију Ц9. Коришћење параметра клНект ће вратити локацију А4.
Параметар Нект значи да ће претраживање започети у горњем левом углу опсега претраге и радити надоле. Претходни параметар значи да ће претраживање започети у доњем десном углу опсега претраживања и радити према горе.
МатцхБите параметар
Тхе МатцхБие параметар се користи само за језике који користе двоструки бајт за представљање сваког знака, као што су кинески, руски и јапански.
Ако је овај параметар постављен на „Тачно“, Финд ће одговарати само двобајтним знаковима са двобајтним знаковима. Ако је параметар подешен на „Фалсе“, тада ће се двобајтни знак подударати са једнозначним или двобајтним знаковима.
Параметар СеарцхФормат
Тхе Параметар СеарцхФормат омогућава вам да тражите одговарајуће формате ћелија. Ово може бити одређени фонт који се користи, подебљани фонт или боја текста. Пре него што употребите овај параметар, морате подесити формат потребан за претрагу помоћу својства Апплицатион.ФиндФормат.
Ево примера како се користи:
| 12345678910111213 | Суб ТестСеарцхФормат ()Дим МиРанге Ас РангеАпплицатион.ФиндФормат.ЦлеарАпплицатион.ФиндФормат.Фонт.Болд = ТачноПостави МиРанге = Табеле ("Лист 1"). УседРанге.Финд ("топлота", Формат претраге: = Тачно)Ако није, онда МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоАпплицатион.ФиндФормат.ЦлеарЕнд Суб |
У овом примеру, ФиндФормат својство је подешено да тражи подебљани фонт. Наредба Финд затим тражи реч „топлота“ постављајући параметар СеарцхФормат на Тачно тако да ће вратити инстанцу тог текста само ако је фонт подебљан.
У примерима података радног листа који су приказани раније, ово ће вратити А9, која је једина ћелија која садржи реч „топлота“ подебљаним фонтом.
Уверите се да је својство ФиндФормат обрисано на крају кода. Ако не, ваша следећа претрага ће то ипак узети у обзир и вратити погрешне резултате.
Тамо где користите параметар СеарцхФормат, можете користити и заменски знак (*) као вредност претраге. У овом случају ће тражити било коју вредност подебљаним фонтом:
| 1 | Постави МиРанге = Табеле ("Схеет1"). УседРанге.Финд ("*", Формат претраживања: = Труе) |
Коришћење више параметара
Сви овде описани параметри претраживања могу се користити у комбинацији једни с другима ако је потребно.
На пример, могли бисте да комбинујете параметар „ЛоокИн“ са параметром „МатцхЦасе“ тако да прегледате цео текст ћелије, али он разликује мала и велика слова
| 123456789 | Под ТестМултиплеПараметерс ()Дим МиРанге Ас РангеСет МиРанге = Схеетс ("Схеет1"). УседРанге.Финд ("Лигхт & Хеат", ЛоокАт: = клВхоле, МатцхЦасе: = Труе)Ако не, МиРанге није ништаМсгБок МиРанге.АддрессИначеМсгБок "Није пронађено"Крај АкоЕнд Суб |
У овом примеру, код ће вратити А4, али ако смо користили само део текста нпр. „Топлота“, ништа се не би нашло јер се по целој вредности ћелије подударамо. Такође, то би пропало због тога што се случај не подудара.
| 1 | Постави МиРанге = Табеле ("Лист 1"). УседРанге.Финд ("топлота", ЛоокАт: = клВхоле, МатцхЦасе: = Труе) |
Замените у програму Екцел ВБА
Као што можете очекивати, постоји функција Замијени у програму Екцел ВБА, која ради на врло сличан начин као и „Пронађи“, али замјењује вриједности на локацији ћелије која се налази новом вриједношћу.
Ово су параметри које можете користити у наредби Реплаце метход. Они раде на потпуно исти начин као и за исказ методе Финд. Једина разлика у односу на „Пронађи“ је та што морате да наведете параметар замене.
| Име | Тип | Опис | Вредности |
| Шта | Потребан | Вредност за тражење | Било који тип података, попут низа или нумеричких |
| Замена | Потребан | Низ за замену. | Било који тип података, попут низа или нумеричких |
| Погледати на | Опционо | Ускладите део или целу ћелију | клПарт или клВхоле |
| СеарцхОрдер | Опционо | Налог за претраживање у - Редови или Колоне | клБиРовс или клБиЦолумнс |
| МатцхЦасе | Опционо | Претраживање разликује велика или мала слова | Тачно или Нетачно |
| МатцхБите | Опционо | Користи се само ако сте инсталирали подршку за двобајтне језике | Тачно или Нетачно |
| СеарцхФормат | Опционо | Дозволите претраживање према формату ћелије | Тачно или Нетачно |
| РеплацеФормат | Опционо | Формат замене методе. | Тачно или Нетачно |
Параметар Реплаце Формат тражи ћелију са одређеним форматом, нпр. подебљано на исти начин на који параметар СеарцхФормат ради у методи Финд. Морате прво поставити својство Апплицатион.ФиндФормат, као што је приказано у раније наведеном коду Финд
Замените без опционих параметара
Најједноставније речено, морате само да наведете шта тражите и чиме желите да га замените.
| 123 | Суб ТестРеплаце ()Табеле („Лист1“). УседРанге.Реплаце Вхат: = "Светлост и топлота", Замена: = "Л & Х"Енд Суб |
Имајте на уму да ће метода Финд вратити само прву инстанцу вредности која се подудара, док метода Реплаце функционише кроз читав наведени опсег и замењује све на чему пронађе подударање.
Код замене приказан овде ће заменити сваку инстанцу „Светлост и топлота“ са „Л и Х“ кроз читав низ ћелија дефинисаних објектом УседРанге
Коришћење ВБА за проналажење или замену текста у текстуалном низу ВБА
Горе наведени примери одлично функционишу када користите ВБА за интеракцију са Екцел подацима. Међутим, за интеракцију са ВБА низовима можете користити уграђене ВБА функције попут ИНСТР и РЕПЛАЦЕ.
Можете користити ИНСТР функција за лоцирање низа текста унутар дужег низа.
| 123 | Суб ТестИнстр ()МсгБок ИнСтр ("Ово је низ МиТект", "МиТект")Енд Суб |
Овај пример кода ће вратити вредност 9, што је бројчана позиција у којој се „МиТект“ налази у низу који се тражи.
Имајте на уму да разликује велика и мала слова. Ако је „МиТект“ мала слова, биће враћена вредност 0, што значи да стринг за претрагу није пронађен. У наставку ћемо разговарати о томе како онемогућити осетљивост на велика и мала слова.
ИНСТР - Почни
Доступна су још два опциона параметра. Можете одредити почетну тачку за претрагу:
| 1 | МсгБок ИнСтр (9, "Ово је низ МиТект", "МиТект") |
Почетна тачка је наведена као 9 па ће се и даље враћати 9. Ако је почетна тачка била 10, онда би се вратила 0 (без подударања) јер би почетна тачка била превише напред.
ИНСТР - Осетљивост великих и малих слова
Такође можете поставити параметар Цомпаре на вбБинариЦомпаре или вбТектЦомпаре. Ако поставите овај параметар, наредба мора имати вредност почетног параметра.
- вбБинариЦомпаре - велика и мала слова (подразумевано)
- вбТектЦомпаре - Не разликују
| 1 | МсгБок ИнСтр (1, "Ово је низ МиТект", "митект", вбТектЦомпаре) |
Ова изјава ће и даље враћати 9, иако је текст за претраживање малим словима.
Да бисте онемогућили разликовање малих и великих слова, такође можете прогласити опцију Упореди текст на врху модула кода.
ВБА функција замене
Ако желите да замените знакове у низу са различитим текстом у вашем коду, онда је метода Реплаце идеална за ово:
| 123 | Суб ТестРеплаце ()МсгБок Реплаце ("Ово је низ МиТект", "МиТект", "Ми Тект")Енд Суб |
Овај код замењује „МиТект“ са „Ми Тект“. Имајте на уму да низ за претрагу разликује велика и мала слова јер је бинарно поређење подразумевано.
Такође можете додати друге опционалне параметре:
- Почетак - дефинише позицију у почетном низу од које замена мора да почне. За разлику од методе Финд, она враћа скраћени низ који почиње од броја знакова дефинисаног параметром Старт.
- Цоунт - дефинише број замена које треба извршити. Подразумевано, Реплаце ће променити сваку инстанцу пронађеног текста за претрагу, али то можете ограничити на једну замену постављањем параметра Цоунт на 1
- Упоредити - као у Финд методи можете одредити бинарну претрагу или текстуалну претрагу користећи вбБинариЦомпаре или вбТектЦомпаре. Бинарно разликује велика и мала слова, а текст не разликује велика и мала слова
| 1 | МсгБок Реплаце ("Ово је низ МиТект (митект)", "МиТект", "Ми Тект", 9, 1, вбТектЦомпаре) |
Овај код враћа „Мој текстуални низ (мој текст)“. То је зато што је почетна тачка 9, па нови враћени низ почиње од карактера 9. Промењен је само први „Мој текст“ јер је параметар Цоунт постављен на 1.
Метод замене је идеалан за решавање проблема попут имена људи који садрже апострофе, нпр. О’Флинн. Ако користите једноструке наводнике за дефинисање вредности низа и постоји апостроф, то ће изазвати грешку јер ће код тумачити апостроф као крај низа и неће препознати остатак низа.
Можете користити методу замене да бисте апостроф заменили ничим, потпуно га уклонивши.
