Vlookup adalah fungsi lembar kerja di excel tetapi dapat juga digunakan di VBA, fungsi Vlookup serupa dengan fungsi di VBA dan di lembar kerja keduanya, kerana fungsi lembaran kerja kaedah untuk menggunakan Vlookup di VBA adalah melalui Application.WorksheetFunction kaedah dan hujah tetap sama.
Fungsi VLOOKUP dalam Excel VBA
Fungsi VLOOKUP di Excel digunakan untuk mencari nilai dalam array dan mengembalikan nilai yang sesuai dari lajur lain. Nilai yang hendak dicari mesti ada di lajur pertama. Perlu juga disebutkan apakah akan mencari pencocokan tepat atau perkiraan pertandingan. Fungsi lembaran kerja VLOOKUP boleh digunakan dalam pengekodan VBA. Fungsi tidak dibina dalam VBA dan dengan itu hanya dapat memanggil menggunakan lembaran kerja.
Fungsi VLOOKUP di Excel mempunyai sintaks berikut:
Di mana, look__alue adalah nilai yang harus dicari, table_arrray adalah tabel, col_index_num adalah bilangan lajur dari nilai pengembalian, range_lookup menandakan jika pertandingan itu tepat atau hampir. range_lookup boleh BENAR / SALAH atau 0/1.
Dalam kod VBA, fungsi VLOOKUP dapat digunakan sebagai:
Application.WorksheetFunction.vlookup (look__alue, table_array, col_index_num, range_lookup)
Bagaimana Menggunakan VLookup dalam Excel VBA?
Berikut adalah beberapa contoh Kod VLookup dalam Excel VBA.
Anda boleh memuat turun Templat VLookup dalam Excel VBA ini di sini - Templat VLookup dalam Excel VBA
Kod VLookup dalam Excel VBA Contoh # 1
Mari kita lihat bagaimana kita boleh memanggil fungsi lembaran kerja VLOOKUP di Excel VBA.
Katakan anda mempunyai data ID pelajar, nama dan markah purata yang diperoleh oleh mereka.
Sekarang, anda ingin mencari markah yang diperoleh oleh pelajar, dengan ID 11004.
Untuk mencari nilainya, ikuti langkah berikut:
- Pergi ke Tab Pembangun dan klik pada Visual Basic.
- Di bawah tetingkap VBA, pergi ke Insert dan klik pada Module.
- Sekarang, tulis kod VBA VLOOKUP. Kod VBA VLOOKUP berikut boleh digunakan.
Sub pandangan 1 ()
Redup student_id Selama
Dim redam Sepanjang Masa
pelajar_id = 11004
Tetapkan myrange = Range ("B4: D8")
markah = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Sub Akhir
Pertama, tentukan id pelajar, yang merupakan nilai untuk mencari. Oleh itu, kami menentukan,
pelajar_id = 11004
Seterusnya, kami menentukan julat di mana nilai dan nilai kembali ada. Oleh kerana data kami terdapat di sel B4: D8, kami menentukan range-myrange sebagai:
Tetapkan myrange = Range ("B4: D8")
Akhirnya, kami memasukkan fungsi VLOOKUP menggunakan fungsi Lembaran Kerja dalam pemboleh ubah, menandakan sebagai:
markah = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Untuk mencetak tanda dalam kotak mesej, marilah kita menggunakan perintah berikut:
MsgBox "Pelajar dengan ID:" & student_id & "diperoleh" & tanda & "markah"
Ia akan kembali:
Pelajar dengan ID: 11004 memperoleh 85 markah.
Sekarang, klik butang jalankan.
Anda akan melihat bahawa kotak mesej akan muncul di helaian Excel.
Kod VLookup dalam Excel VBA Contoh # 2
Katakan anda mempunyai data nama pekerja dan gaji mereka. Data ini diberi lajur B dan C. Sekarang, anda perlu menulis kod VBA VLOOKUP sehingga dengan memberikan nama pekerja di sel, F4, gaji pekerja akan dikembalikan di sel G4.
Marilah kita menulis kod VBA VLOOKUP.
- Tentukan julat di mana nilai-nilai itu ada, iaitu, lajur B dan C.
Tetapkan myrange = Range ("B: C")
- Tentukan nama pekerja dan masukkan nama dari sel F4.
Tetapkan nama = Julat ("F4")
- Tentukan gaji sebagai sel G4.
Tetapkan gaji = Julat ("G4")
- Sekarang, panggil fungsi VLOOKUP menggunakan WorksheetFunction di VBA dan masukkan gaji. Nilai. Ini akan mengembalikan nilai (output fungsi Vlookup) di sel G4. Sintaks berikut boleh digunakan:
gaji.Value = Application.WorksheetFunction.VLookup (nama, myrange, 2, False)
- Sekarang, jalankan modul. Sel G4 akan mengandungi gaji pekerja setelah anda menjalankan kod VBA VLOOKUP.
Andaikan anda menukar nilai sel F4 menjadi "David" di lembaran kerja dan menjalankan kembali kodnya, ia akan mengembalikan gaji David.
Kod VLookup dalam Excel VBA Contoh # 3
Katakan anda mempunyai data pekerja syarikat anda, yang mempunyai ID, nama, Jabatan, dan gaji mereka. Dengan menggunakan Vlookup di VBA, anda ingin mendapatkan maklumat gaji pekerja menggunakan nama dan jabatannya.
Oleh kerana fungsi vlookup dalam excel mencari lookup_value hanya dalam satu lajur, yang merupakan lajur pertama dari table_array, anda diharuskan terlebih dahulu membuat lajur yang mengandungi "Nama" dan "Jabatan" setiap pekerja.
Di VBA, mari kita masukkan nilai "Nama" dan "Jabatan" di lajur B lembaran kerja.
Untuk melakukan ini, pergi ke tab Pembangun dan klik Visual Basic. Kemudian pergi untuk memasukkan dan klik pada Modul untuk memulakan modul baru.
Marilah kita menulis kod, supaya ruangan B mengandungi nilai lajur D (nama) dan lajur E.
Sintaks diberikan sebagai:
Pertama, gunakan gelung 'untuk' dari i = 4 kerana nilai bermula dari baris ke-4 dalam kes ini. Gelung akan berterusan sehingga akhir baris terakhir lajur C. Jadi, pemboleh ubah i boleh digunakan sebagai nombor baris di dalam gelung 'untuk'.
Kemudian masukkan nilai yang akan ditetapkan untuk Sel (baris_nombor, lajur B), yang dapat diberikan sebagai Sel (i, “B”). Nilai, sebagai Sel (bilangan_ baris, lajur D) & “_” & Sel (nombor_ baris, lajur E ).
Andaikan anda ingin menetapkan sel B5 = D5 & "_" & E5, anda hanya boleh menggunakan kod sebagai:
Sel (5, "B"). Nilai = Sel (5, "D"). Nilai & "_" & Sel (5, "E"). Nilai
Sekarang, mari cari nilai carian dalam array B5: E24, anda perlu memasukkan nilai carian terlebih dahulu. Mari kita ambil nilai (Nama dan Jabatan) dari pengguna. Untuk membuat ini,
- tentukan tiga pemboleh ubah, nama, bahagian dan lookup_val sebagai rentetan.
- Ambil input nama dari pengguna. Gunakan kod:
name = InputBox ("Masukkan nama pekerja")
Isi dalam kotak input "Masukkan .." akan ditampilkan di kotak prompt ketika anda menjalankan kod. Rentetan yang dimasukkan dalam prompt akan diberikan kepada pemboleh ubah nama.
- Ambil bahagian dari pengguna. Perkara tersebut boleh dilakukan seperti di atas.
department = InputBox ("Masukkan jabatan pekerja")
- Tetapkan nama dan jabatan dengan "_" sebagai pemisah ke variabel lookup_val menggunakan sintaks berikut:
lookup_val = nama & "_" & jabatan
- Tuliskan sintaks vlookup untuk mencari_volume_up look dalam julat B5: E24 mengembalikannya dalam gaji berubah.
Permulaan gaji berubah:
Gaji redup Selama
Gunakan fungsi Vlookup untuk mencari lookup_val. Table_array dapat diberikan sebagai Julat ("B: F") dan gaji hadir di ruangan ke-5. Sintaks berikut boleh digunakan:
gaji = Application.WorksheetFunction.VLookup (look__val, Range ("B: F"), 5, False)
- Untuk mencetak gaji dalam kotak mesej, gunakan sintaks:
Kotak Mesej (Gaji pekerja adalah ”& gaji)
Sekarang, jalankan kodnya. Kotak prompt akan muncul di lembaran kerja di mana anda boleh memasukkan namanya. Selepas anda memasukkan nama (Say Sashi) dan klik OK.
Ia akan membuka kotak lain di mana anda boleh memasuki jabatan. Setelah anda memasuki jabatan, sebut IT.
Ia akan mencetak gaji pekerja.
Sekiranya Vlookup dapat menjumpai mana-mana pekerja dengan nama dan jabatan, ia akan memberikan kesalahan. Andaikan anda memberikan nama "Wisnu" dan jabatan "IT", ia akan mengembalikan ralat Run-time '1004'.
Untuk mengatasi masalah ini, Anda dapat menentukan dalam kod, bahawa pada jenis kesalahan ini, cetak "Nilai tidak dijumpai" sebagai gantinya. Untuk membuat ini,
- Sebelum menggunakan sintaks vlookup, gunakan kod berikut-
Pada Ralat Mesej GoTo
Periksa:
Kod akhir (dari Cek :) akan dipantau, dan jika menerima kesalahan, ia akan pergi ke pernyataan "pesan"
- Pada akhir kod (Sebelum Akhir Akhir), tentukan bahawa jika nombor ralat adalah 1004, kemudian cetak di kotak pesan "Data pekerja tidak ada". Ini boleh dilakukan dengan menggunakan sintaks:
Mesej:
Sekiranya Err.Number = 1004 Maka
Kotak Mesej ("Data pekerja tidak hadir")
Tamat Sekiranya
Modul 1:
Sub pandangan 3 ()Untuk i = 4 Ke Sel (Rows.Count, "C"). End (xlUp). Baris
Sel (i, "B"). Nilai = Sel (i, "D"). Nilai & "_" & Sel (i, "E"). Nilai
Nama iDim seterusnya Sebagai Rentetan
Dim jabatan sebagai tali
Redup look_val Sebagai Rentetan
Gaji redup Sebagai Longname = InputBox ("Masukkan nama pekerja")
department = InputBox ("Masukkan jabatan pekerja")
lookup_val = name & “_” & departmentPada Ralat Pergi ke Mesej
periksa:
gaji = Application.WorksheetFunction.VLookup (look__val, Range ("B: F"), 5, False)
MsgBox ("Gaji pekerja adalah" & gaji) Mesej:
Sekiranya Err.Number = 1004 Maka
Kotak Mesej ("Data pekerja tidak hadir")
Tamat Sub IfEnd
Perkara yang Perlu Diingat Tentang VLookup di Excel VBA
- Fungsi Vlookup boleh dipanggil dalam Excel VBA dengan menggunakan WorksheetFunction.
- Sintaks fungsi vlookup tetap sama di Excel VBA.
- Apabila kod vBAupup VBA tidak dapat mencari nilai_pencari, ia akan memberikan ralat 1004
- Kesalahan dalam fungsi vlookup dapat diatasi menggunakan pernyataan goto jika mengembalikan kesalahan.
