Vlookup adalah fungsi lembar kerja di excel tetapi juga dapat digunakan di VBA, fungsi Vlookup mirip dengan fungsionalitas di VBA dan di lembar kerja keduanya, karena ini adalah fungsi lembar kerja, metode untuk menggunakan Vlookup di VBA adalah melalui Aplikasi. metode dan argumennya tetap sama.
Fungsi VLOOKUP di Excel VBA
Fungsi VLOOKUP di Excel digunakan untuk mencari nilai dalam array dan mengembalikan nilai yang sesuai dari kolom lain. Nilai yang akan dicari harus ada di kolom pertama. Juga harus disebutkan apakah akan mencari kecocokan tepat atau perkiraan kecocokan. Fungsi lembar kerja VLOOKUP dapat digunakan dalam pengkodean VBA. Fungsi ini tidak dibangun di VBA dan karenanya hanya dapat dipanggil menggunakan lembar kerja.
Fungsi VLOOKUP di Excel memiliki sintaks berikut ini:
Di mana, lookup_value adalah nilai yang dicari, table_arrray adalah tabel, col_index_num adalah nomor kolom dari nilai yang dikembalikan, range_lookup menandakan apakah kecocokan tepat atau perkiraan. range_lookup bisa TRUE / FALSE atau 0/1.
Dalam kode VBA, fungsi VLOOKUP dapat digunakan sebagai:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Bagaimana Cara Menggunakan VLookup di Excel VBA?
Berikut adalah beberapa contoh Kode VLookup di Excel VBA.
Anda dapat mengunduh Template VLookup ini di Excel VBA di sini - VLookup di Template VBA Excel
Kode VLookup di Excel VBA Contoh # 1
Mari kita lihat bagaimana kita dapat memanggil fungsi lembar kerja VLOOKUP di Excel VBA.
Misalkan Anda memiliki data ID siswa, nama dan nilai rata-rata yang diperoleh mereka.
Sekarang, Anda ingin mencari nilai yang diperoleh siswa, dengan ID 11004.
Untuk mencari nilai, ikuti langkah-langkah berikut:
- Buka Tab Pengembang dan klik Visual Basic.
- Di bawah jendela VBA, buka Sisipkan dan klik Modul.
- Sekarang, tulis kode VBA VLOOKUP. Kode VBA VLOOKUP berikut dapat digunakan.
Sub vlookup1 ()
Redupkan student_id As Long
Tanda redup As Long
student_id = 11004
Set myrange = Range (“B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Akhiri Sub
Pertama, tentukan id siswa, yang merupakan nilai yang akan dicari. Oleh karena itu, kami mendefinisikan,
student_id = 11004
Selanjutnya, kami menentukan rentang di mana nilai dan nilai yang dikembalikan ada. Karena data kita ada di sel B4: D8, kita mendefinisikan range- myrange sebagai:
Set myrange = Range (“B4: D8”)
Terakhir, kami memasukkan fungsi VLOOKUP menggunakan fungsi Lembar Kerja dalam variabel, ditandai sebagai:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Untuk mencetak tanda di kotak pesan, mari kita gunakan perintah berikut:
MsgBox “Mahasiswa dengan ID:” & student_id & ”diperoleh” & nilai & ”nilai”
Ini akan mengembalikan:
Siswa dengan ID: 11004 memperoleh 85 nilai.
Sekarang, klik tombol jalankan.
Anda akan melihat bahwa kotak pesan akan muncul di lembar Excel.
Kode VLookup di Excel VBA Contoh # 2
Misalkan Anda memiliki data nama karyawan dan gajinya. Data ini diberi kolom B dan C. Sekarang, Anda perlu menulis kode VBA VLOOKUP sedemikian rupa sehingga diberi nama karyawan di sel, F4, gaji karyawan akan dikembalikan di sel G4.
Mari kita tulis kode VBA VLOOKUP.
- Tentukan kisaran di mana nilai-nilai tersebut ada, yaitu kolom B dan C.
Set myrange = Range (“B: C”)
- Tentukan nama karyawan dan masukkan nama dari sel F4.
Tetapkan nama = Rentang ("F4")
- Tentukan gaji sebagai sel G4.
Tetapkan gaji = Rentang ("G4")
- Sekarang, panggil fungsi VLOOKUP menggunakan WorksheetFunction di VBA dan masukkan dalam gaji.Value. Ini akan mengembalikan nilai (keluaran dari fungsi Vlookup) di sel G4. Sintaks berikut dapat digunakan:
gaji.Value = Application.WorksheetFunction.VLookup (nama, myrange, 2, False)
- Sekarang, jalankan modulnya. Sel G4 akan berisi gaji karyawan setelah Anda menjalankan kode VBA VLOOKUP.
Misalkan Anda mengubah nilai sel F4 menjadi "David" di lembar kerja dan menjalankan kembali kode tersebut, maka gaji David akan dikembalikan.
Kode VLookup di Excel VBA Contoh # 3
Misalkan Anda memiliki data karyawan perusahaan Anda, memiliki ID, nama, Departemen, dan gaji mereka. Menggunakan Vlookup di VBA, Anda ingin mendapatkan detail gaji karyawan menggunakan nama dan departemennya.
Karena fungsi vlookup di excel mencari lookup_value hanya dalam satu kolom, yang merupakan kolom pertama dari table_array, Anda harus terlebih dahulu membuat kolom yang berisi "Name" dan "Department" dari setiap karyawan.
Di VBA, mari kita masukkan nilai "Nama" dan "Departemen" di kolom B lembar kerja.
Untuk melakukan ini, buka tab Pengembang dan klik Visual Basic. Lalu pergi untuk menyisipkan dan klik Modul untuk memulai modul baru.
Sekarang mari kita tulis kode, sehingga kolom B berisi nilai dari kolom D (nama) dan kolom E.
Sintaksnya diberikan sebagai:
Pertama, gunakan loop 'for' dari i = 4 karena nilainya dimulai dari baris ke-4 dalam kasus ini. Pengulangan akan berlanjut hingga akhir baris terakhir kolom C. Jadi, variabel i dapat digunakan sebagai nomor baris di dalam pengulangan 'for'.
Kemudian masukkan nilai yang akan ditetapkan untuk Cell (row_number, kolom B), yang dapat diberikan sebagai Cells (i, "B"). Value, sebagai Cell (row_number, kolom D) & "_" & Cell (row_number, kolom E ).
Misalkan Anda ingin menetapkan sel B5 = D5 & "_" & E5, Anda cukup menggunakan kode sebagai:
Sel (5, "B"). Nilai = Sel (5, "D"). Nilai & "_" & Sel (5, "E"). Nilai
Sekarang, mari kita cari nilai pencarian di array B5: E24, Anda harus memasukkan nilai pencarian terlebih dahulu. Mari kita ambil nilai (Nama dan Departemen) dari pengguna. Untuk melakukan ini,
- tentukan tiga variabel, nama, departemen dan lookup_val sebagai string.
- Ambil masukan nama dari pengguna. Gunakan kode:
name = InputBox ("Masukkan nama karyawan")
Konten di kotak input "Enter the .." akan ditampilkan di kotak prompt saat Anda menjalankan kode. String yang dimasukkan di prompt akan ditetapkan ke variabel nama.
- Ambil departemen dari pengguna. Bisa dilakukan dengan cara yang sama seperti di atas.
department = InputBox ("Masuk ke departemen karyawan")
- Tetapkan nama dan departemen dengan "_" sebagai pemisah ke variabel lookup_val menggunakan sintaks berikut:
lookup_val = nama & “_” & departemen
- Tulis sintaks vlookup untuk mencari lookup_val dalam rentang B5: E24 mengembalikannya dengan gaji variabel.
Inisialisasi gaji variabel:
Redupkan gaji Selama
Gunakan fungsi Vlookup untuk menemukan lookup_val. Tabel_array dapat diberikan sebagai Range ("B: F") dan gaji ada di kolom ke-5. Dengan demikian, sintaks berikut dapat digunakan:
gaji = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- Untuk mencetak gaji di kotak pesan, gunakan sintaks:
MsgBox (Gaji karyawan adalah ”& gaji)
Sekarang, jalankan kodenya. Kotak prompt akan muncul di lembar kerja tempat Anda dapat memasukkan nama. Setelah Anda memasukkan nama (Say Sashi) dan klik OK.
Ini akan membuka kotak lain di mana Anda dapat memasuki departemen. Setelah Anda memasuki departemen, ucapkan IT.
Ini akan mencetak gaji karyawan.
Jika Vlookup dapat menemukan karyawan dengan nama dan departemennya, itu akan memberikan kesalahan. Misalkan Anda memberi nama "Wisnu" dan departemen "IT", itu akan mengembalikan kesalahan Run-time '1004'.
Untuk mengatasi masalah ini, Anda dapat menentukan dalam kode, bahwa pada jenis kesalahan ini, cetak "Nilai tidak ditemukan" sebagai gantinya. Untuk melakukan ini,
- Sebelum menggunakan sintaks vlookup, gunakan kode berikut-
Pada Pesan Kesalahan GoTo
Memeriksa:
Kode akhir (dari Check :) akan dipantau, dan jika menerima kesalahan apapun, itu akan pergi ke pernyataan "pesan"
- Di akhir kode (Sebelum Sub Akhir), tentukan jika nomor kesalahan adalah 1004, lalu cetak di kotak pesan "Data karyawan tidak ada". Ini dapat dilakukan menggunakan sintaks:
Pesan:
Jika Err.Number = 1004 Kemudian
MsgBox ("Data karyawan tidak ada")
Berakhir jika
Modul 1:
Sub vlookup3 ()Untuk i = 4 Ke Sel (Rows.Count, "C"). End (xlUp) .Row
Sel (i, "B"). Nilai = Sel (i, "D"). Nilai & "_" & Sel (i, "E"). Nilai
Selanjutnya nama iDim As String
Departemen redup As String
Redupkan lookup_val Sebagai String
Gaji redup As Longname = InputBox ("Masukkan nama karyawan")
department = InputBox ("Masukkan departemen karyawan")
lookup_val = nama & “_” & departmentOn Error GoTo Message
memeriksa:
gaji = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
MsgBox (“Gaji karyawan adalah” & gaji) Pesan:
Jika Err.Number = 1004 Kemudian
MsgBox ("Data karyawan tidak ada")
Akhiri IfEnd Sub
Hal yang Perlu Diingat Tentang VLookup di Excel VBA
- Fungsi Vlookup bisa dipanggil di Excel VBA dengan menggunakan WorksheetFunction.
- Sintaks fungsi vlookup tetap sama di Excel VBA.
- Ketika kode vlookup VBA tidak dapat menemukan lookup_value, itu akan memberikan kesalahan 1004.
- Kesalahan dalam fungsi vlookup dapat dikelola menggunakan pernyataan goto jika mengembalikan kesalahan.
