Makro untuk mencari nilai di excel

Posting ini mencakup semua yang perlu Anda ketahui tentang fungsi VBA Find. Ini menjelaskan, cara menggunakan Temukan, dalam istilah sederhana. Ini juga memiliki banyak contoh kode Temukan yang dapat Anda gunakan sekarang

Jika Anda ingin langsung ke contoh Temukan kemudian periksa

Jika Anda ingin menelusuri teks dalam string, maka Anda mencari  fungsinya

Jika Anda ingin menemukan baris atau kolom terakhir dengan data, buka

Unduh Kode Sumber

Apa itu Fungsi Temukan VBA?

Fungsi Find sangat umum digunakan di VBA. Tiga hal terpenting yang perlu diketahui tentang Find adalah

1. Fungsi Temukan adalah anggota dari Rentang
2. Itu mencari rentang sel yang berisi nilai atau format tertentu
3. Ini pada dasarnya sama dengan menggunakan Dialog Temukan pada lembar kerja Excel

pengantar

Dialog Temukan Excel

Untuk melihat dialog Temukan Excel, buka pita Beranda dan klik Temukan & Pilih di bagian Pengeditan. Di menu yang muncul, pilih Temukan (pintasannya adalah Ctrl + F)

 
 
Saat  Anda melakukannya, dialog berikut akan muncul

 
 
Fungsi VBA Find menggunakan sebagian besar opsi yang dapat Anda lihat di Dialog ini

Cara Menggunakan Opsi Dengan Temukan

Untuk menggunakan opsi, Anda meneruskannya sebagai parameter ke fungsi Temukan. Ini mirip dengan cara Anda menggunakan fungsi lembar kerja. Misalnya, fungsi Sum memiliki Range sebagai parameter. Ini berarti Anda memberikan rentang saat Anda menggunakannya

VBA Find menggunakan parameter dengan cara yang sama. Anda harus memberikan barang yang Anda cari. Ini adalah parameter pertama dan diperlukan

Parameter lainnya adalah opsional. Jika Anda tidak menggunakannya maka Find akan menggunakan setelan yang ada. Kami akan melihat lebih banyak tentang ini segera

Tabel di bagian selanjutnya menunjukkan parameter ini. Bagian selanjutnya, memberikan contoh dan detail tentang cara menggunakan parameter ini

VBA Temukan Parameter

Tabel berikut menampilkan semua parameter Temukan

Catatan Penting tentang Temukan Parameter

Ingatlah hal-hal berikut karena dapat menyebabkan banyak frustrasi saat menggunakan Temukan

Seperti yang Anda lihat dari tabel, sebagian besar parameter VBA Find bersifat opsional. Seperti yang kami katakan sebelumnya, jika Anda tidak mengatur parameter Temukan, itu menggunakan pengaturan yang ada

Misalnya, jika Anda menyetel parameter LookIn ke xlComments, nilai hanya akan dicari dalam komentar. Lain kali Anda menjalankan Temukan (baik dari Dialog atau dari VBA), setelan LookIn yang ada akan menjadi Komentar

Kode berikut menunjukkan contohnya

' 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"

 
 
Ini berlaku untuk parameter LookIn, LookAt, SearchOrder, dan MatchByte

Temukan Nilai Pengembalian

Jika item pencarian ditemukan, Temukan mengembalikan  sel dengan nilainya. Artinya, ini mengembalikan tipe Range dari satu sel

Jika item pencarian tidak ditemukan maka Temukan mengembalikan objek yang disetel ke Tidak Ada

Dalam contoh berikut, Anda akan melihat cara menangani nilai kembalian

Cara melakukan Pencarian Sederhana

Mari kita mulai dengan contoh sederhana dari VBA Find. Anda memerlukan tiga hal saat menggunakan fungsi Temukan

1. Rentang untuk mencari
2. Nilai yang Anda cari
3. Rentang untuk menyimpan sel yang dikembalikan

 
 
Mari kita ambil contoh data berikut

 
 
Kita akan mencari teks “Jena” di sel A1 hingga A5

Kode berikut mencari "Jena". Saat menemukan "Jena", sel akan ditempatkan di variabel rgFound

' 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

 
 
Kode di atas menunjukkan pencarian paling dasar yang dapat Anda lakukan. Jika ini pertama kalinya Anda menggunakan fungsi VBA Find maka saya sarankan Anda berlatih dengan contoh sederhana seperti ini

Jika Anda ingin mencoba contoh-contoh ini, Anda dapat mengunduh buku kerja dari pos ini
 

Ketika Nilai tidak Ditemukan

Saat Anda menggunakan fungsi VBA Find, akan ada saatnya Anda tidak menemukan kecocokan. Anda perlu menangani ini dalam kode Anda atau Anda akan mendapatkan kesalahan berikut saat mencoba menggunakan rentang yang dikembalikan

 
 
Kode berikut akan memberikan kesalahan ini jika teks "John" tidak ditemukan di rentang A1 hingga A5

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

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

 
 
Yang perlu kita lakukan adalah memeriksa nilai pengembalian seperti yang ditunjukkan kode berikut

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

Menggunakan Setelah dengan Temukan

Parameter Setelah digunakan jika Anda ingin memulai pencarian dari sel tertentu. Saat, Dialog Temukan Excel digunakan, sel aktif dianggap sebagai sel Setelah. Dengan kata lain, sel ini adalah titik awal pencarian. Di VBA, jika tidak ada parameter After yang ditentukan maka pencarian dimulai di sel kiri atas rentang

Contoh 1 Tanpa Setelah

Mari kita lihat kode berikut

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

 
 
Temukan akan mengembalikan sel A2 karena ini adalah tempat “Rachal” pertama ditemukan

Contoh 2 Menggunakan Setelah

Pada contoh berikutnya, kita menggunakan after. Kami memberi tahu VBA untuk memulai penelusuran "Rachal" setelah sel A2

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

 
 
Ini akan mengembalikan sel A6

Contoh 3 Membungkus

Jika kecocokan tidak ditemukan maka pencarian akan “memutar”. Ini berarti akan kembali ke awal rentang

Dalam contoh berikut, kami mencari Drucilla. Kami memulai pencarian kami Setelah sel A2. Temukan akan mencari dari A3 ke A6 dan kemudian akan berpindah ke A1

Jadi kode berikut akan mengembalikan A1 karena tidak ada teks "Drucilla" dari A3 ke A6

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

 
 
Urutan pencarian untuk contoh ini adalah A4, A5, A6, A1

 
Anda dapat mencoba sendiri contoh ini dengan mengunduh buku kerja dari pos
 

Menggunakan LookIn dengan Temukan

Menggunakan LookIn memungkinkan Anda mencari di Nilai, Rumus, atau Komentar

Catatan Penting. Jika sel hanya memiliki teks, teks ini dianggap sebagai rumus DAN  nilai. Lihat tabel di bawah untuk detailnya

 
 
Kita akan menggunakan data sampel berikut

A2 Berisi "Apple" sebagai nilai saja
A3 Berisi  “Apple” sebagai formula saja
A4 Berisi "Apple" di  komentar saja

 
 
Kode di bawah mencari "Apple" dalam berbagai jenis. nilai, rumus, komentar berulir, dan catatan

Untuk melihat contoh kerja kode ini, Anda dapat mengunduh kode sumber dari posting ini

' 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

 
Catatan penting bahwa saya telah menggunakan xlCommentsThreaded untuk yang ketiga karena komentar beralur digunakan di Office 365. Jika Anda menggunakan versi lama yang tidak memiliki komentar, gunakan xlComments
 
 

Menggunakan LookAt dengan Temukan

Menggunakan fungsi LookAt cukup mudah

1. xlWhole berarti nilai pencarian harus cocok dengan seluruh konten sel
2. xlPart berarti nilai pencarian hanya harus cocok dengan bagian sel

 
 
Contoh berikut memiliki "Apple" sebagai bagian dari isi sel di A2 dan merupakan isi penuh di sel A3

 
 
Temukan pertama dalam kode berikut menemukan "Apple" di A2. Find kedua sedang mencari kecocokan penuh jadi temukan 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

 
Anda dapat mencoba sendiri contoh ini dengan mengunduh buku kerja dari pos
 

Menggunakan SearchOrder dengan Temukan

Parameter SearchOrder memungkinkan kita untuk mencari berdasarkan baris atau kolom. Dalam contoh data berikut, kami memiliki dua kemunculan teks "Elli"

 
 
Jika kita mencari berdasarkan baris, kita akan menemukan “Elli” di B2 terlebih dahulu. Ini karena kita mencari dengan urutan baris 1, lalu baris 2 dst

Jika kita mencari berdasarkan kolom, kita akan menemukan "Elli" di A5 terlebih dahulu. Ini karena kita mencari dengan urutan kolom A, Kolom B dst

 
 
Kode berikut menunjukkan contoh penggunaan SearchOrder dengan contoh data ini

' 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

Menggunakan SearchDirection dengan Temukan

SearchDirection memungkinkan Anda untuk menelusuri maju atau mundur. Jadi bayangkan Anda memiliki rentang A1. A7. Pencarian menggunakan xlNext akan masuk dalam urutan

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

Pencarian menggunakan xlPrevious akan dilakukan sesuai urutan

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

 
 
Menggunakan xlNext dengan data sampel akan mengembalikan A2 karena ini menemukan kecocokan pertama. Menggunakan xlPrevious akan mengembalikan A6

' 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

Menggunakan xlPrevious dengan Setelah

Jika Anda menggunakan  parameter dengan xlPrevious maka akan dimulai sebelumnya dari sel Setelah. Jadi jika setelah sel kita atur menjadi A6 maka urutan pencariannya adalah

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

Kode berikut menunjukkan contohnya

' 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

Menggunakan MatchCase dengan Find

Parameter MatchCase digunakan untuk menentukan apakah kasus huruf penting dalam pencarian. Itu dapat diatur ke True atau False

• Benar – huruf besar-kecil harus cocok
• Salah – huruf tidak penting

 
 
Daftar sampel berikut memiliki dua entri untuk "Elli". Yang kedua memiliki huruf kecil e

 
 
Contoh kode berikut menampilkan hasil pengaturan MatchCase menjadi True dan False

' 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

Menggunakan MatchByte dengan Temukan

Parameter MatchByte digunakan untuk bahasa dengan kumpulan karakter byte ganda. Ini adalah bahasa seperti Cina/Jepang/Korea

Jika Anda tidak menggunakannya maka parameter ini tidak relevan. Mereka digunakan sebagai berikut

• True artinya mencocokkan hanya karakter double-byte dengan karakter double-byte
• Sarana palsu untuk karakter double-byte dapat cocok dengan karakter single atau double-byte

Menggunakan Wild Card

Kita bisa menggunakan simbol asterisk (*) sebagai wild card saat mencari teks. Tanda bintang mewakili satu atau lebih karakter
Sebagai contoh
“T*” akan menemukan kata yang dimulai dengan T
“To*” akan menemukan kata yang dimulai dengan To
“*y” akan menemukan kata apa pun yang diakhiri dengan y
“*ey” akan menemukan kata yang diakhiri dengan ey

Kode di bawah menunjukkan contoh penggunaan wildcard berdasarkan data ini

' 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

Menggunakan SearchFormat dengan Temukan

Format Pencarian sedikit berbeda dari parameter lainnya. Ini memungkinkan Anda untuk mencari format sel seperti jenis font atau warna sel

Anda perlu mengatur format terlebih dahulu dengan menggunakan Aplikasi. properti FindFormat. Kemudian Anda menyetel SearchFormat ke True untuk mencari format ini

Dalam data sampel berikut, kami memiliki dua sel yang diformat. Sel A5 diatur ke Bold dan Sel A6 memiliki warna isian yang diatur ke merah

 
 
Kode berikut mencari sel tebal

' 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

Menggunakan Wild Card dengan Format

Anda dapat mencari sel berdasarkan format saja. Dengan kata lain, nilai dalam sel diabaikan dalam pencarian. Caranya dengan menempatkan “*” di string pencarian

Kode berikut mencari sel yang diformat – warna sel dalam contoh ini diatur ke merah. Isi sel tidak masalah

' 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

Penting – Format Kliring

Saat Anda menyetel atribut FindFormat, atribut tersebut tetap di tempatnya hingga Anda menyetelnya lagi. Ini adalah sesuatu yang harus diwaspadai.

Misalnya, bayangkan Anda menyetel format menjadi tebal lalu menggunakan Temukan. Kemudian Anda mengatur format ke ukuran font 12 dan menggunakan Temukan lagi. Pencarian akan mencari sel yang fontnya tebal DAN berukuran 12

Oleh karena itu, sebaiknya hapus formatnya sebelum Anda menggunakannya seperti yang telah saya lakukan pada contoh di atas

' 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

 
 
Anda dapat melihat kami menggunakan ini pada contoh SearchFormat kedua di atas

Beberapa Pencarian

Dalam banyak kasus, Anda ingin mencari beberapa kejadian dengan nilai yang sama. Untuk melakukan ini, kami menggunakan fungsi Temukan terlebih dahulu. Kemudian kita menggunakan. Fungsi FindNext untuk menemukan item berikutnya

 
 
Pencarian FindNext berdasarkan pengaturan yang kami gunakan di Find. Kode berikut menunjukkan contoh sederhana untuk menemukan kemunculan pertama dan kedua dari teks "Elli"

' 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

 
 
Terkadang Anda tidak akan tahu berapa banyak kejadian yang ada. Dalam hal ini kami menggunakan loop untuk terus mencari sampai kami menemukan semua item

Kami menggunakan Temukan untuk mendapatkan item pertama. Jika kami menemukan item, kami kemudian menggunakan Do Loop. FindNext untuk menemukan kejadian lainnya

FindNext akan selesai. Artinya, setelah menemukan A9 maka akan melanjutkan pencarian di A1. Oleh karena itu, kami menyimpan alamat sel pertama yang kami temukan. Saat FindNext mengembalikan sel ini lagi, kami tahu bahwa kami telah menemukan semua item

Kode berikut akan menemukan semua kemunculan Elli

' 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

 
 
Output dari kode ini adalah
Ditemukan. $A$2
Ditemukan. $A$5
Ditemukan. $A$8

Menemukan Sel Terakhir yang Mengandung Data

Tugas yang sangat umum di VBA adalah menemukan sel terakhir yang berisi data dalam satu baris atau kolom. Ini tidak menggunakan fungsi VBA Find. Sebagai gantinya, kami menggunakan kode berikut untuk menemukan baris terakhir dengan data

' 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

 
 
Untuk menemukan kolom terakhir dengan data, kami menggunakan kode serupa

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

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

Menemukan Sel dengan Pola

Jika Anda ingin menemukan sel dengan pola tertentu maka Anda harus menggunakan operator, bukan Cari

Misalnya, untuk menemukan semua nama yang dimulai dengan E, Anda dapat menggunakan kode berikut

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

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

 
 
Jika Anda ingin tahu lebih banyak tentang ini maka periksa

Untuk melihat contoh dunia nyata menggunakan pencocokan pola, periksa

Alternatif untuk menggunakan VBA Find

Jika Anda mengharapkan klik dalam jumlah besar, menggunakan larik adalah opsi yang lebih baik. Anda dapat membaca rentang sel ke array dengan sangat cepat dan efisien

Kode berikut membaca nilai sel ke array dan kemudian membaca array untuk menghitung item

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

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

 
 
Jika Anda ingin mengetahui lebih lanjut tentang array, lihat postingan Panduan Lengkap Menggunakan Array di Excel VBA

Temukan dan ganti

Untuk  menemukan dan Mengganti, Anda dapat menggunakan fungsi Ganti. Ini sangat mirip dengan menggunakan fungsi Temukan

Fungsi ganti berada di luar cakupan posting ini meskipun banyak dari apa yang Anda baca di sini dapat digunakan dengannya. Anda dapat melihat detailnya di Microsoft – VBA Replace Function

Apa berikutnya?

Tutorial VBA Gratis Jika Anda baru mengenal VBA atau ingin mempertajam keterampilan VBA yang ada, mengapa tidak mencoba Tutorial VBA Utama

Pelatihan Terkait. Dapatkan akses penuh ke webinar pelatihan Excel VBA dan semua tutorialnya

(CATATAN. Berencana membangun atau mengelola Aplikasi VBA? . )

Bagaimana cara membuat makro pencarian di Excel?

Bagaimana cara mencari teks di makro Excel?

Bagaimana menemukan nilai dalam lembar menggunakan VBA?

Apakah Xlookup adalah makro?