Visual Basic Applications atau disingkat VBA pada Microsoft Excel sangat membantu untuk dapat melakukan otomatisasi pada pekerjaan yang sifatnya banyak dan berulang. Pada artikel kali ini saya akan membagikan Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis. Hanya dengan satu kali klik, anda bisa mencari nominal transaksi berdasarkan nominal dan tanggal yang cocok, mewarnainya dan kemudian mengambil Nomor Referensi Transaksi tersebut, sehingga bisa meyakinkan anda bahwa pembayaran sudah dilakukan oleh pelanggan dengan benar. Jadi anda tidak perlu melakukan crosscek satu per satu secara manual.

Notifikasi "Validasi Selesai" ketika proses validasi selesai dilakukan.

- Pengenalan Konsep Rumus VBA Excel

     Pada dasarnya Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis ini memiliki prinsip yang sama dengan menggunakan rumus Excel VLOOKUP, dimana harus ada rujukan data yang sama pada kedua File Excel.

File Pertama "Contoh Transferan Pelanggan"

     File yang pertama merupakan kumpulan nominal transfer yang dilakukan pelanggan, disini saya beri nama Contoh Transferan Pelanggan.

File kedua "Contoh Mutasi Rekening"

     File yang kedua merupakan kumpulan mutasi rekening dari bank yang digunakan, disini saya beri nama Contoh Mutasi Rekening. Coba perhatikan kolom Referensi Nominal pada file diatas. saya menggunakan rumus Nominal dan Tanggal Transaksi sebagai rujukan data. Terlihat untuk rujukan data "Referensi Nominal" antara file Contoh Transferan Pelanggan dengan Contoh Mutasi Rekening adalah sama.


- Pemasangan Rumus VBA Excel

     Sebelum pemasangan rumus VBA ini, pastikan menu Developer sudah muncul di Microsoft Excel anda. Namun jika menu Developer belum ada, silahkan ikuti tutorial dibawah ini untuk mengaktifkan menu Developer di Microsoft Excel anda.

- Buka Microsoft Excel anda, klik menu File yang ada di pojok kiri atas. Kemudian klik Options.
- Klik menu Costumize Ribbon dan centang kolom Developer kemudian klik OK.

     Menu Developer sudah siap dan anda siap menggunakan rumus ini. Untuk pemasangan rumus VBA Macro Excel nya akan kita pasang pada file Excel Contoh Mutasi Rekening. Dan berikut ini adalah Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis.Sub Unit1() Dim wsMutasi As Worksheet Dim wsTransfer As Worksheet Dim lastRowMutasi As Long Dim lastRowTransfer As Long Dim rngMutasi As Range Dim rngTransfer As Range Dim cell As Range Dim transferNominal As Double Dim foundRange As Range ' Set workbook objects Set wbMutasi = Workbooks("Contoh Mutasi Rekening.xls") ' Ganti Contoh Mutasi Rekening.xls Dengan Nama File Mutasi Rekening Kamu Set wbTransfer = Workbooks("Contoh Transferan Pelanggan.xls") ' Ganti Contoh Transferan Pelanggan.xls Dengan file Transfer Dari Pelanggan ' Set worksheet objects Set wsMutasi = wbMutasi.Sheets("MUTASI REKENING 2023") ' Ganti "MUTASI REKENING 2023" Dengan Nama Sheet File Mutasi Rekening Kamu Set wsTransfer = wbTransfer.Sheets("PELANGGAN UNIT 1") ' Ganti "PELANGGAN UNIT 1" Dengan Nama Sheet file Transfer Dari Pelanggan ' Find last row in Mutasi sheet lastRowMutasi = wsMutasi.Cells(wsMutasi.Rows.Count, "A").End(xlUp).Row ' Find last row in Transfer sheet lastRowTransfer = wsTransfer.Cells(wsTransfer.Rows.Count, "A").End(xlUp).Row ' Set ranges to search Set rngMutasi = wsMutasi.Range("A2:A" & lastRowMutasi) ' assuming data starts from row 2 in Mutasi sheet and data is in column A and B Set rngTransfer = wsTransfer.Range("A2:A" & lastRowTransfer) ' assuming data starts from row 2 in Transfer sheet and data is in column A and B ' Loop through each cell in Transfer sheet For Each cell In rngTransfer ' Get transfer nominal from Transfer sheet transferNominal = cell.Value ' Try to find the transfer nominal in Mutasi sheet Set foundRange = rngMutasi.Find(What:=transferNominal, LookIn:=xlValues, LookAt:=xlWhole) ' If not found, search for transfer nominal - 1 in Mutasi sheet If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 1, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 1, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 2, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 2, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 3, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 3, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 4, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 4, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 5, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 5, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 6, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 6, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal - 7, LookIn:=xlValues, LookAt:=xlWhole) End If If foundRange Is Nothing Then Set foundRange = rngMutasi.Find(What:=transferNominal + 7, LookIn:=xlValues, LookAt:=xlWhole) End If ' If found, color the cell in Transfer sheet with green and copy the reference number from Mutasi sheet If Not foundRange Is Nothing Then wsMutasi.Cells(foundRange.Row, foundRange.Column + 3).Value = "Unit 1" wsMutasi.Rows(foundRange.Row).Interior.Color = RGB(239, 150, 95) ' set color cell.Interior.Color = RGB(239, 150, 95) ' set color ' Copy the reference number from Mutasi sheet to Transfer sheet wsMutasi.Cells(foundRange.Row, foundRange.Column + 1).Copy Destination:=cell.Offset(0, 7) End If Next cell ' Menampilkan pesan peringatan validasi selesai MsgBox "Validasi Selesai!", vbInformation End Sub Salin Kode VBA

- Penjelasan Bagian Rumus

     - Sub Unit1(), berada pada bagian paling atas berfungsi sebagai judul dari rumus VBA yang kita buat dan dapat kita sesuaikan menjadi Sub ValidasiRekening() misalnya.

     - 'Set workbook objects, merupakan bagian yang mengatur file excel mana saja yang akan digunakan untuk mencari dan mencocokkan data. Disini saya menggunakan 2 file excel yang berbeda.
     Set wbMutasi = Workbooks("Contoh Mutasi Rekening.xls")
     Set wbTransfer = Workbooks("Contoh Transferan Pelanggan.xls")
Bagian yang berwarna merah bisa kita sesuaikan dengan nama file excel yang kita gunakan.

     -'Set worksheet objects, jika rumus diatas digunakan untuk mengatur file excel mana saja, maka rumus kali ini untuk menunjuk sheet excel mana saja yang akan digunakan.
     Set wsMutasi = wbMutasi.Sheets("MUTASI REKENING 2023")
     Set wsTransfer = wbTransfer.Sheets("PELANGGAN UNIT 1").
Bagian yang berwarna merah bisa kita sesuaikan dengan nama Sheet Excel yang akan kita gunakan.

     - Set foundRange = rngMutasi.Find(What:=transferNominal - 1, LookIn:=xlValues, LookAt:=xlWhole) End If, rumus ini memberikan toleransi perbedaan tanggal antara file 1 dengan yang lainnya. -1 artinya jika transfer pelanggan dilakukan pada tanggal 03 Maret 2023, tetapi masuk di mutasi rekening tanggal 04 Maret 2023. maka rumus ini akan menandai transaksi tersebut, begitu seterusnya sampai H-7 atau H+7. Untuk hasil yang lebih akurat, hanya gunakan rumus diatas dan Set foundRange = rngMutasi.Find(What:=transferNominal + 1, LookIn:=xlValues, LookAt:=xlWhole) End If sisanya bisa dihapus untuk mengurangi toleransi perbedaan tanggal transaksi.

     - wsMutasi.Cells(foundRange.Row, foundRange.Column + 3).Value = "Unit 1", maksud dari rumus ini ketika ditemukan data yang cocok, maka akan menambahkan kata Unit 1 pada file excel Mutasi Rekening tepat di kolom D disamping baris data yang ditemukan. Mengubah + 3 menjadi + 4 akan mengubah posisi penulisan Unit 1 yang semula berada di kolom D menjadi di kolom E, begitu juga seterusnya. Sesuaikan dengan kebutuhan kita.

     -wsMutasi.Rows(foundRange.Row).Interior.Color = RGB(239, 150, 95) ' set color cell.Interior.Color = RGB(239, 150, 95) ' set color, merupakan rumus untuk mewarnai data yang cocok pada file Transfer Pelanggan dan pada file Mutasi Rekening. Sesuaikan warna yang anda inginkan dengan melihat tabel warna seperti dibawah ini.

Memasukkan angka RGB(255, 255, 255) akan menghasilkan warna Putih.

     - wsMutasi.Cells(foundRange.Row, foundRange.Column + 1).Copy Destination:=cell.Offset(0, 7). Maksud dari rumus ini, ketika ditemukan data yang cocok maka akan mengambil Nomor Referensi Transfer yang ada pada Mutasi Rekening, lalu menempelkan Nomor Referensi Tersebut ke file excel Transfer Pelanggan.


- Review Kinerja Rumus

     Silahkan tonton video dibawah ini untuk melihat bagaimana Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis bekerja dalam kondisi tanpa kendala.




     Rumus ini masih jauh dari kata sempurna. Masih terdapat penyesuaian penyesuaian yang harus dilakukan. Terbukti dari video dibawah ini.



ketika ada nominal yang sama dalam range tanggal yang sama maka hanya akan mengambil 1 referensi nominal untuk dipakai sebagai validasinya. Penggunaan Referensi Transfer disini tidak boleh lebih dari satu kali. Jika terdapat lebih dari satu penggunaan Referensi Transfer maka kita harus crosscek secara manual. Namun hal ini masih lebih sedikit kasusnya dibanding kita mengecek satu per satu transaksi yang dilakukan.

     Dibawah ini saya menyediakan file Excelnya sebagai contoh untuk memudahkan pemahaman para pembaca sekalian. Silahkan klik link dibawah ini untuk mendownload filenya secara GRATIS.!!

Download file Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis.

     Apabila ada hal yang ingin ditanyakan atau mungkin untuk penyempurnaan Rumus VBA ini, silahkan disampaikan melalui kolom komentar yang tersedia dibawah postingan ini. Terima kasih :)