dear master toby
oh , jadi fungsi lookup tersebut digunakan untuk membantu ketika mencari jenis film , sehingga ketika txtkode tersebut berisikan kode kaset nantik E1 akan menghasilkan jenis dan akan menjadu Value dar txtjenis. terima kasih penjelasannya Salam, monnalissa On Thu, Jan 21, 2016 at 11:05 PM, Toby kenzo [email protected] [belajar-excel] <[email protected]> wrote: > > > > Dear Eko, > > Pertama. > Pada Sheet1 di Cell E1 buat formula EXCEL > =IFERROR(VLOOKUP(D1,$A$2:$B$4,2),"") > maksudnya adalah jika di cell D1 terisi kode, maka di cell E1 akan terisi > data di sebelahnya cell yang terisi kode-kode (tahu khan fungsi formula > Vlookup, kalo belum coba buka2 lagi file di milis ini yang ada penjelasan > tentang Vlookup) > > Kedua. > Delete semua formula yang ada di Sub cmdfind_Click() : > tahu khan lokasinya..? kalo ngga tahu caranya adalah klik 2 kali tombol > "FIND" di userform > selanjutnya, > masukan formula: > > Dim a As String > a = Me.txtkode.Value > > Sheet1.Range("D1").Value = a > Me.txtjenis.Value = Sheet1.Range("E1").Value > > formula di atas artinya: > > pada sheet1 di cell D1 adalah nilainya textbox yang anda beri nama txtkode > (masukan deh salah satu kode film yang ada...) > > karena nilai di Sheet1 Cell D1 terisi maka formula Vlookup yg tadi di > bahas mulai bekerja. > artinya jika di Cell D1 nilainya adalah nilai kode(nilai yang di masukan > di textbox yg anda beri nama txtkode), maka di Cell E1 menjadi nilainya > "jenis Film" > nilai di Sheet1 Cell E1 ini lah yang di muculkan di textbox yang anda beri > nama txtjenis.. > > Semoga bisa membantu... > > TerimaKasih. > > Pada Kam, 21/1/16, Septian Eko Saputro [email protected] > [belajar-excel] <[email protected]> menulis: > > Judul: [belajar-excel] Re: VBA find dibeda Sheet > Kepada: [email protected] > Tanggal: Kamis, 21 Januari, 2016, 9:34 PM > > > > > > > > > > > > > > Mr toby > Maksud > dari rumus tersebut apa ya? Mohon pencerahan > > On Thursday, 21 January 2016, > Toby kenzo [email protected] > [belajar-excel] <[email protected]> > wrote: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hai Eko, > > > > 1. Buat Formula Excel di Cell E1 : > "=IFERROR(VLOOKUP(D1,$A$2:$B$4,2),"")" > > 2. Masukan formula di Sub cmdfind_Click() : > > Dim a As String > > > > a = Me.txtkode.Value > > Sheet1.Range("D1").Value = a > > > > Me.txtjenis.Value = Sheet1.Range("E1").Value > > > > TerimaKasih. > > > > -------------------------------------------- > > Pada Rab, 20/1/16, Septian Eko Saputro [email protected] > [belajar-excel] > <[email protected]> > menulis: > > > > Judul: [belajar-excel] VBA find dibeda Sheet [1 > Attachment] > > Kepada: [email protected] > > Tanggal: Rabu, 20 Januari, 2016, 10:42 PM > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Dear Expert > > > > > > > Saya mempunyai kasus VBA > > untuk sebuah form input data, > > kesulitan saya berada ketika mecari data dari sheet1 > yang > > nantinya digunakan > > untuk mencari jenis film sesuai gambar: > > > > > > > > > > > > > > Tombol find adalah mencari > > hasil jenis dari kriteria kode. Sehingga > > jika kode diisi A1001 nantinya akan muncul hasil > > “Drama”, tetapi saya mengalami > > kesulitan untuk mengambil data tersebut. Apa yang salah > > mengenai rumus VBA yang > > saya buat, ataukah para master mempunyai solusi terkait > case > > tersebut?atau > > > adakah alternatif rumus lain? Mohon pencerahan > > > > > > > > > > > > Salam, > > > > > > monnalissa > > > > > > > > > > > > > > > > > > > > > > > > > > > > #yiv6027948397 #yiv6027948397 -- > > #yiv6027948397ygrp-mkp { > > border:1px solid #d8d8d8;font-family:Arial;margin:10px > > 0;padding:0 10px;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp hr { > > border:1px solid #d8d8d8;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp #yiv6027948397hd { > > color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px > > 0;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp #yiv6027948397ads > { > > margin-bottom:10px;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp .yiv6027948397ad { > > padding:0 0;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp .yiv6027948397ad p > { > > margin:0;} > > > > #yiv6027948397 #yiv6027948397ygrp-mkp .yiv6027948397ad a > { > > color:#0000ff;text-decoration:none;} > > #yiv6027948397 #yiv6027948397ygrp-sponsor > > #yiv6027948397ygrp-lc { > > font-family:Arial;} > > > > #yiv6027948397 #yiv6027948397ygrp-sponsor > > #yiv6027948397ygrp-lc #yiv6027948397hd { > > margin:10px > > 0px;font-weight:700;font-size:78%;line-height:122%;} > > > > #yiv6027948397 #yiv6027948397ygrp-sponsor > > #yiv6027948397ygrp-lc .yiv6027948397ad { > > margin-bottom:10px;padding:0 0;} > > > > #yiv6027948397 #yiv6027948397actions { > > font-family:Verdana;font-size:11px;padding:10px 0;} > > > > #yiv6027948397 #yiv6027948397activity { > > > background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;} > > > > #yiv6027948397 #yiv6027948397activity span { > > font-weight:700;} > > > > #yiv6027948397 #yiv6027948397activity span:first-child { > > text-transform:uppercase;} > > > > #yiv6027948397 #yiv6027948397activity span a { > > color:#5085b6;text-decoration:none;} > > > > #yiv6027948397 #yiv6027948397activity span span { > > color:#ff7900;} > > > > #yiv6027948397 #yiv6027948397activity span > > .yiv6027948397underline { > > text-decoration:underline;} > > > > #yiv6027948397 .yiv6027948397attach { > > clear:both;display:table;font-family:Arial;font-size:12px;padding:10px > > 0;width:400px;} > > > > #yiv6027948397 .yiv6027948397attach div a { > > text-decoration:none;} > > > > #yiv6027948397 .yiv6027948397attach img { > > border:none;padding-right:5px;} > > > > #yiv6027948397 .yiv6027948397attach label { > > display:block;margin-bottom:5px;} > > > > #yiv6027948397 .yiv6027948397attach label a { > > text-decoration:none;} > > > > #yiv6027948397 blockquote { > > margin:0 0 0 4px;} > > > > #yiv6027948397 .yiv6027948397bold { > > font-family:Arial;font-size:13px;font-weight:700;} > > > > #yiv6027948397 .yiv6027948397bold a { > > text-decoration:none;} > > > > #yiv6027948397 dd.yiv6027948397last p a { > > font-family:Verdana;font-weight:700;} > > > > #yiv6027948397 dd.yiv6027948397last p span { > > margin-right:10px;font-family:Verdana;font-weight:700;} > > > > #yiv6027948397 dd.yiv6027948397last p > > span.yiv6027948397yshortcuts { > > margin-right:0;} > > > > #yiv6027948397 div.yiv6027948397attach-table div div a { > > text-decoration:none;} > > > > #yiv6027948397 div.yiv6027948397attach-table { > > width:400px;} > > > > #yiv6027948397 div.yiv6027948397file-title a, > #yiv6027948397 > > div.yiv6027948397file-title a:active, #yiv6027948397 > > div.yiv6027948397file-title a:hover, #yiv6027948397 > > div.yiv6027948397file-title a:visited { > > text-decoration:none;} > > > > #yiv6027948397 div.yiv6027948397photo-title a, > > #yiv6027948397 div.yiv6027948397photo-title a:active, > > #yiv6027948397 div.yiv6027948397photo-title a:hover, > > #yiv6027948397 div.yiv6027948397photo-title a:visited { > > text-decoration:none;} > > > > #yiv6027948397 div#yiv6027948397ygrp-mlmsg > > #yiv6027948397ygrp-msg p a span.yiv6027948397yshortcuts > { > > font-family:Verdana;font-size:10px;font-weight:normal;} > > > > #yiv6027948397 .yiv6027948397green { > > color:#628c2a;} > > > > #yiv6027948397 .yiv6027948397MsoNormal { > > margin:0 0 0 0;} > > > > #yiv6027948397 o { > > font-size:0;} > > > > #yiv6027948397 #yiv6027948397photos div { > > float:left;width:72px;} > > > > #yiv6027948397 #yiv6027948397photos div div { > > border:1px solid > > #666666;height:62px;overflow:hidden;width:62px;} > > > > #yiv6027948397 #yiv6027948397photos div label { > > > color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;} > > > > #yiv6027948397 #yiv6027948397reco-category { > > font-size:77%;} > > > > #yiv6027948397 #yiv6027948397reco-desc { > > font-size:77%;} > > > > #yiv6027948397 .yiv6027948397replbq { > > margin:4px;} > > > > #yiv6027948397 #yiv6027948397ygrp-actbar div a:first-child > { > > margin-right:2px;padding-right:5px;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg { > > font-size:13px;font-family:Arial, helvetica, clean, > > sans-serif;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg table { > > font-size:inherit;font:100%;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg select, > > #yiv6027948397 input, #yiv6027948397 textarea { > > font:99% Arial, Helvetica, clean, sans-serif;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg pre, > #yiv6027948397 > > code { > > font:115% monospace;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg * { > > line-height:1.22em;} > > > > #yiv6027948397 #yiv6027948397ygrp-mlmsg #yiv6027948397logo > { > > padding-bottom:10px;} > > > > > > #yiv6027948397 #yiv6027948397ygrp-msg p a { > > font-family:Verdana;} > > > > #yiv6027948397 #yiv6027948397ygrp-msg > > p#yiv6027948397attach-count span { > > color:#1E66AE;font-weight:700;} > > > > #yiv6027948397 #yiv6027948397ygrp-reco > > #yiv6027948397reco-head { > > color:#ff7900;font-weight:700;} > > > > #yiv6027948397 #yiv6027948397ygrp-reco { > > margin-bottom:20px;padding:0px;} > > > > > #yiv6027948397 #yiv6027948397ygrp-sponsor > #yiv6027948397ov > > li a { > > font-size:130%;text-decoration:none;} > > > > #yiv6027948397 #yiv6027948397ygrp-sponsor > #yiv6027948397ov > > li { > > font-size:77%;list-style-type:square;padding:6px 0;} > > > > #yiv6027948397 #yiv6027948397ygrp-sponsor > #yiv6027948397ov > > ul { > > margin:0;padding:0 0 0 8px;} > > > > #yiv6027948397 #yiv6027948397ygrp-text { > > font-family:Georgia;} > > > > > #yiv6027948397 #yiv6027948397ygrp-text p { > > margin:0 0 1em 0;} > > > > #yiv6027948397 #yiv6027948397ygrp-text tt { > > font-size:120%;} > > > > #yiv6027948397 #yiv6027948397ygrp-vital ul li:last-child > { > > > border-right:none !important; > > } > > #yiv6027948397 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > #yiv9784357340 #yiv9784357340 -- > #yiv9784357340ygrp-mkp { > border:1px solid #d8d8d8;font-family:Arial;margin:10px > 0;padding:0 10px;} > > #yiv9784357340 #yiv9784357340ygrp-mkp hr { > border:1px solid #d8d8d8;} > > #yiv9784357340 #yiv9784357340ygrp-mkp #yiv9784357340hd { > color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px > 0;} > > #yiv9784357340 #yiv9784357340ygrp-mkp #yiv9784357340ads { > margin-bottom:10px;} > > #yiv9784357340 #yiv9784357340ygrp-mkp .yiv9784357340ad { > padding:0 0;} > > #yiv9784357340 #yiv9784357340ygrp-mkp .yiv9784357340ad p { > margin:0;} > > #yiv9784357340 #yiv9784357340ygrp-mkp .yiv9784357340ad a { > color:#0000ff;text-decoration:none;} > #yiv9784357340 #yiv9784357340ygrp-sponsor > #yiv9784357340ygrp-lc { > font-family:Arial;} > > #yiv9784357340 #yiv9784357340ygrp-sponsor > #yiv9784357340ygrp-lc #yiv9784357340hd { > margin:10px > 0px;font-weight:700;font-size:78%;line-height:122%;} > > #yiv9784357340 #yiv9784357340ygrp-sponsor > #yiv9784357340ygrp-lc .yiv9784357340ad { > margin-bottom:10px;padding:0 0;} > > #yiv9784357340 #yiv9784357340actions { > font-family:Verdana;font-size:11px;padding:10px 0;} > > #yiv9784357340 #yiv9784357340activity { > > background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;} > > #yiv9784357340 #yiv9784357340activity span { > font-weight:700;} > > #yiv9784357340 #yiv9784357340activity span:first-child { > text-transform:uppercase;} > > #yiv9784357340 #yiv9784357340activity span a { > color:#5085b6;text-decoration:none;} > > #yiv9784357340 #yiv9784357340activity span span { > color:#ff7900;} > > #yiv9784357340 #yiv9784357340activity span > .yiv9784357340underline { > text-decoration:underline;} > > #yiv9784357340 .yiv9784357340attach { > clear:both;display:table;font-family:Arial;font-size:12px;padding:10px > 0;width:400px;} > > #yiv9784357340 .yiv9784357340attach div a { > text-decoration:none;} > > #yiv9784357340 .yiv9784357340attach img { > border:none;padding-right:5px;} > > #yiv9784357340 .yiv9784357340attach label { > display:block;margin-bottom:5px;} > > #yiv9784357340 .yiv9784357340attach label a { > text-decoration:none;} > > #yiv9784357340 blockquote { > margin:0 0 0 4px;} > > #yiv9784357340 .yiv9784357340bold { > font-family:Arial;font-size:13px;font-weight:700;} > > #yiv9784357340 .yiv9784357340bold a { > text-decoration:none;} > > #yiv9784357340 dd.yiv9784357340last p a { > font-family:Verdana;font-weight:700;} > > #yiv9784357340 dd.yiv9784357340last p span { > margin-right:10px;font-family:Verdana;font-weight:700;} > > #yiv9784357340 dd.yiv9784357340last p > span.yiv9784357340yshortcuts { > margin-right:0;} > > #yiv9784357340 div.yiv9784357340attach-table div div a { > text-decoration:none;} > > #yiv9784357340 div.yiv9784357340attach-table { > width:400px;} > > #yiv9784357340 div.yiv9784357340file-title a, #yiv9784357340 > div.yiv9784357340file-title a:active, #yiv9784357340 > div.yiv9784357340file-title a:hover, #yiv9784357340 > div.yiv9784357340file-title a:visited { > text-decoration:none;} > > #yiv9784357340 div.yiv9784357340photo-title a, > #yiv9784357340 div.yiv9784357340photo-title a:active, > #yiv9784357340 div.yiv9784357340photo-title a:hover, > #yiv9784357340 div.yiv9784357340photo-title a:visited { > text-decoration:none;} > > #yiv9784357340 div#yiv9784357340ygrp-mlmsg > #yiv9784357340ygrp-msg p a span.yiv9784357340yshortcuts { > font-family:Verdana;font-size:10px;font-weight:normal;} > > #yiv9784357340 .yiv9784357340green { > color:#628c2a;} > > #yiv9784357340 .yiv9784357340MsoNormal { > margin:0 0 0 0;} > > #yiv9784357340 o { > font-size:0;} > > #yiv9784357340 #yiv9784357340photos div { > float:left;width:72px;} > > #yiv9784357340 #yiv9784357340photos div div { > border:1px solid > #666666;height:62px;overflow:hidden;width:62px;} > > #yiv9784357340 #yiv9784357340photos div label { > > color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;} > > #yiv9784357340 #yiv9784357340reco-category { > font-size:77%;} > > #yiv9784357340 #yiv9784357340reco-desc { > font-size:77%;} > > #yiv9784357340 .yiv9784357340replbq { > margin:4px;} > > #yiv9784357340 #yiv9784357340ygrp-actbar div a:first-child { > margin-right:2px;padding-right:5px;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg { > font-size:13px;font-family:Arial, helvetica, clean, > sans-serif;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg table { > font-size:inherit;font:100%;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg select, > #yiv9784357340 input, #yiv9784357340 textarea { > font:99% Arial, Helvetica, clean, sans-serif;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg pre, #yiv9784357340 > code { > font:115% monospace;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg * { > line-height:1.22em;} > > #yiv9784357340 #yiv9784357340ygrp-mlmsg #yiv9784357340logo { > padding-bottom:10px;} > > > #yiv9784357340 #yiv9784357340ygrp-msg p a { > font-family:Verdana;} > > #yiv9784357340 #yiv9784357340ygrp-msg > p#yiv9784357340attach-count span { > color:#1E66AE;font-weight:700;} > > #yiv9784357340 #yiv9784357340ygrp-reco > #yiv9784357340reco-head { > color:#ff7900;font-weight:700;} > > #yiv9784357340 #yiv9784357340ygrp-reco { > margin-bottom:20px;padding:0px;} > > #yiv9784357340 #yiv9784357340ygrp-sponsor #yiv9784357340ov > li a { > font-size:130%;text-decoration:none;} > > #yiv9784357340 #yiv9784357340ygrp-sponsor #yiv9784357340ov > li { > font-size:77%;list-style-type:square;padding:6px 0;} > > #yiv9784357340 #yiv9784357340ygrp-sponsor #yiv9784357340ov > ul { > margin:0;padding:0 0 0 8px;} > > #yiv9784357340 #yiv9784357340ygrp-text { > font-family:Georgia;} > > #yiv9784357340 #yiv9784357340ygrp-text p { > margin:0 0 1em 0;} > > #yiv9784357340 #yiv9784357340ygrp-text tt { > font-size:120%;} > > #yiv9784357340 #yiv9784357340ygrp-vital ul li:last-child { > border-right:none !important; > } > #yiv9784357340 > > >
