MEMBUAT LIST SEARCHING DATA DINAMIS

Bismillahi Rahmani Rahim

Assalamualaikum Wr. Wb.

Akhirnya bisa nulis lagi, kali ini pembahasan artikelnya mungkin sedikit jarang digunakan dan memang masih jarang penggunaan model ini.

Membuat "List Searching Data Dinamis" biasanya digunakan pada formulir input data untuk meminimalisir kesalahan input keyData seperti nama, Lokasi dan sebagainya, seperti contoh animasi aplikasi saya dibawah ini.

Pencarian nama driver dengan cara mengetikkan huruf yang terkandung dalam nama personal tersebut akan menampilkan driver tersebut pada list teratas. Jika nama telah berada di list teratas maka kita hanya menekan enter dan nama personal tersebut otomatis masuk ke textbox hasil pencarian.

Ok kita langsung saja ke TKP.

Alat dan Bahan :

- 1 File Berekstensi Macro Enabled

- 1 Userform, 1 Textbox, 1 Listbox

- 1 Image dan secukupnya Label

Assembly:

1. Buat 1 File berekstensi macro enabled xlsm atau xlsb  dan rename salah satu sheet menjadi nama sheet "Sampel Data" dan isikan data seperti gambar dibawah ini. 
   
2. Masuk ke jendela VBA (tekan Alt F11) kemudian insert Userform
3. Klik Userform dan insert semua properti yang dingginkan seperti Textbox, Listbox, Image, Label dan atur sesuai keinginan tetapi ingat untuk mengganti nama masing-masing properti sesuai coding script dibawah ini :
4. Userform = formPencarian, Textbox = txtCari, Listbox = lBoxPencarian, Label = lblNotif dan lblNotifENter, Image1
5. Setelah mengganti semua nama property mari kita mulai mengatur posisi property agar lebih user friendly hahaha...... pakai bahasa istilah dikit boleh donk, lihat gambar berikut ini 
     

Sekarang mari kita double klik pada textbox (txtCari) kemudian masukkan script berikut :

Private Sub txtCari_AfterUpdate()

      On Error Resume Next

      Me.txtCari.Value = Me.lBoxPencarian.List(0, 0)

      Me.lBoxPencarian.Visible = False

      Me.lblNotifENter.Visible = False

End Sub

Private Sub txtCari_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    On Error Resume Next

    Me.txtCari.Value = Me.lBoxPencarian.List(0, 0)

    Me.lBoxPencarian.Visible = False

    Me.lblNotifENter.Visible = False

End Sub

Private Sub txtCari_Change()

    Me.lblNotifENter.Visible = True

    If Me.txtCari.Value = "" Then

        Me.lBoxPencarian.Visible = False

        Exit Sub

    End If

    

    Me.lBoxPencarian.Visible = True

    Dim shtPencarian    As Worksheet

    Dim BarisMulai      As Long

    Dim PosisiKolom     As Integer

    Dim TeksPencarian   As String

    Dim Hasil           As String

    Dim RngCell         As Range

    Dim rngCariUlang    As Range

                

            '============================== KODE KHUSUS LITSBOX =================================

            TeksPencarian = UCase(Me.txtCari.Value)                 'Teks yang dicari

            PosisiKolom = 2                                         'Kolom Kriteria Pencarian di Tabel Sheet

            BarisMulai = 4                                          'Pencarian dimulai di baris

            Set shtPencarian = ThisWorkbook.Sheets("Sampel Data")   'Setting Sheet Destinasi

            '====================================================================================

            

    Me.lBoxPencarian.Clear

        If Trim(TeksPencarian) <> "" Then

        Do Until shtPencarian.Cells(BarisMulai, PosisiKolom) = ""

            With shtPencarian.Range(shtPencarian.Cells(BarisMulai, PosisiKolom), shtPencarian.Cells(500, PosisiKolom))

            Set rngCariUlang = shtPencarian.Range(shtPencarian.Cells(BarisMulai, PosisiKolom), _

            shtPencarian.Cells(500, PosisiKolom))

                    Set Rng = .Find(What:=TeksPencarian, After:=.Cells(1), LookIn:=xlFormulas, _

                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

                    If Not Rng Is Nothing Then

                        Me.lBoxPencarian.AddItem Rng

                        On Error Resume Next

                        Hasil = Application.WorksheetFunction.Match(Me.lBoxPencarian.List(Me.lBoxPencarian.ListIndex + 1, 0), _

                        rngCariUlang, 0)

                    End If

            End With

        On Error GoTo Berhenti

        BarisMulai = Hasil + BarisMulai + 1

        Loop

        End If

Berhenti:

End Sub

Private Sub txtCari_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Me.lblNotif.Visible = True

End Sub

Untuk Listbox (lBoxPencarian) double klik dan masukkan script ini:

Private Sub lBoxPencarian_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Me.txtCari = Me.lBoxPencarian.List(0, 0)

    Me.lblNotifENter.Visible = False

End Sub

Untuk Userform DoubleClick dan masukkan ini

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

      Me.lblNotif.Visible = False

End Sub

Untuk Image1 DoubleClick dan masukkan script ini: 

Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Me.lblNotif.Visible = False

End Sub

Jangan lupa insert sebuah modul untuk script macro pemanggilan di sheet

di jendela VBA klik Insert >> Module kemudian masukkan script berikut

Sub tampilkancari()

    formPencarian.Show

End Sub

Kemudian di WorkSheet "Sampel Data" insert Shapes kotak saja kemudian klik kanan >> assign Macro... kemudian Pilih "tampilkancari"

SAVE hasil kerja anda :) ingat format xlsm atau xlsb

Hasil dari percobaan kita seperti animasi berikut

Untuk Contoh file dapat di lihat dengan klik link  kemudian cari judul file "List Seraching Data Dinamis". atau klik pada menu blog kemudian pilih Download >> File >> Aplikasi VBA.

Sekian artikel kali ini, jangan bosan untuk berkunjung semoga saran anda menghasilkan inspirasi baru :)

Wassalam

KJ99

EIUG Makassar

Read More

MENGHITUNG STOK BARANG Versi 2

BismillahiRahmaniRahim

Assalamualaikum Wr. Wb.

Artikel ini adalah lanjutan dari artikel KJ sebelumnya yang banyak dikunjungi di blog KJ excekj.blogspot.co.id, yaitu MENGHITUNG STOK BARANG.

Tetapi kali ini akan dijelaskan secara detail cara pembuatannya mulai dari awal hingga penggunaannya. dari sekian banyak yang mengunjungi artikel sebelumnya, ada beberapa masalah yang muncul, diantaranya Calendar Add-Ins Tidak muncul, itu karena di Excel sobat tidak melakukan setting di VBA pada Tools tambahan dan caranya banyak di share di google. Masalah terakhir yang muncul adalah blokir download file di dropbox error 403 yang disebabkan trafik kunjungan ke file tersebut overload karena akun dropbox KJ belum di upgrade ke berbayar. kedepannya untuk mengatasi file tidak bisa di download anda cukup email ke kami atau inbox di facebook KJ di menu kanan atas blog ini.

Baik kita langsung saja ke pembahasan kita, yaitu (Versi 2) MENGHITUNG STOK BARANG beserta tutorial pembuatannya.

Alat dan Bahan:

• Siapkan File Excel yang support macro, biasanya di save ke ekstensi .xlsm atau .xlsb kemudian isi dengan 3 sheet, yaitu Data_Barang, Data_Transaksi dan Laporan. model ini adalah model sebuah file perhitungan sederhana.
• Pada Sheet Data_Barang kita Siapkan Tabel Data barang sederhana saja yang memiliki informasi detail barang yang kita miliki seperti gambar dibawah ini, dan insert sebuah TextBox di kanan atas tabel untuk cari barang sesuai kriteria OptionButton. (ini bonus saja hehe)

• Untuk Macro Pencarian kita dapat memasukkan kode dibawah ini, tetapi jangan lupa untuk insert komponen ActiveX Control (1 TextBox dan 3 OptionButton dan Label) dan ganti Caption masing-masing seperti gambar diatas. 
  
  Catatan untuk Macro buka dengan Alt F11 kemudian Double klik pada Sheet Data_Barang dan paste kan kode dibawah ini.
  
  
  Private Sub OptionButton3_Click()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs        As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Barang")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang)

          .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3

          .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2

      End With

  End Sub

  
  

  Private Sub TextBox1_Change()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs        As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Barang")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang)

          Kriteria = .TextBox1.Value

          On Error Resume Next

          If .OptionButton1.Value = True Then

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

          If .OptionButton2.Value = True Then

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

      End With

  End Sub

• Untuk Sheet Data_Transaksi tampilan dapat kita lihat seperti gambar dibawah ini
  
  
  
  Hampir sama dengan Sheet Data_Barang perbedaannya adalah pada kolom Tanggal, keluar/masuk barang dan jumlah transaksi. dari gambar kita melihat ada kolom stok pada kolom paling kanan. sedikit penjelasan dan penggunaan rumus untuk Sheet ini adalah rumus yang kita gunakan di kolom Nama_Barang dan Kolom Stok. 
  Kolom Nama_barang pada Cell F4 bisa di Copy kebawah, kita memakai rumus berikut :=IFERROR(OFFSET(Data_Barang!$C$2,MATCH(D4,Data_Barang!$B$3:$B$999999,0),0),"")untuk penjelasan rumus Offset dan Match dapat kita lihat di artikel KJ lainnya, inti dari rumus diatas adalah kita menetapkan posisi Start kita di cell pada C2 sheet Data_Barang kemudian pada rumus Match kita menghitung dibaris berapa Kode Barang (D4) berada pada Range B3:B9999999 Sheet Data_barang dan 0 paling ujung menetapkan kolom Offset tidak bergerak tetap di kolom C. D4 dan seterusnya kebawah pada Sheet Data_Transaksi merupakan kunci pemanggilan untuk Nama Barang.
  
  Kolom Stok pada Cell H4 bisa di Copy kebawah, kita menggunakan rumus:=SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Masuk")-SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Keluar")Penjelasan dari rumus ini secara umum adalah menjumlahkan semua barang masuk dikurangi dengan semua barang keluar. secara detail adalah =Jumlahkan jika (Jumlahkan kolom ini, jika kolom D4 s.d D Baris rumus ini = kode_barang baris ini, jika Kolom C4 s.d C baris rumus ini berkriteria "Masuk") - SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Keluar") kira-kira seperti itu bunyi rumus tersebut jadi titik awal dihitung di baris 4 (kita kunci dengan tanda $) dan titik akhir berdasarkan posisi baris rumus ini (tidak dikunci/ tidak ada tanda $)
  
  Catatan : Rumus Stok itu kita bisa Cut (Ctrl X) ke Sheet data Barang tetapi dengan sedikit modifikasi pada range mengacu ke baris paling bawah Table pada Sheet Data Transaksi. dan mengganti D4 (referensi kriteria ke Kode barang Tabel Sheet Data_Barang)
  
  kode Macro untuk Sheet Data_Transaksi (ingat untuk mengganti nama masing-masing ActiveX Control sesuai nama pada macro dibawah ini) :
  

  
  Catatan untuk Macro buka dengan Alt F11 kemudian Double klik pada Sheet Data_Transaksi dan paste kan kode dibawah ini.
  
  Private Sub OptionButtonAll_Click()

  Application.ScreenUpdating = False

      Dim rngBarang  As Range

      Dim jBrs              As Long

      Dim Kriteria       As String

  
  

      With Sheets("Data_Transaksi")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang) + 1

          .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4

          .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6

      End With

  End Sub

  
  

  Private Sub TextBox1_Change()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs           As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Transaksi")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang) + 1

          Kriteria = .TextBox1.Value

          On Error Resume Next

          If .OptionButtonNama.Value = True Then

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

          If .OptionButtonKode.Value = True Then

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

      End With

  End Sub

  
  
• Kita beralih ke Sheet Laporan,
  Tabel dapat kita lihat seperti gambar dibawah ini
  

  

  
  

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Pada Sheet ini kita merekapitulasi data berdasar Tanggal yang kita inginkan jadi pada kolom Tanggal kita bebas menggantinya sesuka hati dengan catatan Data akan muncul jika Tanggal tersebut ada di Data Transaksi.
  Kunci pencarian adalah tanggal dan kita memanggil No_Transaksinya agar lebih mudah memanggil data yang lain. pada Cell B5 Kolom No_Transaksi rumus yang kita gunakan adalah rumus CSE (eksekusi Ctrl Shift Enter) atau biasa disebut Array Formula.
  {=IFERROR(SMALL(IF(OFFSET(Data_Transaksi!$B$3,1,0,COUNTA(Data_Transaksi!A1:A1000007)+3,1)=$B$3,OFFSET(Data_Transaksi!$A$3,1,0,COUNTA(Data_Transaksi!A1:A1000007)+3,1)),ROW(B5)-ROW($B$4)),"")}Copy rumus ini kebawah pada kolom yang sama.
  
  Pada kolom Kode_Barang Cell C5 rumus yang digunakan adalah;
  =IFERROR(OFFSET(Data_Transaksi!$D$3,MATCH($B5,OFFSET(Data_Transaksi!$A$3,1,0,COUNTA(Data_Transaksi!$A1:$A1000007)+3,1),0),0),"")Fungsi COUNTA adalah untuk memblok titik terjauh range sesuai Jumlah baris pada Tabel Data_Transaksi.
  
  Saya sengaja tidak memasukkan Form Date Control  untuk menghindari error missing references pada Office user. tetapi jika anda memiliki silahkan gunakan dan output mengacu pada Cell B3 (Cell Tanggal).

Demikian Tutorial MENGHITUNG STOK BARANG Versi 2 dari saya, semoga bermanfaat untuk kita semua.

Catatan: untuk download contoh filenya, anda dapat menuju Halaman Download di Menu diatas dengan Kata Kunci Menghitung Stok Barang Versi 2, atau klik link tulisan yang berwarna biru.

Salam Excel

KJ

Read More

Menghitung Jumlah Kata Dalam Satu Cell di Excel menggunakan function SUBTITUTE dan LEN

Assalamualaikum Wr, Wb

Apa kabar sobat Excel, harapannya selalu dalam Rahmat Tuhan Yang Maha Esa dan sehat selalu.

Sebenarnya kasus (Menghitung Jumlah Kata Dalam Satu Cell di Excel) ini sudah lama di bahas dalam berbagai grup Excel maupun Blog dan Artikel di dunia maya, tetapi tidak ada salahnya jika kali ini penulis memaparkan secara detail bagaimana rumus-rumus itu bisa bekerja menghitung jumlah kata yang ada dalam satu cell tersebut.

Mungkin beberapa Sobat Excel sudah tau cara ini jika menggunakan Microsoft Office Word, yaitu dengan klik pada Ribbon Menu Tools => Word Count maka penjelasan data yang menyangkut jumlah karakter, kata, paragraf dan sebagainya di sebuah halaman document akan terlihat, seperti gambar dibawah ini.
Word 2007

Word 2013

Nah bagaimana jika kasus itu terjadi di Microsoft Office Excel?
beberapa dari sobat Excel mungkin telah mencoba mengkombinasikan beberapa rumus di Microsoft Excel seperti FIND, LEN, MID, SUBTITUTE, SEARCH dan rumus-rumus lain yang terkait Text dalam Microsoft Excel tetapi masih bingung dan ujungnya bertanya kok bisa?

Ok, mari kita lihat rumus berikut yang sering dibahas di berbagai grup Excel atau blog, coba perhatikan gambar dibawah ini:

Pada gambar tersebut tertulis rumus =LEN(LEN(A1)-LEN(SUBTITUTE(A1," ",""))+1 dan hasilnya 61 kata dalam cell tersebut. Mari kita bahas satu persatu alur rumus tersebut hingga dapat menghasilkan jumlah kata yang sesuai isi dalam cell tersebut.

Kita mulai dengan rumus LEN
Syntax rumus LEN =    untuk menghitung jumlah karakter Text dalam sebuah Cell. Text yang dimaksud adalah Number, Alphabet, String, Spasi dan lainnya yang bisa di ketikkan ke cell.
contoh :
di Cell B15 terdapat tulisan "1234 rt tr coba saja" dan jika kita hitung beserta spasi maka jumlahnya 20 karakter, artinya jika di akhir text kita tambahkan spasi lagi maka jumlahnya akan bertambah lagi sesuai jumlah spasi yang kita tambahkan walaupun tidak kelihatan perbedaanya di visualnya saat di enter.

Funtion atau Rumus SUBTITUTE
Syntax rumus SUBTITUTE = SUBTITUTE mengganti text dalam sebuah cell dengan text yang kita inginkan, dalam sytax tersebut kita melihat ada Text, Old_Text, New_Text, Instance_Num.
contoh :

Text :
yaitu keseluruhan text cell yang dituju seperti contoh di atas, atau bisa kita ketik langsung dalam rumus misalnya =SUBTITUTE("Kambing","ambing","uda") maka hasilnya adalah Kuda karena kata "ambing"berubah/diganti menjadi "uda" hasilnya"K+uda = Kuda".
Old_Text :yaitu Text sebelumnya atau text lama yang akan digantikan
New_Text :
yaitu Text baru untuk menggantikan text lama

Setelah melihat penjelasan kedua rumus diatas, mari kita melihat alur kerja kedua rumus tersebut sehingga mampu menghitung jumlah kata dalam sebuah cell seperti contoh awal tadi.
=LEN(A1)-LEN(SUBTITUTE(A1," ",""))+1
perhatikan contoh dibawah ini, kita pindahkan ke cell A3 sumber text cell nya:

Rumus LEN pertama menghasilkan 34 karakter
Rumus LEN kedua menghasilkan 30 karakter setelah spasi dihilangkan (spasi di subtitusi ke "")
LEN1 - LEN2 = 4
pertanyaan yang muncul kenapa selisihnya cuma 4? sedangkan jumlah kata aktual ada 5?
karena yang kita subtitusi adalah karakter spasi karena spasi selalu berada diantara kata bararti selalu selisih 1 (satu) maka untuk mendapatkan jumlah kata kita hanya perlu menambahkan angka 1, seperti pola berikut : KATA(spasi)KATA(spasi)KATA(spasi)KATA(spasi)KATA.
silahkan download contoh file di tab download atau menuju kesini dengan keyword "Menghitung Jumlah Kata Dalam Satu Cell di Excel menggunakan function SUBTITUTE dan LEN"

Sampai disini saya rasa semua Sobat Excel sudah mengerti. Jika ada kritik atau saran silahkan beri komentar atau hubungi kami di kontak sosial media.

Catatan: semua penggunaan rumus di artikel ini menggunakan koma (,) sesuai dengan regional komputer penulis. dan jika rumus tidak bisa berjalan kemungkinan regional berbeda dan harus diganti ke titikkoma (;) dalam penulisan rumusnya atau sesuai dengan regional komputer anda.
Keterangan : Menggunakan Microsoft Office Excel 2013


Salam Excel

Read More