Cari beberapa lembar di excel vba

Artikel ini mengilustrasikan cara membuat kotak pencarian di excel untuk beberapa lembar. Asumsikan Anda memiliki 20 lembar di buku kerja Anda. Sekarang Anda perlu mencari data di semua sheet yang relevan dengan kata kunci. Akan sangat memakan waktu dan melelahkan untuk melakukan ini secara manual. Untungnya, Anda dapat membuat kotak pencarian untuk buku kerja Anda untuk mencari di semua lembar. Ikuti artikel untuk mempelajari cara melakukannya

Daftar isi

Download Buku Latihan

Anda dapat mengunduh buku latihan dari tombol unduh di bawah ini

Kotak Pencarian untuk Beberapa Lembar. xlsm

2 Cara Membuat Kotak Pencarian di Excel untuk Beberapa Lembar

Asumsikan Anda memiliki data sebagai berikut dalam beberapa lembar. Anda perlu mencari berdasarkan nama untuk menemukan dengan cepat semua hasil yang relevan di lembar tersebut

Ikuti cara-cara di bawah ini untuk bisa melakukannya

1. Cari Beberapa Lembar di Excel dengan Pemformatan Bersyarat

Ikuti langkah-langkah di bawah ini untuk menerapkan pemformatan bersyarat untuk mencari dengan cepat melalui beberapa lembar di excel

📌 Langkah

• Pertama, pilih rentang B3. F21 di Lembar2. Kemudian, pilih Pemformatan Bersyarat >> Aturan Baru dari tab Beranda

• Selanjutnya, pilih 'Gunakan rumus untuk menentukan sel mana yang akan diformat' sebagai jenis aturan. Setelah itu, masukkan rumus berikut di kotak teks di bawah untuk Deskripsi Aturan

=SEARCH(Sheet1!$B$4,$B3&$C3&$D3&$E3&$F3)_

• Selanjutnya, klik Format dan pilih warna Isi untuk menyorot sel. Lalu klik Oke

• Setelah itu, terapkan pemformatan yang sama ke rentang data di lembar lain dengan mengikuti prosedur yang sama
• Sekarang buka Sheet1 dan masukkan kata kunci di sel B4. Lebih baik menggunakan wildcard (*, ?, dll. ) di kotak telusur jika Anda tidak memerlukan pencocokan persis

• Lalu pergi ke Sheet2 untuk melihat hasil berikut. Anda dapat melihat hasil yang relevan disorot

• Selanjutnya, buka Sheet3 untuk melihat hasil yang serupa sebagai berikut

2. Buat Kotak Pencarian di Excel untuk Beberapa Lembar dengan VBA

Sekarang Anda mungkin berpikir untuk mendapatkan data yang disorot itu di lembar yang sama i. e. Sheet1 tempat Anda mencari kata kunci. Ikuti langkah-langkah di bawah ini untuk dapat melakukannya dengan VBA

📌 Langkah

• Pertama, buat salinan kumpulan data Anda, lalu tekan ALT+F11 untuk membuka jendela VBA. Kemudian pilih Sisipkan >> Modul

• Selanjutnya, salin kode berikut

Option Explicit
Sub SearchMultipleSheets()
  Dim wsh As Worksheet, ash As Worksheet
  Dim all, dst As Range
  Dim Wht As Variant
  Set ash = ActiveSheet
  Set dst = ash.Range("A6")
  Wht = ash.Range("A4").Value
  Application.ScreenUpdating = False
  dst.CurrentRegion.Clear
  For Each wsh In Worksheets
    If wsh.Name = ash.Name Then GoTo NextSheet
    Set all = SearchAll(wsh.Columns("A"), Wht, LookAt:=xlWhole)
    If all Is Nothing Then GoTo NextSheet
    all.EntireRow.Copy dst
    Set dst = ash.Range("A" & Rows.Count).End(xlUp).Offset(1)
NextSheet:
  Next
  Application.ScreenUpdating = True
End Sub
Function SearchAll(ByVal Where As Range, ByVal What, _
    Optional ByVal After As Variant, _
    Optional ByVal LookIn As XlFindLookIn = xlValues, _
    Optional ByVal LookAt As XlLookAt = xlWhole, _
    Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
    Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
    Optional ByVal MatchCase As Boolean = False, _
    Optional ByVal SearchFormat As Boolean = False) As Range
  Dim First_Address As String
  Dim R As Range
  Dim Stck As New Collection
  Dim Tmp() As Range, Item
  Dim m, n As Long
  If Where Is Nothing Then Exit Function
  If SearchDirection = xlNext And IsMissing(After) Then
    Set R = Where.Areas(Where.Areas.Count)
    Set After = R.Cells(R.Rows.Count * CDec(R.Columns.Count))
  End If
  Set R = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
    SearchDirection, MatchCase, SearchFormat:=SearchFormat)
  If R Is Nothing Then Exit Function
  First_Address = R.Address
  Do
    Stck.Add R
    If SearchFormat Then
      Set R = Where.Find(What, R, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
    Else
      If SearchDirection = xlNext Then
        Set R = Where.FindNext(R)
      Else
        Set R = Where.FindPrevious(R)
      End If
    End If
    If R Is Nothing Then Exit Do
  Loop Until First_Address = R.Address
  ReDim Tmp(0 To Stck.Count - 1)
  m = 0
  For Each Item In Stck
    Set Tmp(m) = Item
    m = m + 1
  Next
  n = 1
  Do
    For m = 0 To UBound(Tmp) - n Step n * 2
      Set Tmp(m) = Union(Tmp(m), Tmp(m + n))
    Next
    n = n * 2
  Loop Until n > UBound(Tmp)
  Set SearchAll = Tmp(0)
End Function

• Setelah itu, tempel kode ke modul

• Sekarang, pindahkan data di setiap lembar ke sel A1 jika memungkinkan, lalu simpan file sebagai buku kerja yang mendukung makro. Ini karena VBA mungkin bertindak sedikit aneh jika ada kolom kosong di awal kumpulan data. Anda dapat melewati langkah ini dan melihat apakah itu berhasil
• Selanjutnya, kembali ke Sheet1 lalu sisipkan persegi panjang dengan memilih Sisipkan >> Ilustrasi >> Bentuk >> Persegi Panjang. Setelah itu ubah warna Fill dan ketik Search agar terlihat sesuai kebutuhan

• Sekarang, klik kanan pada kotak persegi panjang untuk memilih 'Tetapkan makro'. Kemudian, pilih nama makro dan klik OK

• Selanjutnya, ketikkan kata kunci pencarian di sel A4 lalu klik tombol Cari. Setelah itu, Anda akan melihat semua hasil relevan yang disorot dalam metode sebelumnya yang tercantum di bawah ini

Penjelasan Kode VBA

Opsi Eksplisit
Ini memaksa Anda untuk mendeklarasikan semua variabel

Sub SearchMultipleSheets()
Kami akan menulis kode di dalam prosedur subjek ini

Dim wsh Sebagai Lembar Kerja, abu Sebagai Lembar Kerja
Redup semua, dst As Range
Redup Apa Sebagai Varian
Mendeklarasikan variabel yang diperlukan

Tetapkan abu = Lembar Aktif
Tetapkan dst = abu. Rentang ("A6")
Wht = abu. Rentang ("A4"). Nilai
Mendefinisikan variabel

Aplikasi. Pembaruan Layar = Salah
Ini membuat VBA berjalan di latar belakang agar bekerja lebih cepat

dst. Wilayah Saat Ini. Jernih
Menghapus apa pun yang tersisa di wilayah keluaran

Untuk Setiap wsh Di Lembar Kerja
Ulangi setiap lembar

Jika wsh. Nama = abu. Beri Nama Kemudian Buka Lembar Berikutnya
Melewatkan lembar aktif

Atur semua = SearchAll(wsh. Kolom ("A"), Apa, Lihat. =xlSeluruh)
Panggil fungsi SearchAll yang ditentukan di bawah ini

Jika semua Tidak Ada Lalu Buka Lembar Berikutnya
Pergi ke lembar berikutnya jika tidak ada yang ditemukan di lembar saat ini

semua. Seluruh baris. Salin dst
Menyalin seluruh baris sel yang cocok

Tetapkan dst = abu. Rentang ("A" & Baris. Menghitung). Akhir(xlUp). Offset(1)
Menetapkan kolom A sebagai rentang keluaran

Lembar Berikutnya
Pernyataan GoTo mengarahkan VBA untuk dijalankan dari sini

Berikutnya
Pergi ke lembar berikutnya untuk mengulang tugas

Aplikasi. Pembaruan Layar = Benar
Menyetel pembaruan layar ke defaultnya

Akhir Sub
Prosedurnya berakhir di sini

Fungsi SearchAll(ByVal Where As Range, ByVal What, _
Opsional ByVal Setelah Sebagai Varian, _
Opsional ByVal LookIn As XlFindLookIn = xlValues, _
Opsional ByVal LookAt As XlLookAt = xlWhole, _
Opsional ByVal SearchOrder As XlSearchOrder = xlByRows, _
Opsional ByVal SearchDirection As XlSearchDirection = xlNext, _
MatchCase ByVal Opsional Sebagai Boolean = Salah, _
Opsional ByVal SearchFormat As Boolean = False) As Range
Fungsi ini disebut dengan subprosedur

Redupkan Alamat_Pertama Sebagai String
Dim R Sebagai Kisaran
Dim Stck Sebagai Koleksi Baru
Dim Tmp() Sebagai Rentang, Item
Redup m, n Selama
Mendeklarasikan variabel untuk fungsi

Jika Di Mana Tidak Ada Lalu Keluar dari Fungsi
Fungsi berakhir tanpa mengembalikan apapun jika kotak pencarian kosong

Baca Selengkapnya. Cara Membuat Kotak Pencarian di Excel Tanpa VBA (2 Cara Mudah)

Hal-hal untuk diingat

• Anda harus menerapkan pemformatan bersyarat ke data di semua sheet. Dan jangan lupa untuk mengetikkan kata kunci di sel yang tepat yang dirujuk dalam pemformatan bersyarat
• Jangan lupa untuk mencari dengan wildcard untuk mendapatkan semua kemungkinan hasil pencocokan jika Anda tidak memerlukan pencocokan persis
• Anda harus membuat salinan kumpulan data Anda sebelum menerapkan kode. Jika tidak, Anda mungkin kehilangan data Anda
• Anda dapat menyeret tombol Cari untuk memindahkannya sesuai kebutuhan

Kesimpulan

Sekarang Anda tahu cara membuat kotak pencarian untuk beberapa lembar di excel. Apakah Anda dapat menerapkan metode tersebut ke kumpulan data Anda? . Anda juga dapat mengunjungi blog ExcelDemy kami untuk mempelajari lebih lanjut tentang excel. Tetap bersama kami dan terus belajar

Bisakah Anda mencari beberapa lembar Excel sekaligus?

Bagaimana cara mencari data dalam beberapa lembar di Excel?

Bagaimana cara mencari semua lembar di Excel?

Bagaimana cara mencari banyak tab di lembar?