Vlookup adalah fungsi lembar kerja di excel tetapi dapat juga digunakan di VBA, fungsi Vlookup mirip 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 mencari pencocokan tepat atau perkiraan pertandingan. Fungsi lembaran kerja VLOOKUP boleh digunakan dalam pengekodan VBA. Fungsi ini bukan VBA bawaan 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 (lookup_value, table_array, col_index_num, range_lookup)
Bagaimana Menggunakan VLookup dalam Excel VBA?
Berikut adalah beberapa contoh Kod 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.
Andaikan anda mengetahui data ID, 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 vlookup1 ()
Dim student_id As Long
Dim mark As Long
student_id = 11004
Set myrange = Range (“B4: D8”)
markah = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
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:
Seorang 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 mengenai 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.
Mari kita tuliskan kod VBA VLOOKUP.
- Tentukan julat di mana nilai tersebut 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.
Katakan anda menukar nilai sel F4 menjadi "David" di lembaran kerja dan jalankan semula kodnya, dan 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 ke Insert dan klik pada Module 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 dapat digunakan sebagai nombor baris di dalam gelung 'untuk'.
Kemudian masukkan nilai yang akan ditetapkan untuk Cell (baris_nombor, lajur B), yang dapat diberikan sebagai Sel (i, "B"). Nilai, sebagai Sel (bilangan_ baris, lajur D) & "_" & Sel (bilangan_nombor, 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 mengambil nilai (Nama dan Jabatan) dari pengguna. Untuk membuat ini,
- tentukan tiga pemboleh ubah, nama, jabatan, dan look__val sebagai rentetan.
- Ambil input nama dari pengguna. Gunakan kod:
name = InputBox ("Masukkan nama pekerja")
Isi dalam kotak input "Enter the…" akan ditampilkan di kotak prompt ketika anda menjalankan kod. Rentetan yang dimasukkan dalam prompt akan diberikan kepada pemboleh ubah nama.
- Ambil bahagian dari pengguna. Penyelesaiannya 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 look dalam julat B5: E24 mengembalikannya dalam gaji berubah.
Permulaan gaji berubah:
Gaji redup Selagi
Gunakan fungsi Vlookup untuk mencari look__val. Table_array dapat diberikan sebagai Rentang ("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. Katakan anda memberikan nama "Wisnu" dan jabatan "IT", ia akan mengembalikan ralat Masa Jalan '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"
- Di akhir kod (Sebelum Akhir Akhir), tentukan bahawa jika nombor kesalahan 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 vlookup3 ()Untuk i = 4 Ke Sel (Rows.Count, "C"). End (xlUp). Cell Bar
(i, "B"). Nilai = Sel (i, "D"). Nilai & "_ "& Sel (i," E "). Nilai
Nama iDim Seterusnya Sebagai
jabatan String Dim Sebagai String
Dim lookup_val As String
Dim gaji As Longname = InputBox (" Masukkan nama pekerja ")
jabatan = InputBox (" Masukkan jabatan pekerja ")
lookup_val = name &" _ "& department On Error GoTo Message
check:
gaji = Application.WorksheetFunction.VLookup (lookup_val, Range (" B: F "), 5, False)
MsgBox (" Gaji pekerja adalah "& Gaji) Mesej:
Sekiranya Err.Number = 1004 Kemudian
MsgBox (" 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 vBA VBA tidak dapat mencari nilai_pencari, ia akan memberikan ralat 1004.
- Kesalahan dalam fungsi vlookup dapat diatasi menggunakan pernyataan goto jika mengembalikan kesalahan.
