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
