Tulisan ini berisi sharing penggunaan advance filter yang sering saya gunakan untuk mencari record dari sebuah tabel database yang disimpan dalam format workbook atau microsoft excel. Agar tabel bisa diolah dengan baik oleh advance filter, silahkan dibaca ulasan dari Mr. Kid tentang bagaimana cara menyusun data sebagai tabel yang baik.
Selain untuk memudahkan dalam penggunaan, VBA atau macro untuk AdvancedFilter dimaksudkan agar hasil filter bisa ditampilkan dalam workbook dan sheet di luar tabel database. Jika tidak menggunakan VBA, secara default hasil advance filter harus ditampilkan dalam sheet yang sama dengan sheet tabel database atau dalam sheet yang aktif. Pesan di bawah akan muncul apabila kita mencoba untuk menampilkan hasil filter di sheet yang lain.
Pesan di atas tidak muncul apabila proses Advance Filter dilaksanakan melalui VBA atau macro dengan menggunakan method AdvancedFilter. Untuk mengetahui cara penggunaan dari method tersebut, tekan tombol F2 atau Object Browser saat di Microsoft Visual Basic Editor (VBE). Masukkan keyword atau kata kunci AdvancedFilter, kemudian click icon teropong.
Click atau pilih AdvancedFilter, kemudian tekan F1 untuk melihat keterangan detail dari method yang dipilih.
Dari keterangan object browser di atas bisa dijelaskan sebagai berikut:
Fungsi AdvancedFilter bekerja di object Excel.Range atau object Range dengan menggunakan parameter berupa Action, [CriteriaRange], [CopyToRange],[Unique].
Parameter Action harus ada sedangkan [CriteriaRange], [CopyToRange],[Unique] bersifat optional atau pilihan. Ada dua pilihan untuk action yaitu xlFilterCopy dan xlFilterInPlace. xlFilterCopy dipilih untuk proses advance filter ini karena hasil filter akan ditampilkan di tempat lain bukan ditempat table yang sama (xlFilterInPlace).
Dalam contoh ini saya menggunakan dua buah file workbook yaitu workbook yang berisi tabel database dan workbook yang berisi macro atau vba. File database saya beri nama isbn.xlsx berisi tabel nomer isbn dan penerbitnya, sedangkan file isbn.xlsm berisi macro atau vba untuk menampilkan hasil lookup record. Kedua file tersebut harus diletakkan dalam folder yang sama.
Tabel database di file isbn.xlsx disimpan dalam sheet [Publisher] tersusun dari 4 (empat) header/field yaitu,
- [Publisher code]
- [Publisher]
- [Notes]
- [strKey]
Field [strKey] adalah header dummy hasil gabungan dari 3 (tiga) header sebelumnya. Header dummy ini dibuat secara otomatis dengan macro/vba saat file pertama kali dibuka atau melalui event Workbook_Open().
Maksud dari penggabungan ini adalah untuk membuat header bantuan sehingga jika kita memasukkan sembarang keyword atau kata kunci dalam satu cell, bisa didapatkan record antar header.
Dalam file isbn.xlsm terdapat sheet [LookUpPublisher] dengan tampilan sebagai berikut:
Range [B1] adalah tempat untuk memasukkan keyword atau kata kunci. Jika ingin mencari [publisher code] dengan kode yang diawali dengan 870, maka di [B1] dimasukkan kata kunci 870*. Record hasil pencarian akan ditampilkan mulai baris ke 3.
Jadi dengan memasukkan satu kata kunci di [B1], program akan mencari kata kunci tersebut di header [Publisher Code], [Publisher] dan [Notes].
Untuk mengetahui alur program dan code vba-nya download file berikut.
Love it... muantabs dah..
BalasHapusUlasan mas Zain ini bisa sekalian jadi contoh penerapan materi BelajarVBA dari 000 sampai yang sudah ada
Sekalian ditambah pengenalan penggunaan methods AdvancedFilter
Om Zain...
Terus bikin ulasan ya... eh disempetin ding.
Biar yang lainnya juga tertarik ikut sumbang yang biasa dikerjakan.
Kalo boleh, ulasan berikut yang bisa multi kriteria ya...
Seperti filter tanggal sekian sampai tanggal sekian, yang kolom lainnya berisi A, B, atau C gitu...
:)
Mas artikelnya sangat membantu, oya mas sy mau tanya, seandainya di sheet1(database) dengan isian a1=no urut, b1=nama, c1=alamat, d1=kota..
BalasHapusDi sheet 2(output) dibuat semacam commandbutton(proses) dengan kriteria yg di inginkan, misal kita menginginkan hasil output data yg keluar di sheet output hanya yang kota A saja, dengan menginput di sheet2(output) pada cells a1 dan klik commandbutton(proses) maka dari kolom c1 ke kanan muncul dat kota hasil filter sheet1(databse)... Gmn ya mas... Atas bantuannya terimakasij
Coba file yang ada dilink berikut :
Hapushttps://app.box.com/s/0pen6vquw0melo00p637
terima kasih mas sy dah liat dan coba2, tapi saya kesulitan mas, jadi seandainya kriteria yang diinginkan hanya satu saja (field input untuk output hanya 1 saja) setelah tekan proses (command button), dapat mewakili baris yang ada di database tersebut.
BalasHapusmisal:
NO. NAMA. KOTA.
1 DADANG JAKARTA
2 UDIN BANDUNG
3 SITI JAKARTA
4 BOLANG BANDUNG
5 IRMA JAKARTA
ada tombol proses dan input, input di sini kita bebas memilih mau NAMA atau KOTA, jadi seandainya kota yang kita input, dan kita klik proses yang muncul,
misal, yang dipilih (input JAKARTA) klik Proses (command button) otomatis outputnya sbb
NO NAMA KOTA
1 DADANG JAKARTA
2 SITI JAKARTA
3 IRMA JAKARTA
begitu sebaliknya jika kita memilih berdasarkan nama, maaf ya mas nyusahin,
terimakasih.
Pada prosedur myAdvFilter, non aktifkan baris :
Hapus1. GenerateKriteria
2. Sheet1.Protect userinterfaceonly:=True
Kemudian ganti isi tabel data (sisi kiri) dan isi sendiri di tabel kriteria, mulai dari nama kolom yang akan menjadi kriteria fileter beserta nilai-nilai item kriterianya.
Lalu pilih juga nama-nama kolom output di set nama kolom kanan yang kuning (atas).
Pencet GO.
Jika akan dikembangkan lebih lanjut juga memungkinkan, karena script-nya sudah ada disana.
mas numpang tanya bagaimana membuat form pencari dan edit data beserta fungsi nya
BalasHapusternyata sudah banyak yang komen. mohon maaf karena kesibukan jarang berkunjung ke blog ini.
BalasHapusPertanyaan Mas Agung:
pencari dan edit dengan form tentu saja bisa salah satu cara yang aku pakai adalah:
1. Pada form, buat texbox yang nantinya berisi kata kunci untuk mencari data. dan listbox yang menampung hasil filter
2. Lakukan proses advance filter (tentunya dengan vba) sesuai dengan textbox.value dan hasil disimpan di sheet berbeda. misalnya didapat di sheet1!$A2$C$2
3. dengan vba atur property listbox.rowsource=sheet1!$A2$C$2
demikian gambaran singkatnya. maaf belum sempat membuat vba dalam bentuk jadi...
BUAT VBA dalam bentuk jadi dong mas biar mudah dipahami, , sya jga pgen tuh buat pencarian database dikantor
BalasHapusSaya menambahkan permintaan Anonim.
BalasHapusSheet1 misalkan untuk data lengkap, yaitu:
NO. NAMA. KOTA.
1 DADANG JAKARTA
2 UDIN BANDUNG
3 SITI JAKARTA
4 BOLANG BANDUNG
5 IRMA JAKARTA
untuk Sheet2, misalkan mengetik JAKARTA, maka hasil pemilihannya sebagai berikut.
NO NAMA KOTA
1 DADANG JAKARTA
2 SITI JAKARTA
3 IRMA JAKARTA
Mohon dibuatkan saja yang seperti itu, karena saya bingung membuka kode vba_advanced_filter.xlsm, dan mana yang harus diubah.
Terima kasih atas bantuannya.
Dalam contoh ISBN terdapat 2 file workbook, bagaimana kalau jadi satu workbook saja. Apakah bisa...?
BalasHapusTerima Kasih
maaf mas mau tanya, kalo databasenya ngambil dari sheet yg lain yg masih dlm satu file caranya gimana ya? makasih
BalasHapus