Vba как найти значение в диапазоне

Метод Find объекта Range для поиска ячейки по ее данным в VBA Excel. Синтаксис и компоненты. Знаки подстановки для поисковой фразы. Простые примеры.

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.

Параметры метода Range.Find

Наименование Описание
Обязательный параметр
What Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра — Variant.
Необязательные параметры
After Ячейка, после которой следует начать поиск.
LookIn Уточняет область поиска. Список констант xlFindLookIn:

  • xlValues (-4163) – значения;
  • xlComments (-4144) – примечания*;
  • xlNotes (-4144) – примечания*;
  • xlFormulas (-4123) – формулы.
LookAt Поиск частичного или полного совпадения. Список констант xlLookAt:

  • xlWhole (1) – полное совпадение;
  • xlPart (2) – частичное совпадение.
SearchOrder Определяет способ поиска. Список констант xlSearchOrder:

  • xlByRows (1) – поиск по строкам;
  • xlByColumns (2) – поиск по столбцам.
SearchDirection Определяет направление поиска. Список констант xlSearchDirection:

  • xlNext (1) – поиск вперед;
  • xlPrevious (2) – поиск назад.
MatchCase Определяет учет регистра:

  • False (0) – поиск без учета регистра (по умолчанию);
  • True (1) – поиск с учетом регистра.
MatchByte Условия поиска при использовании двухбайтовых кодировок:

  • False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
  • True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
SearchFormat Формат поиска – используется вместе со свойством Application.FindFormat.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Знаки подстановки для поисковой фразы

Условные знаки в шаблоне поисковой фразы:

  • ? – знак вопроса обозначает любой отдельный символ;
  • * – звездочка обозначает любое количество любых символов, в том числе ноль символов;
  • ~ – тильда ставится перед ?, * и ~, чтобы они обозначали сами себя (например, чтобы тильда в шаблоне обозначала сама себя, записать ее нужно дважды: ~~).

Простые примеры

При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:

  1. Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
  2. Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.

В примерах используются переменные:

  • myPhrase – переменная для записи поисковой фразы;
  • myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.

Пример 1

Sub primer1()

Dim myPhrase As Variant, myCell As Range

myPhrase = “стакан”

Set myCell = Range(“A1:L30”).Find(myPhrase)

If Not myCell Is Nothing Then

MsgBox “Значение найденной ячейки: “ & myCell

MsgBox “Строка найденной ячейки: “ & myCell.Row

MsgBox “Столбец найденной ячейки: “ & myCell.Column

MsgBox “Адрес найденной ячейки: “ & myCell.Address

Else

MsgBox “Искомая фраза не найдена”

End If

End Sub

В этом примере мы присваиваем переменной myPhrase значение для поиска – "стакан". Затем проводим поиск этой фразы в диапазоне "A1:L30" с присвоением результата поиска переменной myCell. Далее проверяем переменную myCell, не содержит ли она значение Nothing, и выводим соответствующие сообщения.

Ознакомьтесь с работой кода VBA в случаях, когда в диапазоне "A1:L30" есть ячейка со строкой, содержащей подстроку "стакан", и когда такой ячейки нет.

Пример 2

Теперь посмотрим, как метод Range.Find отреагирует на поиск числа. В качестве диапазона поиска будем использовать первую строку активного листа Excel.

Sub primer2()

Dim myPhrase As Variant, myCell As Range

myPhrase = 526.15

Set myCell = Rows(1).Find(myPhrase)

If Not myCell Is Nothing Then

MsgBox “Значение найденной ячейки: “ & myCell

Else: MsgBox “Искомая фраза не найдена”

End If

End Sub

Несмотря на то, что мы присвоили переменной числовое значение, метод Range.Find найдет ячейку со значением и 526,15, и 129526,15, и 526,15254. То есть, как и в предыдущем примере, поиск идет по подстроке.

Чтобы найти ячейку с точным соответствием значения поисковой фразе, используйте константу xlWhole параметра LookAt:

Set myCell = Rows(1).Find(myPhrase, , , xlWhole)

Аналогично используются и другие необязательные параметры. Количество «лишних» запятых перед необязательным параметром должно соответствовать количеству пропущенных компонентов, предусмотренных синтаксисом метода Range.Find, кроме случаев указания необязательного параметра по имени, например: LookIn:=xlValues. Тогда используется одна запятая, независимо от того, сколько компонентов пропущено.

Пример 3

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

Sub primer3()

Dim myPhrase As Variant, myCell As Range

myPhrase = “01.02.2019”

myPhrase = CDate(myPhrase)

Set myCell = Range(“A:A”).Find(myPhrase)

If Not myCell Is Nothing Then

MsgBox “Номер начальной строки: “ & myCell.Row

Else: MsgBox “Даты “ & myPhrase & ” в таблице нет”

End If

End Sub

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

title keywords f1_keywords ms.prod api_name ms.assetid ms.date ms.localizationpriority

Range.Find method (Excel)

vbaxl10.chm144128

vbaxl10.chm144128

excel

Excel.Range.Find

d9585265-8164-cb4d-a9e3-262f6e06b6b8

08/14/2019

high

Range.Find method (Excel)

Finds specific information in a range.

[!includeAdd-ins note]

Syntax

expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression A variable that represents a Range object.

Parameters

Name Required/Optional Data type Description
What Required Variant The data to search for. Can be a string or any Microsoft Excel data type.
After Optional Variant The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface.

Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell.

If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.

LookIn Optional Variant Can be one of the following XlFindLookIn constants: xlFormulas, xlValues, xlComments, or xlCommentsThreaded.
LookAt Optional Variant Can be one of the following XlLookAt constants: xlWhole or xlPart.
SearchOrder Optional Variant Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
SearchDirection Optional Variant Can be one of the following XlSearchDirection constants: xlNext or xlPrevious.
MatchCase Optional Variant True to make the search case-sensitive. The default value is False.
MatchByte Optional Variant Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
SearchFormat Optional Variant The search format.

Return value

A Range object that represents the first cell where that information is found.

Remarks

This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Use the FindNext and FindPrevious methods to repeat the search.

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

To find cells that match more complicated patterns, use a For Each…Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When Microsoft Excel finds a match, it changes the font to Times New Roman.

For Each c In [A1:C5] If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next`

Examples

This example finds all cells in the range A1:A500 in worksheet one that contain the value 2, and changes the entire cell value to 5. That is, the values 1234 and 99299 both contain 2 and both cell values will become 5.

Sub FindValue()
    
    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500") 
        Set c = .Find(2, lookin:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Value = 5 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing
        End If 
    End With
    
End Sub

This example finds all cells in the range A1:A500 on worksheet one that contain the substring “abc” and then replaces “abc” with “xyz”.

Sub FindString()

    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500")
        Set c = .Find("abc", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = Replace(c.Value, "abc", "xyz")
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With

End Sub

[!includeSupport and feedback]

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

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке “A” ячейку, содержащую “123” можно примерно так:

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать “классического” способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую “123” достаточно такого кода:

Sheets("Данные").Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист “Данные”;
2-я строка: Осуществляем поиск значения “123” в колонке “A”, результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае – будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What – Строка с текстом, который ищем или любой другой тип данных Excel

After – Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn – Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt – Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder – Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection – Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase – Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte – Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat – Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать “далее”) или FindPrevious (искать “назад”).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне “A1:A50” все ячейки с текстом “asd” и поменять их все на “qwe”

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

В ниже следующем примере используется другой вариант продолжения поиска – с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом “курсив” и поменять их формат на обычный (не “курсив”)

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = .Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка – цикл пока результат поиска не будет пустым. 7-я строка – меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать “защиту от зацикливания”, как в Примерах 2 и 3, т.к. шрифт меняется и после “прохождения” по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

With Application.FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Поиск последней заполненной ячейки с помощью Find

Следующий пример – применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

Поиск по шаблону (маске)

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы “т”, при этом после этого слова может следовать любой текст.

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:
* – для обозначения любого количества любых символов;
? – для обозначения одного любого символа;
~ – для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не “01.03.2018”
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты – сложнее. Например, чтобы найти все ячейки, где месяц “март”, недостаточно искать “03” или “3”. Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел – это выбрать формат в котором месяц прописью для ячеек с датами и искать слово “март” в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells.Find(Format(d, "m/d/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

“I know well what I am fleeing from but not what I am in search of” – Michel de Montaigne

Contents

  • 1 Introduction
  • 2 Download the Source Code
  • 3 What is the VBA Find Function?
  • 4 Introduction
    • 4.1 Excel Find Dialog
    • 4.2 How to Use Options With Find
  • 5 VBA Find Parameters
    • 5.1 Important Note about Find Parameters
  • 6 The Find Return Value
  • 7 How to do a Simple Find
    • 7.1 When the Value is not Found
  • 8 Using After with Find
    • 8.1 Example 1 Without After
    • 8.2 Example 2 Using After
    • 8.3 Example 3 Wrapping Around
  • 9 Using LookIn with Find
  • 10 Using LookAt with Find
  • 11 Using SearchOrder with Find
  • 12 Using SearchDirection with Find
    • 12.1 Using xlPrevious with After
  • 13 Using MatchCase with Find
  • 14 Using MatchByte with Find
  • 15 Using the WildCard
  • 16 Using SearchFormat with Find
    • 16.1 Using Wild Card with Format
    • 16.2 Important – Clearing Format
  • 17 Multiple Searches
  • 18 Finding the Last Cell Containing Data
  • 19 Finding Cells with Patterns
  • 20 An Alternative to using VBA Find
  • 21 Find and Replace
  • 22 What’s Next?

Introduction

This post covers everything you need to know about the VBA Find function. It explains, how to use Find, in simple terms. It also has tons of code examples of Find you can use right now.

If you want to go straight to an example of Find then check out How to do a Simple Find.

If you want to search for text within a string then you are looking for the InStr and InStrRev functions.

If you want to find the last row or column with data then go to Finding the Last Cell Containing Data

Download the Source Code

What is the VBA Find Function?

The Find function is very commonly used in VBA. The three most important things to know about Find are:

  1. The Find function is a member of Range.
  2. It searches a range of cells containing a given value or format.
  3. It is essentially the same as using the Find Dialog on an Excel worksheet.

Introduction

Excel Find Dialog

To view the Excel Find dialog, go to the Home ribbon and click on Find & Select in the Editing section. In the menu that appears select Find(shortcut is Ctrl + F)

Excel VBA Find Ribbon

 
When  you do this the following dialog will appear:

Excel Find dialog

 
The VBA Find function uses most of the options you can see on this Dialog.

How to Use Options With Find

To use the options you pass them as parameters to the Find function. This is similar to how you use worksheet functions. For example, the Sum function has a Range as a parameter. This means you give it a range when you use it.

The VBA Find uses parameters in the same way. You must give it the item you are searching for. This is the first parameter and it is required.

The rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.

The table in the next section shows these parameters. The sections that follow this, give examples and details of how to use these parameters.

VBA Find Parameters

The following tables shows all the Find parameters.

Parameter Type Description Values
What Required The value you are searching for Any VBA data type e.g String, Long
After Optional A single cell range that you start your search from Range(“A5”)
LookIn Optional What to search in e.g. Formulas, Values or Comments xlValues, xlFormulas, xlComments
LookAt Optional Look at a part or the whole of the cell xlWhole, xlPart
SearchOrder Optional The order to search xlByRows or xlByColumns.
SearchDirection Optional The direction to search xlNext, xlPrevious
MatchCase Optional If search is case sensitive True or False
MatchByte Optional Used for double byte languages True or False
SearchFormat Optional Allow searching by format. The format is set using Application.FindFormat True or False

Important Note about Find Parameters

Keep the following in mind as it can cause a lot of frustration when using Find.

As you can see from the table most of the VBA Find parameters are optional. As we said earlier, if you don’t set a Find parameter it uses the existing setting.

For example, if you set the LookIn parameter to xlComments, it will search for a value in comments only. The next time you run Find(either from the Dialog or from VBA) the existing LookIn setting will be Comments.

The following code shows an example of this

' Search in comments only
Range("A1:A5").Find "John", LookIn:=xlComments
' Will search comments as this is the existing setting
Range("A1:A5").Find "John"

' Search in formulas only
Range("A1:A5").Find "John", LookIn:=xlFormulas
' Will search formulas as this is the existing setting
Range("A1:A5").Find "John"

 
This applies to the parameters LookIn, LookAt, SearchOrder, and MatchByte.

The Find Return Value

If the search item is found then Find returns  the cell with the value. That is, it returns a Range type of one cell.

If the search item is not found then Find returns an object set to Nothing.

In the following examples, you will see how to deal with the return value.

How to do a Simple Find

Let’s start with a simple example of the VBA Find. You need three things when using the Find function

  1. The Range to search
  2. The value you are searching for
  3. The Range to store the returned cell

 
Let’s take the following sample data

Excel VBA Find

 
We are going to search for the text “Jena” in the cells A1 to A5.

The following code searches for “Jena”. When it finds “Jena”, it then places the cell in the rgFound variable.

' Find the name Jena in the range A1:A5
Dim rgFound As Range
Set rgFound = Range("A1:A5").Find("Jena")

' Print cell address to Immediate Window(Ctrl + G)
Debug.Print rgFound.Address

 
The above code shows the most basic search you can do. If this is your first time using the VBA Find function then I recommend you practice with a simple example like this.

If you want to try these examples you can download the workbook from the top of this post.
 

When the Value is not Found

When you use the VBA Find function, there will be times when you do not find a match. You need to handle this in your code or you will get the following error when you try to use the returned range

Excel VBA Find

 
The following code will give this error if the text “John” is not found in the range A1 to A5

Set rgFound = Range("A1:A5").Find("John")

' Shows Error if John was not found
Debug.Print rgFound.Address

 
What we need to do is check the return value like the following code shows

Set rgFound= Range("A1:A5").Find("John")

If rgFound Is Nothing Then
    Debug.Print "Name was not found."
Else
    Debug.Print "Name found in :" & rgFound.Address
End If

Using After with Find

The After parameter is used if you want to start the search from a particular cell. When, the Excel Find Dialog is used, the active cell is considered the After cell. In other words, this cell is the starting point for the search. In VBA, if no After parameter is specified then the search starts at the top-left cell of the range.

Example 1 Without After

Let’s look at the following code.

Set cell = Range("A1:A6").Find("Rachal")

 
Find will return the cell A2 as this is where the first “Rachal” is found.

Excel VBA Find No After

Example 2 Using After

In the next example, we use after. We are telling VBA to start the search for “Rachal” after cell A2

Set cell = Range("A1:A6").Find("Rachal", After:=Range("A2"))

 
This will return the cell A6

Find with After

Example 3 Wrapping Around

If a match is not found then the search will “wrap around”. This means it will go back to the start of the range.

In the following example, we are looking for Drucilla. We start our search After cell A2. Find will search from A3 to A6 and then will move to A1.

So the following code will return A1 as there is no text “Drucilla” from A3 to A6:

Set cell = Range("A1:A6").Find("Drucilla", After:=Range("A2"))

 
vba find example 3a

 
The search order for this example was A4, A5, A6, A1.

 
You can try these example for yourself by downloading the workbook from the top of the post.
 

Using LookIn with Find

Using LookIn allows you to search in Values, Formulas or Comments.

Important Note: When a cell has text only, this text is considered a formula AND a value. See the table below for details

Cell Contains Result LookIn value is
Apple Apple Value and Formula
=”App” & “le”‘ Apple Value only
=LEFT(“Apple”,4)’ Appl Formula only

 
We are going to use the following sample data.

A2 Contains “Apple” as a value only
A3 Contains  “Apple” as a formula only
A4 Contains “Apple” in  the comment only

 
VBA Find LookIn

 
The code below searches for “Apple” in the different types: value, formula, threaded comment and note.

To see a working example of this code you can download the source code from the top of this post.

' Searches in value, formula, threaded comment and note.
' https://excelmacromastery.com/excel-vba-find/
Sub UseLookIn()

    ' Finds A2
    Dim rgFound As Range
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlValues)
    Debug.Print "Found 'Apple' as value in: " & rgFound.Address

    ' Finds A3
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlFormulas)
    Debug.Print "Found 'Apple' as formula in: " & rgFound.Address

    ' Finds A4
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlCommentsThreaded)
    Debug.Print "Found 'Apple' as comment threaded in: " & rgFound.Address
    
    ' Finds A5
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlNotes)
    Debug.Print "Found 'Apple' as note in: " & rgFound.Address

End Sub
 

Important note that I have used xlCommentsThreaded for the third one as threaded comments are used in Office 365. If you are using an older version that doesn’t have threaded comments then use xlComments.

Using LookAt with Find

Using the LookAt function is pretty straightforward.

  1. xlWhole means the search value must match the entire cell contents.
  2. xlPart means the search value only has to match part of the cell.

 
The following example has “Apple” as part of the cell contents in A2 and it is the full contents in cell A3.
VBA Find LookAt

 
The first Find in the following code finds “Apple” in A2. The second Find is looking for a full match so finds A3.

' https://excelmacromastery.com/
Sub UseLookAt()

    Dim cell As Range

    ' Finds A2
    Set cell = Range("A1:A3").Find("Apple", Lookat:=xlPart)
    Debug.Print cell.Address

    ' Finds A3
    Set cell = Range("A1:A3").Find("Apple", Lookat:=xlWhole)
    Debug.Print cell.Address

End Sub

 
You can try these example for yourself by downloading the workbook from the top of the post.
 

Using SearchOrder with Find

The SearchOrder parameter allows us to search by row or by column. In the following sample data we have two occurrences of the text “Elli”.

 
VBA Find SearchOrder

 
If we search by row we will find the “Elli” in B2 first. This is because we search in the order row 1, then row 2 etc.

If we search by column we will find the “Elli” in A5 first. This is because we search in the order column A, the Column B etc.

 
The following code shows an example of using the SearchOrder with this sample data

' https://excelmacromastery.com/
Sub UseSearchOrder()

    Dim cell As Range

    ' Finds B2
    Set cell = Range("A1:B6").Find("Elli", SearchOrder:=xlRows)
    Debug.Print cell.Address

    ' Finds A5
    Set cell = Range("A1:B6").Find("Elli", SearchOrder:=xlColumns)
    Debug.Print cell.Address

End Sub

 

Using SearchDirection with Find

SearchDirection allows you to search forward or backward. So imagine you have the range A1:A7. Searching using xlNext will go in the order

A1, A2, A3, A4, A5, A6, A7

Searching using xlPrevious will go in the order

A7, A6, A5, A4, A3, A2, A1

VBA Find SearchDirection

 
Using xlNext with the sample data will return A2 as this where it finds the first match. Using xlPrevious will return A6.

' NOTE: Underscore allows breaking up a line
' https://excelmacromastery.com/
Sub UseSearchDirection()

    Dim cell As Range

    ' Finds A2
    Set cell = shData.Range("A1:A7") _
        .Find("Elli", SearchDirection:=xlNext)
    Debug.Print cell.Address

    ' Finds A6
    Set cell = shData.Range("A1:A7") _
        .Find("Elli", SearchDirection:=xlPrevious)
    Debug.Print cell.Address

End Sub

Using xlPrevious with After

It you use the After parameter with xlPrevious then it will start before from the After cell. So if we set the After cell to be A6 then the search order will be

A5,A4,A3,A2,A1,A7,A6.

The following code shows an example of this

' https://excelmacromastery.com/
Sub UseSearchDirectionAfter()

    Dim cell As Range

    ' Finds A2
    Set cell = shData.Range("A1:A7").Find("Elli" _
            , After:=Range("A6"), SearchDirection:=xlPrevious)
    Debug.Print cell.Address

    ' Finds A6
    Set cell = shData.Range("A1:A7").Find("Elli" _
            , After:=Range("A7"), SearchDirection:=xlPrevious)
    Debug.Print cell.Address

End Sub

Using MatchCase with Find

The MatchCase parameter is used to determine if the case of the letters matters in the search. It can be set to True or False.

  • True – the case of the letters must match
  • False – the case of the letters does not matter

 
The following sample list has two entries for “Elli”. The second has a small letter e

VBA Find MatchCase

 
The following code examples show the result of setting MatchCase to True and False

' https://excelmacromastery.com/
Sub UseMatchCase()

    Dim cell As Range

    ' Finds A2
    Set cell = Range("A1:B6").Find("elli", MatchCase:=False)
    Debug.Print cell.Address

    ' Finds A6
    Set cell = Range("A1:B6").Find("elli", MatchCase:=True)
    Debug.Print cell.Address

End Sub

Using MatchByte with Find

The MatchByte parameter is used for languages with a double-byte character set. These are languages such as Chinese/Japanese/Korean.

If you are not using them then this parameter is not relevant. They are used as follows

  • True means to match only double-byte characters with double-byte characters.
  • False means to double-byte characters can match with single or double-byte characters.

Using the WildCard

We can use the asterisk symbol(*) as a wild card when searching for text. The asterisk represents one or more characters.
For example
“T*” will find any word that starts with T.
“To*” will find any word that starts with To.
“*y” will find any word that ends with y.
“*ey” will find any word that ends with ey.

The code below shows examples of using the wildcard based on this data:
vba find wild card

' Examples of using the wild card
' https://excelmacromastery.com/excel-vba-find/
Sub WildCard()

    Dim rgFound As Range
    
    ' Finds Tom in A2
    Set rgFound = shWildCard.Range("A1:A6").Find("T*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address

    ' Finds Tim in A5
    Set rgFound = shWildCard.Range("A1:A6").Find("Ti*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    
    ' Finds Tommy in A4
    Set rgFound = shWildCard.Range("A1:A6").Find("*my")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    
    ' Finds Ellen in A3
    Set rgFound = shWildCard.Range("A1:A6").Find("*len*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    
    ' Finds Helen in A6
    Set rgFound = shWildCard.Range("A1:A6").Find("*elen*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    
End Sub

Using SearchFormat with Find

Search Format is a bit different than the other parameters. It allows you to search for a cell format such as font type or cell color.

You need to set the format first by using the Application.FindFormat property. Then you set SearchFormat to True to search for this format.

In the following sample data, we have two cells formatted. Cell A5 is set to Bold and Cell A6 has the fill colour set to red.
VBA Find Search Format

 
The following code searches for the bold cell:

' Find the cell which has a bold format
' https://excelmacromastery.com/excel-vba-find/
Sub UseSearchFormat()

    Dim findText As String
    findText = "Elli"

    ' Clear previous formats and set new format
    Application.FindFormat.Clear
    Application.FindFormat.Font.Bold = True

    ' Finds A2
    Dim rgFound As Range
    Set rgFound = Range("A1:A6").Find(findText, SearchFormat:=False)
    Debug.Print "Found '" & findText & "' in cell: " & rgFound.Address

    ' Finds A5
    Set rgFound = Range("A1:A6").Find(findText, SearchFormat:=True)
    Debug.Print "Found '" & findText & "' in cell: " & rgFound.Address
    
    Application.FindFormat.Clear

End Sub

Using Wild Card with Format

You can search for a cell based on the format only. In other words, the value in the cell is ignored in the search. You do this by placing “*” in the search string.

The following code searches for a cell that is formatted – the cell color in this example is set to red. The contents of the cell do not matter:

' Find the cell which is formatted - contents do not matter
' https://excelmacromastery.com/excel-vba-find/
Sub UseSearchFormatWild()
    
    ' Clear previous formats and set new format
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = rgbRed

    ' Finds A2 as it ignores the format and finds the first cell with any contents
    Dim rgFound As Range
    Set rgFound = shSearchFormat.Range("A1:B6").Find("*", SearchFormat:=False)
    Debug.Print "Found format in cell: " & rgFound.Address

    ' Finds A5 as this is first cell with the format set to interior color as red
    Set rgFound = shSearchFormat.Range("A1:B6").Find("*", SearchFormat:=True)
    Debug.Print "Found format in cell: " & rgFound.Address
    
    Application.FindFormat.Clear

End Sub

Important – Clearing Format

When you set the FindFormat attributes they remain in place until you set them again. This is something to watch out for.

For example, imagine you set the format to bold and then use Find. Then you set the format to font size 12 and use Find again. The search will look for cells where the font is bold AND of size 12.

Therefore, it is a good idea to clear the format before you use it as I have done in the above examples.

Application.FindFormat.Clear

 
You can see we used this in the second SearchFormat example above.

Multiple Searches

In many cases you will want to search for multiple occurrences of the same value.  To do this we use the Find function first. Then we use the .FindNext function to find the next item.

VBA Find Multiple Searches

 
.FindNext searches based on the setting we used in the Find. The following code shows a simple example of finding the first and second occurrences of the text “Elli”.

' https://excelmacromastery.com/
Sub SearchNext()

    Dim cell As Range
    ' Find first - A2
    Set cell = Range("A1:A9").Find("Elli")
    Debug.Print "Found: " & cell.Address

    ' Find second - A5
    Set cell = Range("A1:A9").FindNext(cell)
    Debug.Print "Found: " & cell.Address

End Sub

 
Sometimes you won’t know how many occurrences there is. In this case we use a loop to keep searching until we have found all the items.

We use Find to get the first item. If we find an item we then use a Do Loop with .FindNext to find the rest of the occurrences.

FindNext will wrap around. That is, after it finds A9 it will continue the search at A1. Therefore, we store the address of the first cell we find. When FindNext returns this cell again we know we have found all the items.

The following code will find all the occurrences of Elli

' https://excelmacromastery.com/
Sub MultipleSearch()

    ' Get name to search
    Dim name As String: name = "Elli"

    ' Get search range
    Dim rgSearch As Range
    Set rgSearch = Range("A1:A9")

    Dim cell As Range
    Set cell = rgSearch.Find(name)

    ' If not found then exit
    If cell Is Nothing Then
        Debug.Print "Not found"
        Exit Sub
    End If

    ' Store first cell address
    Dim firstCellAddress As String
    firstCellAddress = cell.Address

    ' Find all cells containing Elli
    Do
        Debug.Print "Found: " & cell.Address
        Set cell = rgSearch.FindNext(cell)
    Loop While firstCellAddress <> cell.Address

End Sub

 
The output from this code is
Found: $A$2
Found: $A$5
Found: $A$8

Finding the Last Cell Containing Data

A very common task in VBA is finding the last cell that contains data in a row or colum. This does not use the VBA Find function. Instead, we use the following code to find the last row with data

' Find the last row with data in column A
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Find the last row with data in column C
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

 
To find the last column with data we use similar code

' Find the last column with data in row 1
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

' Find the last column with data in row 3
lLastCol = Cells(3, Columns.Count).End(xlToLeft).Column

Finding Cells with Patterns

If you want to find cells with certain patterns then you have to use the Like operator rather than Find.

For example, to find  the all the names starting with E you could use the following code

' Print all names starting with the letter E
' https://excelmacromastery.com/
Sub PatternMatch()

    Dim cell As Range
    ' Go through each cell in range
    For Each cell In Range("A1:A20")
        ' Check the pattern
        If cell Like "[E]*" Then
            Debug.Print cell
        End If
    Next

End Sub

To see a real-world example of using pattern matching check out Example 3: Check if a filename is valid.

An Alternative to using VBA Find

If you are expecting a large number of hits then using an array is a better option. You can read a range of cells to an array very quickly and efficiently.

The following code reads the cell values to an array and then reads through the array to count the items.

' https://excelmacromastery.com/
Sub UseArrayToCount()

    Dim arr As Variant
    ' read cell range to array
    arr = Sheet2.Range("A1:B25").Value

    Dim name As Variant, cnt As Long
    ' Go through the array
    For Each name In arr
        ' Count in the name 'Ray' is found
        If name = "Ray" Then
            cnt = cnt + 1
        End If
    Next name

    Debug.Print "The number of occurrences was: " & cnt

End Sub

Find and Replace

To  do a find and Replace you can use the Replace function. It is very similar to using the Find function.

The replace function is outside the scope of this post although a lot of what you read here can be used with it. You can see the details of it at Microsoft – VBA Replace Function

 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

20 Step by Step Examples to Search and Find with MacrosIn this Excel VBA Tutorial, you learn how to search and find different items/information with macros.

This VBA Find Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get free access to this example workbook by clicking the button below.

Get immediate free access to the Excel VBA Find workbook example

Use the following Table of Contents to navigate to the Section you’re interested in.

Related Excel VBA and Macro Training Materials

The following VBA and Macro training materials may help you better understand and implement the contents below:

  • Tutorials about general VBA constructs and structures:
    • Tutorials for Beginners:
      • Macros.
      • VBA.
    • Enable and disable macros.
    • The Visual Basic Editor (VBE).
    • Procedures:
      • Sub procedures.
      • Function procedures.
    • Work with:
      • Objects.
      • Properties.
      • Methods.
      • Variables.
      • Data types.
      • R1C1-style references.
      • Worksheet functions.
      • Loops.
      • Arrays.
    • Refer to:
      • Sheets and worksheets.
      • Cell ranges.
  • Tutorials with practical VBA applications and macro examples:
    • Find the last row or last column.
    • Set or get a cell’s or cell range’s value.
    • Check if a cell is empty.
    • Use the VLookup function.
  • The comprehensive and actionable Books at The Power Spreadsheets Library:
    • Excel Macros for Beginners Book Series.
    • VBA Fundamentals Book Series.

#1. Excel VBA Find (Cell with) Value in Cell Range

VBA Code to Find (Cell with) Value in Cell Range

To find a cell with a numeric value in a cell range, use the following structure/template in the applicable statement:

CellRangeObject.Find(What:=SearchedValue, After:=SingleCellRangeObject, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=XlSearchOrderConstant, SearchDirection:=XlSearchDirectionConstant)

The following Sections describe the main elements in this structure.

CellRangeObject

A Range object representing the cell range you search in.

Find

The Range.Find method:

  • Finds specific information (the numeric value you search for) in a cell range (CellRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedValue

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a numeric value in a cell range, set the What parameter to the numeric value you search for (SearchedValue).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range you search in (CellRangeObject).

If you omit specifying the After parameter, the search begins after the first cell (in the upper left corner) of the cell range you search in (CellRangeObject).

To find a cell with a numeric value in a cell range, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=xlValues

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a numeric value in a cell range, set the LookIn parameter to xlValues. xlValues refers to values.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a numeric value in a cell range, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=XlSearchOrderConstant

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (CellRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a numeric value in a cell range, set the SearchOrder parameter to either of the following, as applicable:

  • xlByRows (SearchOrder:=xlByRows): To search by rows.
  • xlByColumns (SearchOrder:=xlByColumns): To search by columns.
SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a numeric value in a cell range, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.

Macro Example to Find (Cell with) Value in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyRange: The cell range you search in.
    2. MyValue: The numeric value you search for.
  2. Finds MyValue in MyRange.
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the numeric value (MyValue) is found.
Function FindValueInCellRange(MyRange As Range, MyValue As Variant) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyRange and MyValue
        '(2) Finds a value passed as argument (MyValue) in a cell range passed as argument (MyRange)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the value (MyValue) is found
    
    With MyRange
        FindValueInCellRange = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) Value in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) contain randomly generated values.
  • Cell J7 contains the searched value (41).
  • Cell K7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the numeric value (MyValue) is found. This is cell B11.
  • Cell L7 displays the worksheet formula used in cell K7 (=FindValueInCellRange(A6:H30,J7)).
    • The cell range where the search is carried out contains cells A6 to H30 (A6:H30).
    • The searched value is stored in cell J7 (J7).
Example: Find value in cell range with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#2. Excel VBA Find (Cell with) Value in Table

VBA Code to Find (Cell with) Value in Table

To find a cell with a numeric value in an Excel Table, use the following structure/template in the applicable statement:

ListObjectObject.DataBodyRange.Find(What:=SearchedValue, After:=SingleCellRangeObject, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=XlSearchOrderConstant, SearchDirection:=XlSearchDirectionConstant)

The following Sections describe the main elements in this structure.

ListObjectObject

A ListObject object representing the Excel Table you search in.

DataBodyRange

The ListObject.DataBodyRange property returns a Range object representing the cell range containing an Excel Table’s values (excluding the headers).

Find

The Range.Find method:

  • Finds specific information (the numeric value you search for) in a cell range (containing the applicable Excel Table’s values).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedValue

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a numeric value in an Excel Table, set the What parameter to the numeric value you search for (SearchedValue).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range you search in (containing the applicable Excel Table’s values).

If you omit specifying the After parameter, the search begins after the first cell (in the upper left corner) of the cell range you search in (containing the applicable Excel Table’s values).

To find a cell with a numeric value in an Excel Table, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=xlValues

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a numeric value in an Excel Table, set the LookIn parameter to xlValues. xlValues refers to values.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a numeric value in an Excel Table, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=XlSearchOrderConstant

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (containing the applicable Excel Table’s values) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a numeric value in an Excel Table, set the SearchOrder parameter to either of the following, as applicable:

  • xlByRows (SearchOrder:=xlByRows): To search by rows.
  • xlByColumns (SearchOrder:=xlByColumns): To search by columns.
SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a numeric value in an Excel Table, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.

Macro Example to Find (Cell with) Value in Table

The following macro (User-Defined Function) example does the following:

  1. Accepts 3 arguments:
    1. MyWorksheetName: The name of the worksheet where the Excel Table you search in is stored.
    2. MyValue: The numeric value you search for.
    3. MyTableIndex: The index number of the Excel Table (stored in the worksheet named MyWorksheetName) you search in. MyTableIndex is an optional argument with a default value of 1.
  2. Finds MyValue in the applicable Excel Table’s values (excluding the headers).
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the applicable Excel Table where the numeric value (MyValue) is found.
Function FindValueInTable(MyWorksheetName As String, MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 3 arguments: MyWorksheetName, MyValue and MyTableIndex
        '(2) Finds a value passed as argument (MyValue) in an Excel Table stored in a worksheet whose name is passed as argument (MyWorksheetName). The index number of the Excel Table is either:
            '(1) Passed as an argument (MyTableIndex); or
            '(2) Assumed to be 1 (if MyTableIndex is omitted)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the Excel Table (stored in the MyWorksheetName worksheet and whose index is MyTableIndex) where the value (MyValue) is found
    
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).DataBodyRange
        FindValueInTable = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) Value in Table

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H31) contain an Excel Table with randomly generated values.
  • Cell J7 contains the searched value (41).
  • Cell K7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the Excel Table where the numeric value (MyValue) is found. This is cell B12.
  • Cell L7 displays the worksheet formula used in cell K7 (=FindValueInTable(“Find Value in Table”,J7)).
    • The name of the worksheet where the Excel Table is stored is “Find Value in Table” (“Find Value in Table”).
    • The searched value is stored in cell J7 (J7).
    • The index number of the Excel Table is 1 (by default).
Example: Find value in table with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#3. Excel VBA Find (Cell with) Value in Column

VBA Code to Find (Cell with) Value in Column

To find a cell with a numeric value in a column, use the following structure/template in the applicable statement:

RangeObjectColumn.Find(What:=SearchedValue, After:=SingleCellRangeObject, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=XlSearchDirectionConstant)

The following Sections describe the main elements in this structure.

RangeObjectColumn

A Range object representing the column you search in.

Find

The Range.Find method:

  • Finds specific information (the numeric value you search for) in a cell range (RangeObjectColumn).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedValue

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a numeric value in a column, set the What parameter to the numeric value you search for (SearchedValue).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the column you search in (RangeObjectColumn).

If you omit specifying the After parameter, the search begins after the first cell of the column you search in (RangeObjectColumn).

To find a cell with a numeric value in a column, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=xlValues

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a numeric value in a column, set the LookIn parameter to xlValues. xlValues refers to values.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a numeric value in a column, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=xlByRows

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable column (RangeObjectColumn) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a numeric value in a column, set the SearchOrder parameter to xlByRows. xlByRows results in the Range.Find method searching by rows.

SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a numeric value in a column, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.

Macro Example to Find (Cell with) Value in Column

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyColumn: The column you search in.
    2. MyValue: The numeric value you search for.
  2. Finds MyValue in MyColumn.
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the numeric value (MyValue) is found.
Function FindValueInColumn(MyColumn As Range, MyValue As Variant) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyColumn and MyValue
        '(2) Finds a value passed as argument (MyValue) in a column passed as argument (MyColumn)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the value (MyValue) is found
    
    With MyColumn
        FindValueInColumn = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) Value in Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A6 to A31) contains randomly generated values.
  • Cell C7 contains the searched value (90).
  • Cell D7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the numeric value (MyValue) is found. This is cell A13.
  • Cell E7 displays the worksheet formula used in cell D7 (=FindValueInColumn(A:A,C7)).
    • The column where the search is carried out is column A (A:A).
    • The searched value is stored in cell C7 (C7).

Get immediate free access to the Excel VBA Find workbook example

#4. Excel VBA Find (Cell with) Value in Table Column

VBA Code to Find (Cell with) Value in Table Column

To find a cell with a numeric value in an Excel Table column, use the following structure/template in the applicable statement:

ListColumnObject.DataBodyRange.Find(What:=SearchedValue, After:=SingleCellRangeObject, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=XlSearchOrderConstant, SearchDirection:=XlSearchDirectionConstant)

The following Sections describe the main elements in this structure.

ListColumnObject

A ListColumn object representing the Excel Table column you search in.

DataBodyRange

The ListColumn.DataBodyRange property returns a Range object representing the cell range containing an Excel Table column’s values (excluding the header).

Find

The Range.Find method:

  • Finds specific information (the numeric value you search for) in a cell range (containing the applicable Excel Table column’s values).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedValue

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a numeric value in an Excel Table column, set the What parameter to the numeric value you search for (SearchedValue).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range you search in (containing the applicable Excel Table column’s values).

If you omit specifying the After parameter, the search begins after the first cell of the cell range you search in (containing the applicable Excel Table column’s values).

To find a cell with a numeric value in an Excel Table column, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=xlValues

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a numeric value in an Excel Table column, set the LookIn parameter to xlValues. xlValues refers to values.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a numeric value in an Excel Table column, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=XlSearchOrderConstant

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (containing the applicable Excel Table column’s values) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a numeric value in an Excel Table column, set the SearchOrder parameter to either of the following, as applicable:

  • xlByRows (SearchOrder:=xlByRows): To search by rows.
  • xlByColumns (SearchOrder:=xlByColumns): To search by columns.
SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a numeric value in an Excel Table column, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.

Macro Example to Find (Cell with) Value in Table Column

The following macro (User-Defined Function) example does the following:

  1. Accepts 4 arguments:
    1. MyWorksheetName: The name of the worksheet where the Excel Table (containing the column you search in) is stored.
    2. MyColumnIndex: The index/column number of the column you search in (in the applicable Excel Table).
    3. MyValue: The numeric value you search for.
    4. MyTableIndex: The index number of the Excel Table (stored in the worksheet named MyWorksheetName) containing the column you search in. MyTableIndex is an optional argument with a default value of 1.
  2. Finds MyValue in the applicable Excel Table column’s values (excluding the header).
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the applicable Excel Table column where the numeric value (MyValue) is found.
Function FindValueInTableColumn(MyWorksheetName As String, MyColumnIndex As Long, MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 4 arguments: MyWorksheetName, MyColumnIndex, MyValue and MyTableIndex
        '(2) Finds a value passed as argument (MyValue) in an Excel Table column, where:
            '(1) The table column's index is passed as argument (MyColumnIndex); and
            '(2) The Excel Table is stored in a worksheet whose name is passed as argument (MyWorksheetName). The index number of the Excel Table is either:
                '(1) Passed as an argument (MyTableIndex); or
                '(2) Assumed to be 1 (if MyTableIndex is omitted)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the applicable Excel Table column where the value (MyValue) is found
    
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).ListColumns(MyColumnIndex).DataBodyRange
        FindValueInTableColumn = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) Value in Table Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H31) contain an Excel Table with randomly generated values. Cells in the first row (row 7) contain the searched value (90), except for the cell in the searched column (Column 3).
  • Cell J7 contains the searched value (90).
  • Cell K7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the applicable Excel Table column (Column 3) where the numeric value (MyValue) is found. This is cell C14.
  • Cell L7 displays the worksheet formula used in cell K7 (=FindValueInTableColumn(“Find Value in Table Column”,3, J7)).
    • The name of the worksheet where the Excel Table is stored is “Find Value in Table Column” (“Find Value in Table Column”).
    • The index number of the Excel Table column is 3 (3).
    • The searched value is stored in cell J7 (J7).
    • The index number of the Excel Table is 1 (by default).
Example: Find value in table column with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#5. Excel VBA Find Minimum Value in Cell Range

VBA Code to Find Minimum Value in Cell Range

To find the minimum value in a cell range, use the following structure/template in the applicable statement:

Application.Min(CellRangeObject)

The following Sections describe the main elements in this structure.

Application.Min

The WorksheetFunction.Min method returns the minimum value in a set of values.

CellRangeObject

The WorksheetFunction.Min method accepts up to thirty parameters (Arg1 to Arg30). These are the values for which you want to find the minimum value.

To find the minimum value in a cell range, pass a Range object (CellRangeObject) representing the cell range whose minimum value you want to find as method parameter.

Macro Example to Find Minimum Value in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts 1 argument (MyRange): The cell range whose minimum value you search for.
  2. Finds and returns the minimum value in the cell range (MyRange).
Function FindMinimumValueInCellRange(MyRange As Range) As Double
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyRange
        '(2) Finds the minimum value in the cell range passed as argument (MyRange)
    
    FindMinimumValueInCellRange = Application.Min(MyRange)

End Function

Effects of Executing Macro Example to Find Minimum Value in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) contain randomly generated values.
  • Cell J7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the minimum value in the cell range (MyRange). This is the number 1.
  • Cell K7 displays the worksheet formula used in cell J7 (=FindMinimumValueInCellRange(A6:H30)). The cell range where the search is carried out contains cells A6 to H30 (A6:H30).
Example: Find minimum value in cell range with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#6. Excel VBA Find (Cell with) String (or Text) in Cell Range

VBA Code to Find (Cell with) String (or Text) in Cell Range

To find a cell with a string (or text) in a cell range, use the following structure/template in the applicable statement:

CellRangeObject.Find(What:=SearchedString, After:=SingleCellRangeObject, LookIn:=XlFindLookInConstant, LookAt:=XlLookAtConstant, SearchOrder:=XlSearchOrderConstant, SearchDirection:=XlSearchDirectionConstant, MatchCase:=BooleanValue)

The following Sections describe the main elements in this structure.

CellRangeObject

A Range object representing the cell range you search in.

Find

The Range.Find method:

  • Finds specific information (the string or text you search for) in a cell range (CellRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedString

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a string (or text) in a cell range, set the What parameter to the string (or text) you search for (SearchedString).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range you search in (CellRangeObject).

If you omit specifying the After parameter, the search begins after the first cell (in the upper left corner) of the cell range you search in (CellRangeObject).

To find a cell with a string (or text) in a cell range, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=XlFindLookInConstant

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a string (or text) in a cell range, set the LookIn parameter to either of the following, as applicable:

  • xlFormulas (LookIn:=xlFormulas): To search in the applicable cell range’s formulas.
  • xlValues (LookIn:=xlValues): To search in the applicable cell range’s values.
LookAt:=XlLookAtConstant

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a string (or text) in a cell range, set the LookAt parameter to either of the following, as applicable:

  • xlWhole (LookAt:=xlWhole): To match against the entire/whole searched cell contents.
  • xlPart (LookAt:=xlPart): To match against any part of the searched cell contents.
SearchOrder:=XlSearchOrderConstant

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (CellRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a string (or text) in a cell range, set the SearchOrder parameter to either of the following, as applicable:

  • xlByRows (SearchOrder:=xlByRows): To search by rows.
  • xlByColumns (SearchOrder:=xlByColumns): To search by columns.
SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a string (or text) in a cell range, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.
MatchCase:=BooleanValue

The MatchCase parameter of the Range.Find method specifies whether the search is:

  • Case-sensitive; or
  • Case-insensitive.

The default value of the MatchCase parameter is False.

To find a cell with a string (or text) in a cell range, set the MatchCase parameter to either of the following, as applicable:

  • True (MatchCase:=True): To carry out a case-sensitive search.
  • False (MatchCase:=False): To carry out a case-insensitive search.

Macro Example to Find (Cell with) String (or Text) in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyRange: The cell range you search in.
    2. MyString: The string (or text) you search for.
  2. Finds MyString in MyRange. The search is case-insensitive.
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the string or text (MyString) is found.
Function FindStringInCellRange(MyRange As Range, MyString As Variant) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyRange and MyString
        '(2) Finds a string passed as argument (MyString) in a cell range passed as argument (MyRange). The search is case-insensitive
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the string (MyString) is found
    
    With MyRange
        FindStringInCellRange = .Find(What:=MyString, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) String (or Text) in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) contain randomly generated words.
  • Cell J7 contains the searched string or text (Excel).
  • Cell K7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the cell range (MyRange) where the string or text (MyString) is found. This is cell F20.
  • Cell L7 displays the worksheet formula used in cell K7 (=FindStringInCellRange(A6:H30,J7)).
    • The cell range where the search is carried out contains cells A6 to H30 (A6:H30).
    • The searched string or text is stored in cell J7 (J7).
Example: Find String in cell range with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#7. Excel VBA Find (Cell with) String (or Text) in Column

VBA Code to Find (Cell with) String (or Text) in Column

To find a cell with a string (or text) in a column, use the following structure/template in the applicable statement:

RangeObjectColumn.Find(What:=SearchedString, After:=SingleCellRangeObject, LookIn:=XlFindLookInConstant, LookAt:=XlLookAtConstant, SearchOrder:=xlByRows, SearchDirection:=XlSearchDirectionConstant, MatchCase:=BooleanValue)

The following Sections describe the main elements in this structure.

RangeObjectColumn

A Range object representing the column you search in.

Find

The Range.Find method:

  • Finds specific information (the string or text you search for) in a cell range (RangeObjectColumn).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedString

The What parameter of the Range.Find method specifies the data to search for.

To find a cell with a string (or text) in a column, set the What parameter to the string (or text) you search for (SearchedString).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the column you search in (RangeObjectColumn).

If you omit specifying the After parameter, the search begins after the first cell of the column you search in (RangeObjectColumn).

To find a cell with a string (or text) in a column, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=XlFindLookInConstant

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a cell with a string (or text) in a column, set the LookIn parameter to either of the following, as applicable:

  • xlFormulas (LookIn:=xlFormulas): To search in the applicable column’s formulas.
  • xlValues (LookIn:=xlValues): To search in the applicable column’s values.
LookAt:=XlLookAtConstant

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a cell with a string (or text) in a column, set the LookAt parameter to either of the following, as applicable:

  • xlWhole (LookAt:=xlWhole): To match against the entire/whole searched cell contents.
  • xlPart (LookAt:=xlPart): To match against any part of the searched cell contents.
SearchOrder:=xlByRows

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable column (RangeObjectColumn) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a cell with a string (or text) in a column, set the SearchOrder parameter to xlByRows. xlByRows results in the Range.Find method searching by rows.

SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a cell with a string (or text) in a column, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.
MatchCase:=BooleanValue

The MatchCase parameter of the Range.Find method specifies whether the search is:

  • Case-sensitive; or
  • Case-insensitive.

The default value of the MatchCase parameter is False.

To find a cell with a string (or text) in a column, set the MatchCase parameter to either of the following, as applicable:

  • True (MatchCase:=True): To carry out a case-sensitive search.
  • False (MatchCase:=False): To carry out a case-insensitive search.

Macro Example to Find (Cell with) String (or Text) in Column

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyColumn: The column you search in.
    2. MyString: The string (or text) you search for.
  2. Finds MyString in MyColumn. The search is case-insensitive.
  3. Returns a string containing the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the string or text (MyString) is found.
Function FindStringInColumn(MyColumn As Range, MyString As Variant) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyColumn and MyString
        '(2) Finds a string passed as argument (MyString) in a column passed as argument (MyColumn). The search is case-insensitive
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the string (MyString) is found
    
    With MyColumn
        FindStringInColumn = .Find(What:=MyString, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

Effects of Executing Macro Example to Find (Cell with) String (or Text) in Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A6 to A30) contains randomly generated words.
  • Cell C7 contains the searched string or text (Excel).
  • Cell D7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first cell in the column (MyColumn) where the string or text (MyString) is found. This is cell A21.
  • Cell E7 displays the worksheet formula used in cell D7 (=FindStringInColumn(A:A,C7)).
    • The column where the search is carried out is column A (A:A).
    • The searched string or text is stored in cell C7 (C7).
Example: Find string in column with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#8. Excel VBA Find String (or Text) in Cell

VBA Code to Find String (or Text) in Cell

To find a string (or text) in a cell, use the following structure/template in the applicable statement:

InStr(StartingPosition, SearchedCell.Value, SearchedString, VbCompareMethodConstant)

The following Sections describe the main elements in this structure.

InStr

The InStr function returns a number. This number specifies the position of the first occurrence of a string or text (SearchedString) in another string (the string stored in SearchedCell).

StartingPosition

The Start argument of the InStr function is:

  • An optional argument.
  • A numeric expression specifying the starting position for the string (or text) search.

If you omit specifying the Start argument, the search begins at the first character of the searched string (the string stored in SearchedCell).

To find a string (or text) in a cell, set the Start argument to the position (in the string stored in SearchedCell) where the string (or text) search starts.

SearchedCell.Value

The String1 argument of the InStr function represents the string expression the InStr function searches in.

To find a string (or text) in a cell, set the String1 argument to the value/string stored in the searched cell. For these purposes:

  • “SearchedCell” is a Range object representing the searched cell.
  • “Value” refers to the Range.Value property. The Range.Value property returns the value/string stored in the searched cell (SearchedCell).
SearchedString

The String2 argument of the InStr function represents the string expression (or text) the InStr function searches for.

To find a string (or text) in a cell, set the String2 argument to the string (or text) you search for.

VbCompareMethodConstant

The Compare argument of the InStr function:

  • Is an optional argument.
  • Specifies the type of string comparison carried out by the InStr function.
  • Can take any of the built-in constants/values from the vbCompareMethod enumeration.

If you omit specifying the Compare argument, the type of string comparison is determined by the Option Compare statement. The Option Compare statement declares the default string comparison method at a module level. The default string comparison method is binary (vbBinaryCompare).

To find a string (or text) in a cell, set the Compare argument to either of the following, as applicable:

  • vbBinaryCompare: Performs a binary comparison. vbBinaryCompare:
    • Results in a case-sensitive search.
    • May be (slightly) faster than vbTextCompare.
  • vbTextCompare: Performs a textual comparison. vbTextCompare:
    • Results in a case-insensitive search.
    • May be (slightly) slower than vbBinaryCompare.
    • Is more prone to errors/bugs than vbBinaryCompare.

Macro Example to Find String (or Text) in Cell

The following macro (User-Defined Function) example does the following:

  1. Accepts three arguments:
    1. MyCell: The cell you search in.
    2. MyString: The string (or text) you search for.
    3. MyStartingPosition: The starting position for the string (or text) search. MyStartingPosition is an optional argument with a default value of 1.
  2. Finds MyString in the value/string stored in MyCell.
  3. Returns the following:
    1. If MyString is not found in the value/string stored in MyCell, the string “String not found in cell”.
    2. If MyString is found in the value/string stored in MyCell, the position of the first occurrence of MyString in the value/string stored in MyCell.
Function FindStringInCell(MyCell As Range, MyString As Variant, Optional MyStartingPosition As Variant = 1) As Variant
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts three arguments: MyCell, MyString and MyStartingPosition (optional argument with a default value of 1)
        '(2) Finds a string passed as argument (MyString) in the value/string stored in a cell passed as argument (MyCell)
        '(3) Returns the following (as applicable):
            'If MyString is not found in the value/string stored in MyCell: The string "String not found in cell"
            'If MyString is found in the value/string stored in MyCell: The position of the first occurrence of MyString in the value/string stored in MyCell
    
    'Obtain position of first occurrence of MyString in the value/string stored in MyCell
    FindStringInCell = InStr(MyStartingPosition, MyCell.Value, MyString, vbBinaryCompare)
    
    'If MyString is not found in the value/string stored in MyCell, return the string "String not found in cell"
    If FindStringInCell = 0 Then FindStringInCell = "String not found in cell"

End Function

Effects of Executing Macro Example to Find String (or Text) in Cell

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A7 to A31) contains randomly generated words.
  • Column B (cells B7 to B31) contains a 2-character string (ar).
  • Column C (cells C7 to C31) contains worksheet formulas working with the macro (User-Defined Function) example. These worksheet formulas return either of the following (as applicable):
    • The string “String not found in cell”, if the string (or text) specified in the applicable cell of column B is not found in the applicable cell of column A.
    • The position of the first occurrence of the string (or text) specified in the applicable cell of column B in the applicable cell of column A, if the string (or text) specified in the applicable cell of column B is found in the applicable cell of column A.
  • Column D (cells D7 to D31) displays the worksheet formulas used in column C (=FindStringInCell(CellInColumnA,CellInColumnB)).
    • The cell where the search is carried out is in column A (CellInColumnA).
    • The searched string or text is stored in column B (CellInColumnB).
Example: Find string in cell with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#9. Excel VBA Find String (or Text) in String

VBA Code to Find String (or Text) in String

To find a string (or text) in a string, use the following structure/template in the applicable statement:

InStr(StartingPosition, SearchedString, SearchedText, VbCompareMethodConstant)

The following Sections describe the main elements in this structure.

InStr

The InStr function returns a number. This number specifies the position of the first occurrence of a string or text (SearchedText) in another string (SearchedString).

StartingPosition

The Start argument of the InStr function is:

  • An optional argument.
  • A numeric expression specifying the starting position for the string (or text) search.

If you omit specifying the Start argument, the search begins at the first character of the searched string (SearchedString).

To find a string (or text) in a string, set the Start argument to the position (in SearchedString) where the string (or text) search starts.

SearchedString

The String1 argument of the InStr function represents the string expression the InStr function searches in.

To find a string (or text) in a string, set the String1 argument to the searched string.

SearchedText

The String2 argument of the InStr function represents the string expression (or text) the InStr function searches for.

To find a string (or text) in a string, set the String2 argument to the string (or text) you search for.

VbCompareMethodConstant

The Compare argument of the InStr function:

  • Is an optional argument.
  • Specifies the type of string comparison carried out by the InStr function.
  • Can take any of the built-in constants/values from the vbCompareMethod enumeration.

If you omit specifying the Compare argument, the type of string comparison is determined by the Option Compare statement. The Option Compare statement declares the default string comparison method at a module level. The default string comparison method is binary (vbBinaryCompare).

To find a string (or text) in a string, set the Compare argument to either of the following, as applicable:

  • vbBinaryCompare: Performs a binary comparison. vbBinaryCompare:
    • Results in a case-sensitive search.
    • May be (slightly) faster than vbTextCompare.
  • vbTextCompare: Performs a textual comparison. vbTextCompare:
    • Results in a case-insensitive search.
    • May be (slightly) slower than vbBinaryCompare.
    • Is more prone to errors/bugs than vbBinaryCompare.

Macro Example to Find String (or Text) in String

The following macro (User-Defined Function) example does the following:

  1. Accepts three arguments:
    1. MyString: The string you search in.
    2. MyText: The string (or text) you search for.
    3. MyStartingPosition: The starting position for the string (or text) search. MyStartingPosition is an optional argument with a default value of 1.
  2. Finds MyText in MyString.
  3. Returns the following:
    1. If MyText is not found in MyString, the string “Text not found in string”.
    2. If MyText is found in MyString, the position of the first occurrence of MyText in MyString.
Function FindTextInString(MyString As Variant, MyText As Variant, Optional MyStartingPosition As Variant = 1) As Variant
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts three arguments: MyString, MyText and MyStartingPosition (optional argument with a default value of 1)
        '(2) Finds text (a string) passed as argument (MyText) in a string passed as argument (MyString)
        '(3) Returns the following (as applicable):
            'If MyText is not found in MyString: The string "Text not found in string"
            'If MyText is found in MyString: The position of the first occurrence of MyText in MyString
    
    'Obtain position of first occurrence of MyText in MyString
    FindTextInString = InStr(MyStartingPosition, MyString, MyText, vbBinaryCompare)
    
    'If MyText is not found in MyString, return the string "Text not found in string"
    If FindTextInString = 0 Then FindTextInString = "Text not found in string"

End Function

Effects of Executing Macro Example to Find String (or Text) in String

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A7 to A31) contains randomly generated words.
  • Column B (cells B7 to B31) contains text (at).
  • Column C (cells C7 to C31) contains worksheet formulas working with the macro (User-Defined Function) example. These worksheet formulas return either of the following (as applicable):
    • The string “Text not found in string”, if the string (or text) specified in the applicable cell of column B is not found in the string specified in the applicable cell of column A.
    • The position of the first occurrence of the string (or text) specified in the applicable cell of column B in the string specified in the applicable cell of column A, if the string (or text) specified in the applicable cell of column B is found in the string specified in the applicable cell of column A.
  • Column D (cells D7 to D31) displays the worksheet formulas used in column C (=FindTextInString(CellInColumnA,CellInColumnB)).
    • The string where the search is carried out is stored in column A (CellInColumnA).
    • The searched string (or text) is stored in column B (CellInColumnB).
Example: Find text in string with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#10. Excel VBA Find Character in String

VBA Code to Find Character in String

To find a character in a string, use the following structure/template in the applicable statement:

InStr(StartingPosition, SearchedString, SearchedCharacter, VbCompareMethodConstant)

The following Sections describe the main elements in this structure.

InStr

The InStr function returns a number. This number specifies the position of the first occurrence of a string or text (SearchedCharacter) in another string (SearchedString).

StartingPosition

The Start argument of the InStr function is:

  • An optional argument.
  • A numeric expression specifying the starting position for the character search.

If you omit specifying the Start argument, the search begins at the first character of the searched string (SearchedString).

To find a character in a string, set the Start argument to the position (in SearchedString) where the character search starts.

SearchedString

The String1 argument of the InStr function represents the string expression the InStr function searches in.

To find a character in a string, set the String1 argument to the searched string.

SearchedCharacter

The String2 argument of the InStr function represents the string expression (or text) the InStr function searches for.

To find a character in a string, set the String2 argument to the character you search for.

VbCompareMethodConstant

The Compare argument of the InStr function:

  • Is an optional argument.
  • Specifies the type of string comparison carried out by the InStr function.
  • Can take any of the built-in constants/values from the vbCompareMethod enumeration.

If you omit specifying the Compare argument, the type of string comparison is determined by the Option Compare statement. The Option Compare statement declares the default string comparison method at a module level. The default string comparison method is binary (vbBinaryCompare).

To find a character in a string, set the Compare argument to either of the following, as applicable:

  • vbBinaryCompare: Performs a binary comparison. vbBinaryCompare:
    • Results in a case-sensitive search.
    • May be (slightly) faster than vbTextCompare.
  • vbTextCompare: Performs a textual comparison. vbTextCompare:
    • Results in a case-insensitive search.
    • May be (slightly) slower than vbBinaryCompare.
    • Is more prone to errors/bugs than vbBinaryCompare.

Macro Example to Find Character in String

The following macro (User-Defined Function) example does the following:

  1. Accepts three arguments:
    1. MyString: The string you search in.
    2. MyCharacter: The character you search for.
    3. MyStartingPosition: The starting position for the character search. MyStartingPosition is an optional argument with a default value of 1.
  2. Finds MyCharacter in MyString.
  3. Returns the following:
    1. If MyCharacter is not found in MyString, the string “Character not found in string”.
    2. If MyCharacter is found in MyString, the position of the first occurrence of MyCharacter in MyString.
Function FindCharacterInString(MyString As Variant, MyCharacter As Variant, Optional MyStartingPosition As Variant = 1) As Variant
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts three arguments: MyString, MyCharacter and MyStartingPosition (optional argument with a default value of 1)
        '(2) Finds a character passed as argument (MyCharacter) in a string passed as argument (MyString)
        '(3) Returns the following (as applicable):
            'If MyCharacter is not found in MyString: The string "Character not found in string"
            'If MyCharacter is found in MyString: The position of the first occurrence of MyCharacter in MyString
    
    'Obtain position of first occurrence of MyCharacter in MyString
    FindCharacterInString = InStr(MyStartingPosition, MyString, MyCharacter, vbBinaryCompare)
    
    'If MyCharacter is not found in MyString, return the string "Character not found in string"
    If FindCharacterInString = 0 Then FindCharacterInString = "Character not found in string"

End Function

Effects of Executing Macro Example to Find Character in String

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A7 to A31) contains randomly generated words.
  • Column B (cells B7 to B31) contains a character (a).
  • Column C (cells C7 to C31) contains worksheet formulas working with the macro (User-Defined Function) example. These worksheet formulas return either of the following (as applicable):
    • The string “Character not found in string”, if the character specified in the applicable cell of column B is not found in the string specified in the applicable cell of column A.
    • The position of the first occurrence of the character specified in the applicable cell of column B in the string specified in the applicable cell of column A, if the character specified in the applicable cell of column B is found in the string specified in the applicable cell of column A.
  • Column D (cells D7 to D31) displays the worksheet formulas used in column C (=FindCharacterInString(CellInColumnA,CellInColumnB)).
    • The string where the search is carried out is stored in column A (CellInColumnA).
    • The searched character is stored in column B (CellInColumnB).
Example: Find character in string with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#11. Excel VBA Find Column with Specific Header

VBA Code to Find Column with Specific Header

To find a column with a specific header, use the following structure/template in the applicable statement:

HeaderRowRangeObject.Find(What:=SearchedHeader, After:=SingleCellRangeObject, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=XlSearchDirectionConstant, MatchCase:=BooleanValue)

The following Sections describe the main elements in this structure.

HeaderRowRangeObject

A Range object representing the cell range containing the headers you search in.

Find

The Range.Find method:

  • Finds specific information (the header you search for) in a cell range (HeaderRowRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedHeader

The What parameter of the Range.Find method specifies the data to search for.

To find a column with a specific header, set the What parameter to the header you search for (SearchedHeader).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range containing the headers you search in (HeaderRowRangeObject).

If you omit specifying the After parameter, the search begins after the first cell of the cell range you search in (HeaderRowRangeObject).

To find a column with a specific header, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=xlValues

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find a column with a specific header, set the LookIn parameter to xlValues. xlValues refers to values.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find a column with a specific header, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=xlByColumns

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (HeaderRowRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find a column with a specific header, set the SearchOrder parameter to xlByColumns. xlByColumns searches by columns.

SearchDirection:=XlSearchDirectionConstant

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find a column with a specific header, set the SearchDirection parameter to either of the following, as applicable:

  • xlNext (SearchDirection:=xlNext): To search for the next match.
  • xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.
MatchCase:=BooleanValue

The MatchCase parameter of the Range.Find method specifies whether the search is:

  • Case-sensitive; or
  • Case-insensitive.

The default value of the MatchCase parameter is False.

To find a column with a specific header, set the MatchCase parameter to either of the following, as applicable:

  • True (MatchCase:=True): To carry out a case-sensitive search.
  • False (MatchCase:=False): To carry out a case-insensitive search.

Macro Example to Find Column with Specific Header

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyRange: The cell range whose first row contains the headers you search in.
    2. MyHeader: The header you search for.
  2. Finds MyHeader in the first row of MyRange.
  3. Returns the number of the column containing the first cell in the header row where the header (MyHeader) is found.
Function FindColumnWithSpecificHeader(MyRange As Range, MyHeader As Variant) As Long
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyRange and MyHeader
        '(2) Finds a header passed as argument (MyHeader) in the first row (the header row) of a cell range passed as argument (MyRange). The search is case-insensitive
        '(3) Returns the number of the column containing the first cell in the header row where the header (MyHeader) is found
    
    With MyRange.Rows(1)
        FindColumnWithSpecificHeader = .Find(What:=MyHeader, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    End With

End Function

Effects of Executing Macro Example to Find Column with Specific Header

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H31) contain data with the following characteristics:
    • Headers in its first row (row 6).
    • Randomly generated values.
  • Cell J7 contains the searched header (Column 3).
  • Cell K7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the column number of the first cell in the header row (cells A6 to H6) of the cell range (MyRange) where the header (MyHeader) is found. This is column 3 (C).
  • Cell L7 displays the worksheet formula used in cell K7 (=FindColumnWithSpecificHeader(A6:H31,J7)).
    • The cell range whose first row contains the headers where the search is carried out contains cells A6 to H31 (A6:H31).
    • The searched header is stored in cell J7 (J7).
Example: Find column with specific header with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#12. Excel VBA Find Next or Find All

VBA Code to Find Next or Find All

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, use the following structure/template in the applicable procedure:

Dim FoundCell As Range
Dim FirstFoundCellAddress As String
Set FoundCell = SearchedRangeObject.Find(What:=SearchedData, After:=SingleCellRangeObject, LookIn:=XlFindLookInConstant, LookAt:=XlLookAtConstant, SearchOrder:=XlSearchDirectionConstant, SearchDirection:=xlNext, MatchCase:=BooleanValue)
If Not FoundCell Is Nothing Then
    FirstFoundCellAddress = FoundCell.Address
    Do
        Statements
        Set FoundCell = SearchedRangeObject.FindNext(After:=FoundCell)
    Loop Until FoundCell.Address = FirstFoundCellAddress
End If

The following Sections describe the main elements in this structure.

Lines #1 and #2: Dim FoundCell As Range | Dim FirstFoundCellAddress As String

Dim

The Dim statement:

  1. Declares variables.
  2. Allocates storage space.
FoundCell | FirstFoundCellAddress

The names of the variables declared with the Dim statement.

  • FoundCell holds/represents the cell where the searched data is found.
  • FirstFoundCellAddress holds/represents the address of the first cell where the searched data is found.
As Range | As String

The data type of the variables declared with the Dim statement.

  • FoundCell is of the Range object data type. The Range object represents a cell or cell range.
  • FirstFoundCellAddress is of the String data type. The String data type (generally) holds textual data.

Line #3: Set FoundCell = SearchedRangeObject.Find(What:=SearchedData, After:=SingleCellRangeObject, LookIn:=XlFindLookInConstant, LookAt:=XlLookAtConstant, SearchOrder:=XlSearchDirectionConstant, SearchDirection:=xlNext, MatchCase:=BooleanValue)

Set

The Set statement assigns an object reference to an object variable.

FoundCell

Object variable holding/representing the cell where the searched data is found.

=

The assignment operator assigns an object reference (returned by the Range.Find method) to an object variable (FoundCell).

SearchedRangeObject

A Range object representing the cell range you search in.

Find

The Range.Find method:

  • Finds specific information (the data you search for) in a cell range (SearchedRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=SearchedData

The What parameter of the Range.Find method specifies the data to search for.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the What parameter to the data you search for (SearchedData).

After:=SingleCellRangeObject

The After parameter of the Range.Find method specifies the cell after which the search begins. This must be a single cell in the cell range you search in (SearchedRangeObject).

If you omit specifying the After parameter, the search begins after the first cell (in the upper left corner) of the cell range you search in (SearchedRangeObject).

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the After parameter to a Range object representing the cell after which the search begins.

LookIn:=XlFindLookInConstant

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the LookIn parameter to any of the following, as applicable:

  • xlCommentsThreaded (LookIn:=xlCommentsThreaded): To search in the applicable cell range’s threaded comments.
  • xlValues (LookIn:=xlValues): To search in the applicable cell range’s values.
  • xlComments (LookIn:=xlComments): To search in the applicable cell range’s comments/notes.
  • xlFormulas (LookIn:=xlFormulas): To search in the applicable cell range’s formulas.
LookAt:=XlLookAtConstant

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the LookAt parameter to either of the following, as applicable:

  • xlWhole (LookAt:=xlWhole): To match against the entire/whole searched cell contents.
  • xlPart (LookAt:=xlPart): To match against any part of the searched cell contents.
SearchOrder:=XlSearchDirectionConstant

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (SearchedRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the SearchOrder parameter to either of the following, as applicable:

  • xlByRows (SearchOrder:=xlByRows): To search by rows.
  • xlByColumns (SearchOrder:=xlByColumns): To search by columns.
SearchDirection:=xlNext

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the SearchDirection parameter to xlNext. xlNext results in the Range.Find method searching for the next match.

MatchCase:=BooleanValue

The MatchCase parameter of the Range.Find method specifies whether the search is:

  • Case-sensitive; or
  • Case-insensitive.

The default value of the MatchCase parameter is False.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the MatchCase parameter to either of the following, as applicable:

  • True (MatchCase:=True): To carry out a case-sensitive search.
  • False (MatchCase:=False): To carry out a case-insensitive search.

Lines #4 and 10: If Not FoundCell Is Nothing Then | End If

If … Then | End If

The If… Then… Else statement:

  • Conditionally executes a set of statements (lines #5 to #9);
  • Depending on an expression’s value (Not FoundCell Is Nothing).
Not FoundCell Is Nothing

The condition of an If… Then… Else statement is an expression evaluating to True or False. If the expression returns True, the applicable set of statements (lines #5 to #9) is executed.

In this expression:

  • Not FoundCell:
    • The Not operator performs a logical negation on an expression.
    • FoundCell is the object variable holding/representing the cell where the searched data is found.
    • The Range.Find method (in line #3) returns Nothing if no match is found. Therefore:
      • If the Range.Find method finds no match:
        • FoundCell is Nothing.
        • Not FoundCell is not Nothing.
      • If the Range.Find method finds a match:
        • FoundCell is not Nothing.
        • Not FoundCell is Nothing.
  • Is: The Is operator is an object reference comparison operator.
  • Nothing: Nothing allows you to disassociate a variable from the data it previously represented. The Range.Find method (in line #3) returns Nothing if no match is found.

Line #5: FirstFoundCellAddress = FoundCell.Address

FirstFoundCellAddress

Variable holding/representing the address of the first cell where the searched data is found.

=

The assignment operator assigns the result returned by an expression (FoundCell.Address) to a variable (FirstFoundCellAddress).

FoundCell

Object variable holding/representing the cell where the searched data is found.

At this point, FoundCell holds/represents the first cell where the searched data is found (by line #3).

Address

The Range.Address property returns a String representing the applicable cell range’s (FoundCell’s) reference.

Lines #6 and #9: Do | Loop Until FoundCell.Address = FirstFoundCellAddress

Do | Loop Until…

The Do… Loop Until statement repeats a set of statements until a condition becomes True.

FoundCell.Address = FirstFoundCellAddress

The condition of a Do… Loop Until statement is an expression evaluating to True or False. The applicable set of statements (lines #7 and #8) are:

  1. (Always) executed once, even if the condition is never met; and
  2. Repeatedly executed until the condition returns True.

In this expression:

  • FoundCell.Address:
    • FoundCell is an object variable holding/representing the cell where the searched data is found.
    • The Range.Address property returns a String representing the applicable cell range’s (FoundCell’s) reference.
  • =: The equal to comparison operator returns True or False as follows:
    • True if both expressions (FoundCell.Address and FirstFoundCellAddress) are equal.
    • False if the expressions (FoundCell.Address and FirstFoundCellAddress) are not equal.
  • FirstFoundCellAddress: Variable holding/representing the address of the first cell where the searched data is found.

This condition is tested (only) after the procedure finds (and works with) the first cell where the searched data is found. Therefore, the condition (only) returns True after the Range.FindNext method (line #8) wraps around to the first cell where the searched data is found (after finding all other cells where the searched data is found).

Line #7: Statements

Set of statements to be repeatedly executed for each cell where the searched data is found.

Line #8: Set FoundCell = SearchedRangeObject.FindNext(After:=FoundCell)

Set

The Set statement assigns an object reference to an object variable.

FoundCell

Object variable holding/representing the cell where the searched data is found.

=

The assignment operator assigns an object reference (returned by the Range.FindNext method) to an object variable (FoundCell).

SearchedRangeObject

A Range object representing the cell range you search in.

FindNext

The Range.FindNext method:

  • Continues the search that was begun by the Range.Find method (line #3).
  • Finds the next cell matching the conditions specified by the Range.Find method (line #3).
  • Returns a Range object representing the next cell where the information is found.
After:=FoundCell

The After parameter of the Range.FindNext method specifies the cell after which the search restarts.

To (i) find the next appearance of specific information or (ii) find all appearances of specific information, set the After parameter to the object variable holding/representing the cell where the searched data is found.

Macro Example to Find Next or Find All

The following macro example does the following:

  1. Find:
    1. All cells whose value is 10;
    2. In the cell range containing cells A6 to H30 in the “Find Next All” worksheet in the workbook where the procedure is stored.
  2. Set the interior/fill color of all found cells to light green.
Sub FindNextAll()
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This procedure:
        '(1) Finds all cells whose value is 10 in cells A6 to H30 of the "Find Next All" worksheet in this workbook
        '(2) Sets the found cells' interior/fill color to light green
    
    'Declare variable to hold/represent searched value
    Dim MyValue As Long
    
    'Declare variable to hold/represent address of first cell where searched value is found
    Dim FirstFoundCellAddress As String
    
    'Declare object variable to hold/represent cell range where search takes place
    Dim MyRange As Range
    
    'Declare object variable to hold/represent cell where searched value is found
    Dim FoundCell As Range
    
    'Specify searched value
    MyValue = 10
    
    'Identify cell range where search takes place
    Set MyRange = ThisWorkbook.Worksheets("Find Next All").Range("A6:H30")
    
    'Find first cell where searched value is found
    With MyRange
        Set FoundCell = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    End With
    
    'Test whether searched value is found in cell range where search takes place
    If Not FoundCell Is Nothing Then
        
        'Store address of first cell where searched value is found
        FirstFoundCellAddress = FoundCell.Address
        
        Do
            
            'Set interior/fill color of applicable cell where searched value is found to light green
            FoundCell.Interior.Color = RGB(63, 189, 133)
            
            'Find next cell where searched value is found
            Set FoundCell = MyRange.FindNext(After:=FoundCell)
        
        'Loop until address of current cell where searched value is found is equal to address of first cell where searched value was found
        Loop Until FoundCell.Address = FirstFoundCellAddress
    
    End If
    
End Sub

Effects of Executing Macro Example to Find Next or Find All

The following image illustrates the effects of executing the macro example. In this example:

  • Cells A6 to H30 contain randomly generated values.
  • A text box (Find all cells where value = 10) executes the macro example when clicked.

After the macro is executed, Excel sets the interior/fill color of all cells whose value is 10 to light green.

Example: Find next appearance or find all appearances with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#13. Excel VBA Find Last Row with Data in Cell Range

VBA Code to Find Last Row with Data in Cell Range

To find the last row with data in a cell range, use the following structure/template in the applicable procedure:

If Application.CountA(SearchedCellRangeObject) = 0 Then
    LastRowVariable = ValueIfCellRangeEmpty
Else
    LastRowVariable = SearchedCellRangeObject.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

The following Sections describe the main elements in this structure.

Lines #1, #3 and #5: If Application.CountA(SearchedCellRangeObject) = 0 Then | Else | End If

If… Then … Else… End If

The If… Then… Else statement:

  1. Conditionally executes a set of statements (line #2 or line #4);
  2. Depending on an expression’s value (Application.CountA(SearchedCellRangeObject) = 0).
Application.CountA(SearchedCellRangeObject) = 0

The condition of an If… Then… Else statement is an expression evaluating to True or False.

  • If the expression returns True, a set of statements (line #2) is executed.
  • If the expression returns False, a different set of statements (line #4) is executed.

In this expression:

  • Application.CountA(…): The WorksheetFunction.CountA method counts the number of cells in a cell range (SearchedCellRangeObject) that are not empty.
  • SearchedCellRangeObject: A Range object representing the cell range whose last row you search.
  • =: The equal to comparison operator returns True or False as follows:
    • True if both expressions (Application.CountA(SearchedCellRangeObject) and 0) are equal.
    • False if the expressions (Application.CountA(SearchedCellRangeObject) and 0) are not equal.
  • 0: The number 0. The WorksheetFunction.CountA method returns 0 if all cells in SearchedCellRangeObject are empty.

Line #2: LastRowVariable = ValueIfCellRangeEmpty

Assignment statement assigning:

  • ValueIfCellRangeEmpty; to
  • LastRowVariable.

In this expression:

  • LastRowVariable: Variable of (usually) the Long data type holding/representing the number of the last row with data in the cell range whose last row you search (SearchedCellRangeObject).
  • =: Assignment operator. Assigns a value (ValueIfCellRangeEmpty) to a variable (LastRowVariable).
  • ValueIfCellRangeEmpty: Value assigned to LastRowVariable when SearchedCellRangeObject is empty and the WorksheetFunction.CountA method returns 0.

Line #4: LastRowVariable = SearchedCellRangeObject.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastRowVariable

Variable of (usually) the Long data type holding/representing the number of the last row with data in the cell range whose last row you search (SearchedCellRangeObject).

=

The assignment operator assigns a value (SearchedCellRangeObject.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row) to a variable (LastRowVariable).

SearchedCellRangeObject

A Range object representing the cell range whose last row you search.

Find

The Range.Find method:

  • Finds specific information in a cell range (SearchedCellRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=”*”

The What parameter of the Range.Find method specifies the data to search for.

To find the last row with data in a cell range, set the What parameter to any character sequence. The asterisk (*) acts as a wildcard and results in Range.Find searching for any character sequence.

LookIn:=xlFormulas

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find the last row with data in a cell range, set the LookIn parameter to xlFormulas. xlFormulas refers to formulas.

LookAt:=xlPart

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find the last row with data in a cell range, set the LookAt parameter to xlPart. xlPart matches the data you are searching for (any character sequence as specified by the What parameter) against any part of the searched cell contents.

SearchOrder:=xlByRows

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (SearchedCellRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find the last row with data in a cell range, set the SearchOrder parameter to xlByRows. xlByRows results in the Range.Find method searching by rows.

SearchDirection:=xlPrevious

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find the last row with data in a cell range, set the SearchDirection parameter to xlPrevious. xlPrevious results in the Range.Find method searching for the previous match.

Row

The Range.Row property returns the number of the first row of the first area in a cell range.

When searching for the last row with data in a cell range, the Range.Row property returns the row number of the cell represented by the Range object returned by the Range.Find method (Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)).

Macro Example to Find Last Row with Data in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MyRange). This is the cell range whose last row you search.
  2. Tests whether MyRange is empty and proceeds accordingly:
    1. If MyRange is empty, returns the number 0 as the number of the last row with data in MyRange.
    2. If MyRange isn’t empty:
      1. Finds the last row with data in MyRange; and
      2. Returns the number of the last row with data in MyRange.
Function FindLastRow(MyRange As Range) As Long
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyRange
        '(2) Tests whether MyRange is empty
        '(3) If MyRange is empty, returns 0 as the number of the last row with data in MyRange
        '(4) If MyRange is not empty:
            '(1) Finds the last row with data in MyRange by searching for the last cell with any character sequence
            '(2) Returns the number of the last row with data in MyRange
        
    'Test if MyRange is empty
    If Application.CountA(MyRange) = 0 Then

        'If MyRange is empty, assign 0 to FindLastRow
        FindLastRow = 0

    Else
    
        'If MyRange isn't empty, find the last cell with any character sequence by:
            '(1) Searching for the previous match;
            '(2) Across rows
        FindLastRow = MyRange.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    End If
    
End Function

Effects of Executing Macro Example to Find Last Row with Data in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) contain data.
  • Cell J7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the number of the last row with data in the cell range (MyRange). This is row 30.
  • Cell K7 displays the worksheet formula used in cell J7 (=FindLastRow(A:H)). The cell range whose last row is searched contains columns A through H (A:H).
Example: Find last row with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#14. Excel VBA Find Last Column with Data in Cell Range

VBA Code to Find Last Column with Data in Cell Range

To find the last column with data in a cell range, use the following structure/template in the applicable procedure:

If Application.CountA(SearchedCellRangeObject) = 0 Then
    LastColumnVariable = ValueIfCellRangeEmpty
Else
    LastColumnVariable = SearchedCellRangeObject.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If

The following Sections describe the main elements in this structure.

Lines #1, #3 and #5: If Application.CountA(SearchedCellRangeObject) = 0 Then | Else | End If

If… Then … Else… End If

The If… Then… Else statement:

  1. Conditionally executes a set of statements (line #2 or line #4);
  2. Depending on an expression’s value (Application.CountA(SearchedCellRangeObject) = 0).
Application.CountA(SearchedCellRangeObject) = 0

The condition of an If… Then… Else statement is an expression evaluating to True or False.

  • If the expression returns True, a set of statements (line #2) is executed.
  • If the expression returns False, a different set of statements (line #4) is executed.

In this expression:

  • Application.CountA(…): The WorksheetFunction.CountA method counts the number of cells in a cell range (SearchedCellRangeObject) that are not empty.
  • SearchedCellRangeObject: A Range object representing the cell range whose last column you search.
  • =: The equal to comparison operator returns True or False as follows:
    • True if both expressions (Application.CountA(SearchedCellRangeObject) and 0) are equal.
    • False if the expressions (Application.CountA(SearchedCellRangeObject) and 0) are not equal.
  • 0: The number 0. The WorksheetFunction.CountA method returns 0 if all cells in SearchedCellRangeObject are empty.

Line #2: LastColumnVariable = ValueIfCellRangeEmpty

Assignment statement assigning:

  • ValueIfCellRangeEmpty; to
  • LastColumnVariable.

In this expression:

  • LastColumnVariable: Variable of (usually) the Long data type holding/representing the number of the last column with data in the cell range whose last column you search (SearchedCellRangeObject).
  • =: Assignment operator. Assigns a value (ValueIfCellRangeEmpty) to a variable (LastColumnVariable).
  • ValueIfCellRangeEmpty: Value assigned to LastColumnVariable when SearchedCellRangeObject is empty and the WorksheetFunction.CountA method returns 0.

Line #4: LastColumnVariable = SearchedCellRangeObject.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

LastColumnVariable

Variable of (usually) the Long data type holding/representing the number of the last column with data in the cell range whose last column you search (SearchedCellRangeObject).

=

The assignment operator assigns a value (SearchedCellRangeObject.Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column) to a variable (LastColumnVariable).

SearchedCellRangeObject

A Range object representing the cell range whose last column you search.

Find

The Range.Find method:

  • Finds specific information in a cell range (SearchedCellRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=”*”

The What parameter of the Range.Find method specifies the data to search for.

To find the last column with data in a cell range, set the What parameter to any character sequence. The asterisk (*) acts as a wildcard and results in Range.Find searching for any character sequence.

LookIn:=xlFormulas

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find the last column with data in a cell range, set the LookIn parameter to xlFormulas. xlFormulas refers to formulas.

LookAt:=xlPart

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • Can take any of the built-in constants/values from the XlLookAt enumeration.

To find the last column with data in a cell range, set the LookAt parameter to xlPart. xlPart matches the data you are searching for (any character sequence as specified by the What parameter) against any part of the searched cell contents.

SearchOrder:=xlByColumns

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (SearchedCellRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find the last column with data in a cell range, set the SearchOrder parameter to xlByColumns. xlByColumns results in the Range.Find method searching by columns.

SearchDirection:=xlPrevious

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find the last column with data in a cell range, set the SearchDirection parameter to xlPrevious. xlPrevious results in the Range.Find method searching for the previous match.

Column

The Range.Column property returns the number of the first column of the first area in a cell range.

When searching for the last column with data in a cell range, the Range.Column property returns the column number of the cell represented by the Range object returned by the Range.Find method (Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)).

Macro Example to Find Last Column with Data in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MyWorksheetName). This is the name of the worksheet whose last column you search.
  2. Tests whether the worksheet named MyWorksheetName is empty and proceeds accordingly:
    1. If the worksheet named MyWorksheetName is empty, returns the number 0 as the number of the last column with data in the worksheet.
    2. If the worksheet named MyWorksheetName isn’t empty:
      1. Finds the last column with data in the worksheet; and
      2. Returns the number of the last column with data in the worksheet.
Function FindLastColumn(MyWorksheetName As String) As Long
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyWorksheetName
        '(2) Tests whether the worksheet named MyWorksheetName is empty
        '(3) If the worksheet named MyWorksheetName is empty, returns 0 as the number of the last column with data in the worksheet
        '(4) If the worksheet named MyWorksheetName is not empty:
            '(1) Finds the last column with data in the worksheet by searching for the last cell with any character sequence
            '(2) Returns the number of the last column with data in the worksheet
    
    'Declare object variable to hold/represent all cells in the worksheet named MyWorksheetName
    Dim MyRange As Range
    
    'Identify all cells in the worksheet named MyWorksheetName
    Set MyRange = ThisWorkbook.Worksheets(MyWorksheetName).Cells
    
    'Test if MyRange is empty
    If Application.CountA(MyRange) = 0 Then

        'If MyRange is empty, assign 0 to FindLastColumn
        FindLastColumn = 0

    Else
    
        'If MyRange isn't empty, find the last cell with any character sequence by:
            '(1) Searching for the previous match;
            '(2) Across columns
        FindLastColumn = MyRange.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    End If
    
End Function

Effects of Executing Macro Example to Find Last Column with Data in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) in the worksheet named “Find Last Column Data” contain data.
    • Columns A through H of the “Find Last Column Data” worksheet contain exactly the same data as that in the “Find Last Column Formula” worksheet (displayed in the image below).
    • The “Find Last Column Data” worksheet contains no data in columns J or K whereas the “Find Last Column Formula” worksheet (displayed in the image below) does.
  • Cell J7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the number of the last column with data in the worksheet (named “Find Last Column Data”). This is column H or 8.
  • Cell K7 displays the worksheet formula used in cell J7 (=FindLastColumn(“Find Last Column Data”)). The name of the worksheet whose last column is searched is “Find Last Column Data”.

Get immediate free access to the Excel VBA Find workbook example

#15. Excel VBA Find Last Non Empty Cell in Column

VBA Code to Find Last Non Empty Cell in Column

To find the last non empty cell in a column, use the following structure/template in the applicable statement:

WorksheetObject.Range(ColumnLetter & WorksheetObject.Rows.Count).End(xlUp)

The following Sections describe the main elements in this structure.

WorksheetObject

A Worksheet object representing the worksheet containing the column whose last non empty cell you want to find.

Range

The Worksheet.Range property returns a Range object representing a cell or cell range.

ColumnLetter

The letter of the column whose last non empty cell you want to find.

&

The concatenation operator joins two strings and creates a new string.

WorksheetObject

A Worksheet object representing the worksheet containing the column whose last non empty cell you want to find.

Rows

The Worksheet.Rows property returns a Range object representing all rows in the applicable worksheet (containing the column whose last non empty cell you want to find).

Count

The Range.Count property returns the number of objects in a collection (the number of rows in the worksheet containing the column whose last non empty cell you want to find).

End(xlUp)

The Range.End property returns a Range object representing the cell at the end of the region containing the source range. In other words: The Range.End property is the rough equivalent of using the “Ctrl + Arrow Key” or “End, Arrow Key” keyboard shortcuts.

The Range.End property accepts one parameter: Direction. Direction:

  • Specifies the direction in which to move.
  • Can take the any of the built-in constants/values from the XlDirection enumeration.

To find the last non empty cell in a column, set the Direction parameter to xlUp. xlUp:

  • Results in moving up, to the top of the data region.
  • Is the rough equivalent of the “Ctrl + Up Arrow” or “End, Up Arrow” keyboard shortcuts.

Macro Example to Find Last Non Empty Cell in Column

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MyColumn): The letter of the column (in the worksheet where the UDF is used) whose last non empty cell you want to find.
  2. Finds the last non empty cell in the applicable column.
  3. Returns a string containing the address (as an A1-style relative reference) of the last non empty cell in the applicable column.
Function FindLastNonEmptyCellColumn(MyColumn As String) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyColumn
        '(2) Finds the last non empty cell in the column whose letter is passed as argument (MyColumn) in the worksheet where the UDF is used
        '(3) Returns the address (as an A1-style relative reference) of the last non empty cell found in the column whose letter is passed as argument (MyColumn) in the worksheet where the UDF is used
    With Application.Caller.Parent
        FindLastNonEmptyCellColumn = .Range(MyColumn & .Rows.Count).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With

End Function

Effects of Executing Macro Example to Find Last Non Empty Cell in Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A6 to A30) contains data.
  • Cell C7 contains the letter of the column whose last non empty cell is sought (A).
  • Cell D7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the last non empty cell in column A (MyColumn). This is cell A30
  • Cell E7 displays the worksheet formula used in cell D7 (=FindLastNonEmptyCellColumn(C7)). The letter of the column whose last non empty cell is sought is stored in cell C7 (C7).
Example: Find last non empty cell in column with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#16. Excel VBA Find Empty (or Blank) Cells

VBA Code to Find Empty (or Blank) Cells

To find all empty (or blank) cells in a cell range, use the following structure/template in the applicable procedure:

Dim BlankCellsObjectVariable As Range
On Error Resume Next
Set BlankCellsObjectVariable = RangeObjectWithBlankCells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not BlankCellsObjectVariable Is Nothing Then
    StatementsIfBlankCells
End If

The following Sections describe the main elements in this structure.

Line #1: Dim BlankCellsObjectVariable As Range

Dim

The Dim statement:

  1. Declares variables.
  2. Allocates storage space.
BlankCellsObjectVariable

The name of the variable declared with the Dim statement.

BlankCellsObjectVariable holds/represents the empty (or blank) cells found.

As Range

The data type of the variable declared with the Dim statement.

BlankCellsObjectVariable is declared as of the Range object data type. The Range object represents a cell or cell range.

Line #2: On Error Resume Next

The On Error Resume Next statement specifies that, when a run-time error occurs, control passes to the statement immediately following that statement where the error occurred. Therefore, procedure execution continues.

Line #3 returns an error (Run time error ‘1004′: No cells were found) if the cell range where you search for empty (or blank) cells doesn’t contain any empty (or blank) cells.

Line #3: Set BlankCellsObjectVariable = RangeObjectWithBlankCells.SpecialCells(xlCellTypeBlanks)

Set

The Set statement assigns an object reference to an object variable.

BlankCellsObjectVariable

Object variable holding/representing the empty (or blank) cells found.

=

The assignment operator assigns an object reference (returned by the Range.SpecialCells method) to an object variable (BlankCellsObjectVariable).

RangeObjectWithBlankCells

A Range object representing the cell range you search in for empty (or blank) cells.

SpecialCells

The Range.SpecialCells method returns a Range object representing all cells matching a specified:

  • Type; and
  • Value.
xlCellTypeBlanks

The Type parameter of the Range.SpecialCells method:

  • Specifies the cells to include in the Range object returned by the Range.SpecialCells method.
  • Can take any of the built-in constants/values from the XlCellType enumeration.

To find all empty (or blank) cells in a cell range, set the Type parameter to xlCellTypeBlanks. xlCellTypeBlanks results in the Range.SpecialCells method including blank/empty cells in the Range object it returns.

Line #4: On Error GoTo 0

The On Error GoTo 0 statement disables error handling (originally enabled by line #2).

Line #5 and #7: If Not BlankCellsObjectVariable Is Nothing Then | End If

If… Then | End If

The If… Then… Else statement:

  • Conditionally executes a set of statements (line #6);
  • Depending on an expression’s value (Not BlankCellsObjectVariable Is Nothing).
Not BlankCellsObjectVariable Is Nothing

The condition of an If… Then… Else statement is an expression evaluating to True or False. If the expression returns True, the applicable set of statements (line #6) is executed.

In this expression:

  • Not BlankCellsObjectVariable:
    • The Not operator performs a logical negation on an expression.
    • BlankCellsObjectVariable is the object variable holding/representing the empty (or blank) cells found.
    • BlankCellsObjectVariable holds/represents Nothing if no empty (or blank) cells are found. Therefore:
      • If the Range.SpecialCells method finds no empty (or blank) cells:
        • BlankCellsObjectVariable is Nothing.
        • Not BlankCellsObjectVariable is not Nothing.
      • If the Range.SpecialCells method finds empty (or blank) cells:
        • BlankCellsObjectVariable is not Nothing.
        • Not BlankCellsObjectVariable is Nothing.
  • Is: The Is operator is an object reference comparison operator.
  • Nothing: Nothing allows you to disassociate a variable from the data it previously represented. BlankCellsObjectVariable holds/represents Nothing if no empty (or blank) cells are found.

Line #6: StatementsIfBlankCells

Statements conditionally executed by the If… Then… Else statement if the applicable condition (Not BlankCellsObjectVariable Is Nothing) returns True (the Range.SpecialCells method finds empty or blank cells).

Macro Example to Find Empty (or Blank) Cells

The following macro example does the following:

  1. Find all empty (or blank) cells in the cell range containing cells A6 to H30 in the “Find Blank Cells” worksheet in the workbook where the procedure is stored.
  2. Set the interior/fill color of all found empty (or blank) cells to light green.
Sub FindBlankCells()
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This procedure:
        '(1) Finds all blank cells in cells A6 to H30 of the "Find Blank Cells" worksheet in this workbook
        '(2) Sets the found cells' interior/fill color to light green
    
    'Declare object variable to hold/represent blank cells
    Dim MyBlankCells As Range
        
    'Enable error-handling
    On Error Resume Next
    
    'Identify blank cells in searched cell range
    Set MyBlankCells = ThisWorkbook.Worksheets("Find Blank Cells").Range("A6:H30").SpecialCells(xlCellTypeBlanks)
    
    'Disable error-handling
    On Error GoTo 0
    
    'Test whether blank cells were found in searched cell range
    If Not MyBlankCells Is Nothing Then
        
        'Set interior/fill color of blank cells found to light green
        MyBlankCells.Interior.Color = RGB(63, 189, 133)
        
    End If

End Sub

Effects of Executing Macro Example to Find Empty (or Blank) Cells

The following image illustrates the effects of using the macro example. In this example:

  • Columns A through H (cells A6 to H30) contain:
    • Data; and
    • A few empty cells (with light green interior/fill).
  • A text box (Find all blank cells) executes the macro example when clicked.

After the macro is executed, Excel sets the interior/fill color of all empty (or blank) cells to light green.

Example: Find blank cells with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#17. Excel VBA Find First Empty (or Blank) Cell in Cell Range

VBA Code to Find First Empty (or Blank) Cell in Cell Range

To find the first empty (or blank) cell in a cell range, use the following structure/template in the applicable procedure:

For Each iCell In CellRangeObject
    If IsEmpty(iCell) Then
        StatementsForFirstEmptyCell
        Exit For
    End If
Next iCell

The following Sections describe the main elements in this structure.

Lines #1 and #6: For Each iCell In CellRangeObject | Next iCell

For Each … In … | Next …

The For Each… Next statement repeats a series of statements for each element (iCell, an individual cell) in a collection (CellRangeObject).

iCell

Object variable (of the Range data type) used to iterate/loop through each element of the collection (CellRangeObject).

CellRangeObject

A Range object representing the cell range you search in.

The series of statements repeated by the For Each… Next statement are repeated for each individual element (iCell) in CellRangeObject.

Lines #2 and #5: If IsEmpty(iCell) Then | End If

If… Then | End If

The If… Then Else statement:

  • Conditionally executes a set of statements (lines #3 and #4);
  • Depending on an expression’s value (IsEmpty(iCell)).
IsEmpty(iCell)

The condition of an If… Then… Else statement is an expression evaluating to True or False. If the expression returns True, the applicable set of statements (lines #3 and #4) are executed.

In this expression:

  • As a general rule, the IsEmpty function returns a Boolean value (True or False) indicating whether a variable is initialized. You can (also) use the IsEmpty function to test whether a cell is empty (or blank).
  • iCell is an object variable representing the individual cell the For Each… Next statement is currently working with (iterating through).
  • IsEmpty(iCell) returns True or False as follows:
    • True if the cell (currently) represented by iCell is empty (or blank).
    • False if the cell (currently) represented by iCell isn’t empty. A cell isn’t considered to be empty if, for example, it contains a worksheet formula returning a zero-length string (“”).

Line #3: StatementsForFirstEmptyCell

Statements conditionally executed by the If… Then… Else statement if the applicable condition (IsEmpty(iCell)) returns True (the cell currently represented by iCell is empty or blank).

Line #4: Exit For

The Exit For statement:

  • Exits a For Each… Next loop.
  • Transfers control to the statement following the Next statement (line #6).

Macro Example to Find First Empty (or Blank) Cell in Cell Range

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MyRange): The cell range whose first empty cell you search for.
  2. Loops through each individual cell in the cell range (MyRange) and tests whether the applicable cell is empty (or blank).
  3. Returns the following:
    1. If MyRange contains empty cells, the address (as an A1-style relative reference) of the first empty cell in the cell range (MyRange).
    2. If MyRange doesn’t contain empty cells, the string “No empty cells found in cell range”.
Function FindNextEmptyCellRange(MyRange As Range) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyRange
        '(2) Loops through each individual cell in the cell range (MyRange) and tests whether the applicable cell is empty
        '(3) Returns the following (as applicable):
            'If there are empty cells in MyRange: The address (as an A1-style relative reference) of the first empty cell
            'If there are no empty cells in MyRange: The string "No empty cells found in cell range"
    
    'Declare object variable to iterate/loop through all cells in the cell range (MyRange)
    Dim iCell As Range
    
    'Loop through each cell in the cell range (MyRange)
    For Each iCell In MyRange
        
        'If the current cell is empty:
            '(1) Return the current cell's address (as an A1-style relative reference)
            '(2) Exit the For Each... Next loop
        If IsEmpty(iCell) Then
            FindNextEmptyCellRange = iCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
            Exit For
        End If
        
    Next iCell
    
    'If no empty cells are found in the cell range (MyRange), return the string "No empty cells found in cell range"
    If FindNextEmptyCellRange = "" Then FindNextEmptyCellRange = "No empty cells found in cell range"

End Function

Effects of Executing Macro Example to Find First Empty (or Blank) Cell in Cell Range

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Columns A through H (cells A6 to H30) contain:
    • Data; and
    • A few empty cells (with light green interior/fill).
  • Cell J7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first empty cell in the cell range (MyRange). This is cell B7.
  • Cell K7 displays the worksheet formula used in cell J7 (=FindNextEmptyCellRange(A6:H30)). The cell range where the search is carried out contains cells A6 to H30 (A6:H30).
Example: Find first empty cell in cell range with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#18. Excel VBA Find First Empty (or Blank) Cell in Column

VBA Code to Find First Empty (or Blank) Cell in Column

To find the first empty (or blank) cell in a column, use the following structure/template in the applicable statement:

ColumnRangeObject.Find(What:="", After:=ColumnRangeObject.Cells(ColumnRangeObject.Cells.Count), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

The following Sections describe the main elements in this structure.

ColumnRangeObject

A Range object representing the column whose first empty (or blank) cell you search for.

Find

The Range.Find method:

  • Finds specific information (the first empty or blank cell) in a cell range (ColumnRangeObject).
  • Returns a Range object representing the first cell where the information is found.
What:=””

The What parameter of the Range.Find method specifies the data to search for.

To find the first empty (or blank) cell in a column, set the What parameter to a zero-length string (“”).

After:=ColumnRangeObject.Cells(ColumnRangeObject.Cells.Count)

The After parameter of the Range.Find method specifies the cell after which the search begins.

To find the first empty (or blank) cell in a column, set the After parameter to “ColumnRangeObject.Cells(ColumnRangeObject.Cells.Count)”. For these purposes:

  • “ColumnRangeObject” is a Range object representing the column whose first empty (or blank) cell you search for.
  • The Range.Cells property (Cells) returns a Range object representing all cells in the column whose first empty (or blank) cell you search for.
  • The Range.Item property returns a Range object representing the last cell in the column whose first empty (or blank) cell you search for. For these purposes, the RowIndex parameter of the Range.Item property is set to the value returned by the Range.Count property (Count).
  • The Range.Count property (Count) returns the number of cells in the Range object returned by the Range.Cells property (ColumnRangeObject.Cells).
LookIn:=xlFormulas

The LookIn parameter of the Range.Find method:

  • Specifies the type of data to search in.
  • Can take any of the built-in constants/values from the XlFindLookIn enumeration.

To find the first empty (or blank) cell in a column, set the LookIn parameter to xlFormulas. xlFormulas refers to formulas.

LookAt:=xlWhole

The LookAt parameter of the Range.Find method:

  • Specifies against which of the following the data you are searching for is matched:
    • The entire/whole searched cell contents.
    • Any part of the searched cell contents.
  • The LookAt parameter can take any of the built-in constants/values from the XlLookAt enumeration.

To find the first empty (or blank) cell in a column, set the LookAt parameter to xlWhole. xlWhole matches the data you are searching for against the entire/whole searched cell contents.

SearchOrder:=xlByRows

The SearchOrder parameter of the Range.Find method:

  • Specifies the order in which the applicable cell range (ColumnRangeObject) is searched:
    • By rows.
    • By columns.
  • Can take any of the built-in constants/values from the XlSearchOrder enumeration.

To find the first empty (or blank) cell in a column, set the SearchOrder parameter to xlByRows. xlByRows searches by rows.

SearchDirection:=xlNext

The SearchDirection parameter of the Range.Find method:

  • Specifies the search direction:
    • Search for the previous match.
    • Search for the next match.
  • Can take any of the built-in constants/values from the XlSearchDirection enumeration.

To find the first empty (or blank) cell in a column, set the SearchDirection parameter to xlNext. xlNext searches for the next match.

Macro Example to Find First Empty (or Blank) Cell in Column

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MyColumn): The letter of the column (in the worksheet where the UDF is used) whose first empty (or blank) cell you search for.
  2. Finds the first empty (or blank) cell in the applicable column.
  3. Returns a string containing the address (as an A1-style relative reference) of the first empty (or blank) cell in the applicable column.
Function FindFirstEmptyCellColumn(MyColumn As String) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MyColumn
        '(2) Finds the first empty (blank) cell in the column whose letter is passed as argument (MyColumn) in the worksheet where the UDF is used
        '(3) Returns the address (as an A1-style relative reference) of the first empty (blank) cell in the column whose letter is passed as argument (MyColumn) in the worksheet where the UDF is used
    With Application.Caller.Parent.Columns(MyColumn)
        FindFirstEmptyCellColumn = .Find(What:="", After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With

End Function

Effects of Executing Macro Example to Find First Empty (or Blank) Cell in Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column I (cells I1 to I25) contains:
    • Data; and
    • A few empty cells (with light green interior/fill).
  • Cell A7 contains the letter of the column whose first empty (or blank) cell is sought (I).
  • Cell B7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an A1-style relative reference) of the first empty (or blank) cell in column I (MyColumn). This is cell I10.
  • Cell C7 displays the worksheet formula used in cell B7 (=FindFirstEmptyCellColumn(A7)). The letter of the column whose first empty (or blank) cell is sought is stored in cell A7 (A7).

Example: Find first empty (or blank) cell in column with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#19. Excel VBA Find Next Empty (or Blank) Cell in Column

VBA Code to Find Next Empty (or Blank) Cell in Column

To find the next empty (or blank) cell in a column, use the following structure/template in the applicable procedure:

If IsEmpty(RangeObjectSourceCell) Then
    RangeObjectSourceCell.ActionForNextEmptyCell
ElseIf IsEmpty(RangeObjectSourceCell.Offset(1, 0)) Then
    RangeObjectSourceCell.Offset(1, 0).ActionForNextEmptyCell
Else
    RangeObjectSourceCell.End(xlDown).Offset(1, 0).ActionForNextEmptyCell
End If

The following Sections describe the main elements in this structure.

Lines #1, #3, #5 and #7: If IsEmpty(RangeObjectSourceCell) Then | ElseIf IsEmpty(RangeObjectSourceCell.Offset(1, 0)) Then | Else | End If

If… Then | ElseIf… Then | Else | End If

The If… Then… Else statement:

  • Conditionally executes a set of statements (lines #2, #4 or #6);
  • Depending on an expression’s value (IsEmpty(RangeObjectSourceCell) or IsEmpty(RangeObjectSourceCell.Offset(1, 0))).
IsEmpty(RangeObjectSourceCell)

The condition of an If… Then… Else statement is an expression evaluating to True or False. If the expression returns True, the applicable set of statements (line #2) is executed.

In this expression:

  • As a general rule, the IsEmpty function returns a Boolean value (True or False) indicating whether a variable is initialized. You can (also) use the IsEmpty function to test whether a cell is empty (or blank).
  • RangeObjectSourceCell is a Range object representing the cell where the search (for the next empty or blank cell in the column) begins.
  • IsEmpty(RangeObjectSourceCell) returns True or False as follows:
    • True if the cell represented by RangeObjectSourceCell is empty (or blank).
    • False if the cell represented by RangeObjectSourceCell isn’t empty. A cell isn’t considered to be empty if, for example, it contains a worksheet formula returning a zero-length string (“”).
IsEmpty(RangeObjectSourceCell.Offset(1, 0))

The condition of an If… Then… Else statement is an expression evaluating to True or False. If the expression returns True, the applicable set of statements (line #4) is executed.

In this expression:

  • As a general rule, the IsEmpty function returns a Boolean value (True or False) indicating whether a variable is initialized. You can (also) use the IsEmpty function to test whether a cell is empty (or blank).
  • RangeObjectSourceCell is a Range object representing the cell where the search (for the next empty or blank cell in the column) begins.
  • The Range.Offset property (Offset(1, 0)) returns a Range object representing a cell range at an offset from the source cell range. In this expression:
    • The source cell range is the cell where the search (for the next empty or blank cell in the column) begins (RangeObjectSourceCell).
    • The offset from the source cell range is as follows:
      • 1 row downwards, as specified by the RowOffset parameter of the Range.Offset property (1).
      • No column offsetting, as specified by the ColumnOffset parameter of the Range.Offset property (0).
  • IsEmpty(RangeObjectSourceCell.Offset(1, 0)) returns True or False as follows:
    • True if the cell represented by the Range object returned by the Range.Offset property is empty.
    • False if the cell represented by the Range object returned by the Range.Offset property isn’t empty. A cell isn’t considered to be empty if, for example, it contains a worksheet formula returning a zero-length string (“”).

Line #2: RangeObjectSourceCell.ActionForNextEmptyCell

Statement conditionally executed by the If… Then… Else statement if the applicable condition (IsEmpty(RangeObjectSourceCell)) returns True.

RangeObjectSourceCell

A Range object representing the cell where the search (for the next empty or blank cell in the column) begins.

ActionForNextEmptyCell

VBA construct (usually a property or method) working with the applicable cell (represented by RangeObjectSourceCell).

Line #4: RangeObjectSourceCell.Offset(1, 0).ActionForNextEmptyCell

Statement conditionally executed by the If… Then… Else statement if the applicable condition (IsEmpty(RangeObjectSourceCell.Offset(1, 0))) returns True.

RangeObjectSourceCell

A Range object representing the cell where the search (for the next empty or blank cell in the column) begins.

Offset(1, 0)

The Range.Offset property (Offset(1, 0)) returns a Range object representing a cell range at an offset from the source cell range. For these purposes:

  • The source cell range is the cell where the search (for the next empty or blank cell in the column) begins (RangeObjectSourceCell).
  • The offset from the source cell range is as follows:
    • 1 row downwards, as specified by the RowOffset parameter of the Range.Offset property (1).
    • No column offsetting, as specified by the ColumnOffset parameter of the Range.Offset property (0).
ActionForNextEmptyCell

VBA construct (usually a property or method) working with the applicable cell (returned by the Range.Offset property).

Line #6: RangeObjectSourceCell.End(xlDown).Offset(1, 0).ActionForNextEmptyCell

Statement conditionally executed by the If… Then… Else statement if no previous condition (in lines #1 or #3) returns True.

RangeObjectSourceCell

A Range object representing the cell where the search (for the next empty or blank cell in the column) begins.

End(xlDown)

The Range.End property returns a Range object representing the cell at the end of the region containing the source range. In other words: The Range.End property is the rough equivalent of using the “Ctrl + Arrow Key” or “End, Arrow Key” keyboard shortcuts.

The Range.End property accepts one parameter: Direction. Direction:

  • Specifies the direction in which to move.
  • Can take the any of the built-in constants/values from the XlDirection enumeration.

To find the next empty (or blank) cell in a column, set the Direction parameter to xlDown. xlDown:

  • Results in moving down, to the bottom of the data region.
  • Is the rough equivalent of the “Ctrl + Down Arrow” or “End, Down Arrow” keyboard shortcuts.
Offset(1, 0)

The Range.Offset property (Offset(1, 0)) returns a Range object representing a cell range at an offset from the source cell range. For these purposes:

  • The source cell range is the cell represented by the Range object returned by the Range.End property (End(xlDown)).
  • The offset from the source cell range is as follows:
    • 1 row downwards, as specified by the RowOffset parameter of the Range.Offset property (1).
    • No column offsetting, as specified by the ColumnOffset parameter of the Range.Offset property (0).
ActionForNextEmptyCell

VBA construct (usually a property or method) working with the applicable cell (returned by the Range.Offset property).

Macro Example to Find Next Empty (or Blank) Cell in Column

The following macro (User-Defined Function) example does the following:

  1. Accepts one argument (MySourceCell): The cell where the search (for the next empty or blank cell in the column) begins.
  2. Finds the next empty (or blank) cell in the applicable column after MySourceCell.
  3. Returns a string containing the address (as an R1C1-style absolute reference) of the next empty (or blank) cell in the applicable column.
Function FindNextEmptyCellColumn(MySourceCell As Range) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 1 argument: MySourceCell
        '(2) Finds the next empty/blank cell in the applicable column and after the cell passed as argument (MySourceCell)
        '(3) Returns the address (as an R1C1-style absolute reference) of the next empty/blank cell in the applicable column and after the cell passed as argument (MySourceCell)
    
    With MySourceCell(1)
        
        'If first cell in cell range passed as argument (MySourceCell) is empty, obtain/return that cell's address
        If IsEmpty(MySourceCell(1)) Then
            FindNextEmptyCellColumn = .Address(ReferenceStyle:=xlR1C1)
        
        'If cell below first cell in cell range passed as argument (MySourceCell) is empty, obtain/return that cell's address
        ElseIf IsEmpty(.Offset(1, 0)) Then
            FindNextEmptyCellColumn = .Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
        
        'Otherwise:
            '(1) Find the next empty/blank cell in the applicable column and after the first cell in cell range passed as argument (MySourceCell)
            '(2) Obtain/return the applicable cell's address
        Else
            FindNextEmptyCellColumn = .End(xlDown).Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
        End If
        
    End With

End Function

Effects of Executing Macro Example to Find Next Empty (or Blank) Cell in Column

The following image illustrates the effects of using the macro (User-Defined Function) example. In this example:

  • Column A (cells A6 to A30) contains:
    • Data; and
    • A few empty cells.
  • Cell C7 contains the worksheet formula that works with the macro (User-Defined Function) example. This worksheet formula returns the address (as an R1C1-style absolute reference) of the next empty (or blank) cell in column A after cell A13 (MySourceCell). This cell R21C1 (or A21 as an A1-style relative reference).
  • Cell D7 displays the worksheet formula used in cell C7 (=FindNextEmptyCellColumn(A13)). The cell where the search (for the next empty or blank cell in the column) begins is A13 (A13).
Example: Find next empty cell in column with VBA macros

Get immediate free access to the Excel VBA Find workbook example

#20. Excel VBA Find Value in Array

VBA Code to Find Value in Array

To find a numeric value in a one-dimensional array, use the following structure/template in the applicable procedure:

Dim PositionOfValueInArray As Variant
Dim LoopCounter As Long
PositionOfValueInArray = OutputIfValueNotInArray
For LoopCounter = LBound(SearchedArray) To UBound(SearchedArray)
    If SearchedArray(LoopCounter) = SearchedValue Then
        PositionOfValueInArray = LoopCounter
        Exit For
    End If
Next LoopCounter

The following Sections describe the main elements in this structure.

Lines #1 and #2: Dim PositionOfValueInArray As Variant | Dim LoopCounter As Long

The Dim statement:

  1. Declares variables.
  2. Allocates storage space.
PositionOfValueInArray | LoopCounter

The names of the variables declared with the Dim statement.

  • PositionOfValueInArray holds/represents:
    • The position of the searched numeric value in the array, if the searched numeric value is found in the array.
    • The default/fallback output/data, if the searched numeric value isn’t found in the array.
  • LoopCounter represents the loop counter used in the For… Next loop in lines #4 to #9.
As Variant | As Long

The data type of the variables declared with the Dim statement.

  • PositionOfValueInArray is of the Variant data type. The Variant data type can (generally) contain any type of data, except for fixed-length Strings.
  • LoopCounter is of the Long data type. The Long data type can contain integers between -2,147,483,648 and 2,147,483,647.

Line #3: PositionOfValueInArray = OutputIfValueNotInArray

PositionOfValueInArray

Variable holding/representing:

  • The position of the searched numeric value in the array, if the searched numeric value is found in the array.
  • The default/fallback output/data, if the searched numeric value isn’t found in the array.
=

The assignment operator assigns the result returned by an expression (OutputIfValueNotInArray) to a variable (PositionOfValueInArray).

OutputIfValueNotInArray

Expression specifying the default/fallback output/data to be assigned to the PositionOfValueInArray variable if the searched numeric value isn’t found in the array.

Lines #4 and #9: For LoopCounter = LBound(SearchedArray) To UBound(SearchedArray) | Next LoopCounter

For… = … To … | Next…

The For… Next statement repeats a series of statements a specific number of times.

LoopCounter

Variable holding/representing the loop counter.

LBound(…) | UBound(…)

The LBound/UBound functions return the lower/upper bound of an array’s dimension. The LBound/UBound functions accept two arguments:

  • ArrayName: The name of the array whose lower/upper bound you want to obtain.
  • Dimension:
    • An optional argument.
    • The dimension (of the applicable array) whose lower/upper bound you want to obtain.

If you omit specifying the Dimension argument, the LBound/UBound functions return the lower/upper bound of the array’s first dimension.

The initial/final values of LoopCounter are set to the following:

  • Initial value (Start) of LoopCounter: The output returned by the LBound function (LBound(SearchedArray)).
  • Final value (End) of LoopCounter: The output returned by the UBound function (UBound(SearchedArray)).
SearchedArray

The array you search in.

SearchedArray is passed as the ArrayName argument of the LBound/UBound functions.

Lines #5 and #8: If SearchedArray(LoopCounter) = SearchedValue Then | End If

If… Then… End If

The If… Then… Else statement:

  1. Conditionally executes a set of statements (lines #6 and #7);
  2. Depending on an expression’s value (SearchedArray(LoopCounter) = SearchedValue).
SearchedArray(LoopCounter) = SearchedValue

The condition of an If… Then… Else statement is an expression evaluating to True or False.

  • If the expression returns True, a set of statements (lines #6 and #7) is executed.
  • If the expression returns False:
    • No statements are executed.
    • Execution continues with the statement following End If (line #8).

In this expression:

  • SearchedArray: The array you search in.
  • LoopCounter: Variable holding/representing the loop counter.
  • SearchedArray(LoopCounter): Array element whose index number is LoopCounter.
  • =: The equal to comparison operator returns True or False as follows:
    • True if both expressions (SearchedArray(LoopCounter) and SearchedValue) are equal.
    • False if the expressions (SearchedArray(LoopCounter) and SearchedValue) are not equal.
  • SearchedValue: Numeric value you search for in the array (SearchedArray).

Line #6: PositionOfValueInArray = LoopCounter

PositionOfValueInArray

Variable holding/representing:

  • The position of the searched numeric value in the array, if the searched numeric value is found in the array.
  • The default/fallback output/data, if the searched numeric value isn’t found in the array.
=

The assignment operator assigns the result returned by an expression (LoopCounter) to a variable (PositionOfValueInArray).

LoopCounter

Variable holding/representing the loop counter.

Line #7: Exit For

The Exit For statement:

  • Exits a For… Next loop.
  • Transfers control to the statement following the Next statement (line #8).

Macro Example to Find Value in Array

The following macro (User-Defined Function) example does the following:

  1. Accepts two arguments:
    1. MyArray: The array you search in.
    2. MyValue: The numeric value you search for.
  2. Loops through each element in MyArray and tests whether the applicable element is equal to MyValue.
  3. Returns the following:
    1. If MyValue is not found in MyArray, the string “Value not found in array”.
    2. If MyValue is found in MyArray, the position/index number of MyValue in MyArray.
Function FindValueInArray(MyArray As Variant, MyValue As Variant) As Variant
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 2 arguments: MyArray and MyValue
        '(2) Loops through each element in the array (MyArray) and tests whether the applicable element is equal to the searched value (MyValue)
        '(3) Returns the following (as applicable):
            'If MyValue is not found in MyArray: The string "Value not found in array"
            'If MyValue is found in MyArray: The position (index number) of MyValue in MyArray
    
    'Declare variable to represent loop counter
    Dim iCounter As Long

    'Specify default/fallback value/string to be returned ("Value not found in array") if MyValue is not found in MyArray
    FindValueInArray = "Value not found in array"

    'Loop through each element in the array (MyArray)
    For iCounter = LBound(MyArray) To UBound(MyArray)
    
        'Test if the current array element is equal to MyValue
        If MyArray(iCounter) = MyValue Then
            
            'If the current array element is equal to MyValue:
                '(1) Return the position (index number) of the current array element
                '(2) Exit the For... Next loop
            FindValueInArray = iCounter
            Exit For
            
        End If
        
    Next iCounter

End Function

Effects of Executing Macro Example to Find Value in Array

To illustrate the effects of using the macro (User-Defined Function) example, I use the following macro. The following macro does the following:

  1. Declare an array (MySearchedArray).
  2. Fill the array with values (0, 10, 20, …, 90).
  3. Call the FindValueInArray macro (User-Defined Function) example to find the number “50” in the array.
  4. Display a message box with the value/string returned by the FindValueInArray macro (User-Defined Function) example.
Sub FindValueInArrayCaller()
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This procedure:
        '(1) Declares an array and fills it with values (0, 10, 20, ..., 90)
        '(2) Calls the FindValueInArray UDF to find the number "50" in the array
        '(3) Displays a message box with the value/string returned by the FindValueInArray UDF
    
    'Declare a zero-based array with 10 elements of the Long data type
    Dim MySearchedArray(0 To 9) As Long
    
    'Assign values to array elements
    MySearchedArray(0) = 0
    MySearchedArray(1) = 10
    MySearchedArray(2) = 20
    MySearchedArray(3) = 30
    MySearchedArray(4) = 40
    MySearchedArray(5) = 50
    MySearchedArray(6) = 60
    MySearchedArray(7) = 70
    MySearchedArray(8) = 80
    MySearchedArray(9) = 90
    
    'Do the following:
        '(1) Call the FindValueInArray UDF and search for the number "50" in MySearchedArray
        '(2) Display a message box with the value/string returned by the FindValueInArray UDF
    MsgBox FindValueInArray(MySearchedArray, 50)
    

End Sub

The following GIF illustrates the effects of using the macro above to call the macro (User-Defined Function) example.

As expected, this results in Excel displaying a message box with the number 5. This is the position of the searched numeric value (50) in the array.

Example: Find value in array with VBA macros

Get immediate free access to the Excel VBA Find workbook example

Learn More About Excel VBA Find

Workbook Example Used in this Excel VBA Find Tutorial

This VBA Find Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples above. You can get free access to this example workbook by clicking the button below.

Get immediate free access to the Excel VBA Find workbook example

The Power Spreadsheets Library

The Books at The Power Spreadsheets Library are comprehensive and actionable guides for professionals who want to:

  • Automate Excel;
  • Save time for the things that really matter; and
  • Open new career opportunities.

Learn more about The Power Spreadsheets Library here.

Добавить комментарий