Deafile Bedah Blog: Rumus VBA Macro Excel

SO atau Stock Opname secara garis besar yaitu menghitung jumlah fisik barang di gudang, kemudian membandingkannya dengan stok barang di sistem. Jika terjadi selisih diantara fisik dengan sistem tersebut maka dilakukan adjustment atau penyesuaian. Supaya stok barang di sistem dengan stok barang di gudang (fisik) adalah sama. Pada perusahaan-perusahaan besar sudah pasti di dukung dengan alat dan aplikasi yang mumpuni untuk melakukan Stock Opname ini, sehingga proses yang dilakukan efisien dan akurat. Namun untuk aplikasi dan alat tersebut sudah tentu tidaklah murah. Nahh, untuk kalian yang memiliki usaha dalam skala menengah ke bawah bisa dicoba dengan Aplikasi Scan dan Hitung Stock Opname Menggunakan Ms.Excel yang akan saya jelaskan dalam artikel kali ini.

Tampilan Aplikasi Scan dan Hitung SO by deafile.blogspot.com.

1.PERSIAPAN APLIKASI HITUNG SO

Aplikasi ini dapat membantu anda dalam menghitung dan menjumlahkan stok barang dalam jumlah yang banyak. Hal pertama yang anda butuhkan untuk menggunakan aplikasi ini adalah Master Barang, yang berisi tentang Barcode Barang, Kode Barang, dan Nama Barang. Yang kedua yaitu alat Scanner Barcode, kemudian aplikasi Microsoft Excel yang mendukung kode macro didalamnya. Jika Microsoft Excel yang anda punya sudah mendukung untuk kode macro maka akan muncul menu Developer seperti pada gambar dibawah ini.

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 Aplikasi ini. Namun sebelum saya jelaskan lebih lanjut, silahkan lihat cuplikan dari penggunaan Aplikasi Scan dan Hitung Stock Opname Menggunakan Ms.Excel berikut ini.

2.PENJELASAN APLIKASI HITUNG SO

Dari cuplikan video diatas sudah saya berikan contoh bagaimana menggunakan Aplikasinya. Dan disini akan saya berikan sedikit lagi penjelasan mengenai menu yang tersedia di Aplikasi Hitung SO ini.

2.a) Master Barang, pada sheet ini berisi kumpulan barang yang akan anda hitung nantinya. Kegunaan sheet Master Barang ini untuk menampilkan Kode Barang dan Nama Barang jika anda memasukkan Barcode di Aplikasi Hitung SO nantinya. Ada beberapa rumus pada sheet Master Barang ini yang sangat penting agar Aplikasi Hitung SO ini berjalan. Oleh karena itu saya mengunci rumus tersebut supaya tidak bisa anda hapus. Jadi anda tidak perlu khawatir untuk mengedit dan mengganti nilainya, sesuaikan dengan Master Barang yang anda miliki.

2.b) Database, sheet Database ini digunakan sebagai tampungan data dari hasil inputan di Aplikasi Hitung SO. Lengkap dengan nama perangkat yang menginput dan waktu inputnya kapan.

2.b) Pivot Database, sheet ini berfungsi untuk menjumlahkan data yang ada di sheet Database. Klik kanan pada tabel Pivot kemudian klik Refresh, agar data penjumlahan update.

3.DOWNLOAD APLIKASI HITUNG SO

Saya rasa cukup untuk penjelasannya, jika anda berminat untuk menggunakan Aplikasi Scan dan Hitung Stock Opname Menggunakan Ms.Excel ini. Silahkan klik link download dibawah ini. GRATIS..!!

Download Aplikasi Scan dan Hitung Stock Opname Menggunakan Ms.Excel

Tak lupa juga saya ucapkan terima kasih kepada

-bukuyudi.blogspot.com dengan judul artikelnya
Download Contoh Form Entri Database Worksheet Excel [VBA]

-Channel Youtube TheDataLabs dengan videonya
Fully Automated Data Entry User Form in Excel - Step By Step Tutorial.

Karena berkat kedua rumus tersebut saya bisa mendapatkan inspirasi untuk menggabungkannya dan saya buat menjadi artikel ini. Sekian dari saya, kurang lebihnya mohon maaf. Apabila ada hal yang ingin ditanyakan atau sekedar memberikan kritik dan saran, silahkan sampaikan melalui kolom komentar dibawah. Terima kasih.

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

- 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 :)

Berlangganan

Ikuti Blog ini

Statistik Deafile

&data;

&data;

&data;

Tutorial

Bekerja Sama

Kirim Artikel

Berlangganan Artikel

Aplikasi Scan dan Hitung Stock Opname Menggunakan Ms.Excel

Rumus VBA Macro Excel Untuk Validasi Pembayaran Semi Otomatis

Postingan Populer

Ikuti Blog ini

Profil Admin

Kotak Diskusi