Assalamualaikum ..
Dear Mr.Kid,
Terima kasih atas pencerahannya. Skripnya saya sudah coba dan berhasil.
Salam
Edo
On Wednesday, May 18, 2016 6:54 PM, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> wrote:
Hai Edo,
Di Excel cells, supaya fungsi Match yang menghasilkan error value bisa hilang
dibutuhkan fungsi IFError atau perlakuan dengan fungsi lainnya.
Jadi, di VBA perlakukan yang sama.
Pada prinsipnya, buatlah kalimat formula dalam Excel cells yang menghasilkan
nilai yang benar lebih dahulu, barulah setelah itu di translate ke bunyi
perintah VBA.
Contoh :
di C1 ada formula match berbunyi :
=Match( A1 , $g$7:$g$23 , 0 )
berhubung formula di atas bisa menghasilkan error value, maka si match dikenai
iferror menjadi :
=IFError( Match( A1 , $g$7:$g$23 , 0 ) , "" )
Maka di dapat bunyi formula akhir yang sesuai kebutuhan adalah :
=IFError( Match( A1 , $g$7:$g$23 , 0 ) , "" )
Akan pakai VBA dalam memanggil fungsi Match dan IFError :
dim lPosisi as long
lPosisi=application.worksheetfunction.IFError(
application.worksheetfunction.match( range("a1") , range("$g$7:$g$23") , 0 ) ,
"" )
Regards,
Kid
2016-05-18 14:39 GMT+07:00 [email protected] [belajar-excel]
<[email protected]>:
Dear Para Master,
Saya mencoba membuat VBA untuk identifikasi semacam nomer index untuk setiap
data di range"data" (J2:J6) dengan memakai application.match. Bagaimana cara
penulisan skrip VBA yang tepat supaya tidak ada #N/A jika di data input ada
baris yang kosong seperti sel B3:B6?
Terima Kasih
Edo
#yiv8941337053 #yiv8941337053 -- #yiv8941337053ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8941337053
#yiv8941337053ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8941337053
#yiv8941337053ygrp-mkp #yiv8941337053hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8941337053 #yiv8941337053ygrp-mkp #yiv8941337053ads
{margin-bottom:10px;}#yiv8941337053 #yiv8941337053ygrp-mkp .yiv8941337053ad
{padding:0 0;}#yiv8941337053 #yiv8941337053ygrp-mkp .yiv8941337053ad p
{margin:0;}#yiv8941337053 #yiv8941337053ygrp-mkp .yiv8941337053ad a
{color:#0000ff;text-decoration:none;}#yiv8941337053 #yiv8941337053ygrp-sponsor
#yiv8941337053ygrp-lc {font-family:Arial;}#yiv8941337053
#yiv8941337053ygrp-sponsor #yiv8941337053ygrp-lc #yiv8941337053hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8941337053
#yiv8941337053ygrp-sponsor #yiv8941337053ygrp-lc .yiv8941337053ad
{margin-bottom:10px;padding:0 0;}#yiv8941337053 #yiv8941337053actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8941337053
#yiv8941337053activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8941337053
#yiv8941337053activity span {font-weight:700;}#yiv8941337053
#yiv8941337053activity span:first-child
{text-transform:uppercase;}#yiv8941337053 #yiv8941337053activity span a
{color:#5085b6;text-decoration:none;}#yiv8941337053 #yiv8941337053activity span
span {color:#ff7900;}#yiv8941337053 #yiv8941337053activity span
.yiv8941337053underline {text-decoration:underline;}#yiv8941337053
.yiv8941337053attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8941337053 .yiv8941337053attach div a
{text-decoration:none;}#yiv8941337053 .yiv8941337053attach img
{border:none;padding-right:5px;}#yiv8941337053 .yiv8941337053attach label
{display:block;margin-bottom:5px;}#yiv8941337053 .yiv8941337053attach label a
{text-decoration:none;}#yiv8941337053 blockquote {margin:0 0 0
4px;}#yiv8941337053 .yiv8941337053bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8941337053
.yiv8941337053bold a {text-decoration:none;}#yiv8941337053 dd.yiv8941337053last
p a {font-family:Verdana;font-weight:700;}#yiv8941337053 dd.yiv8941337053last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8941337053
dd.yiv8941337053last p span.yiv8941337053yshortcuts
{margin-right:0;}#yiv8941337053 div.yiv8941337053attach-table div div a
{text-decoration:none;}#yiv8941337053 div.yiv8941337053attach-table
{width:400px;}#yiv8941337053 div.yiv8941337053file-title a, #yiv8941337053
div.yiv8941337053file-title a:active, #yiv8941337053
div.yiv8941337053file-title a:hover, #yiv8941337053 div.yiv8941337053file-title
a:visited {text-decoration:none;}#yiv8941337053 div.yiv8941337053photo-title a,
#yiv8941337053 div.yiv8941337053photo-title a:active, #yiv8941337053
div.yiv8941337053photo-title a:hover, #yiv8941337053
div.yiv8941337053photo-title a:visited {text-decoration:none;}#yiv8941337053
div#yiv8941337053ygrp-mlmsg #yiv8941337053ygrp-msg p a
span.yiv8941337053yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8941337053
.yiv8941337053green {color:#628c2a;}#yiv8941337053 .yiv8941337053MsoNormal
{margin:0 0 0 0;}#yiv8941337053 o {font-size:0;}#yiv8941337053
#yiv8941337053photos div {float:left;width:72px;}#yiv8941337053
#yiv8941337053photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv8941337053
#yiv8941337053photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8941337053
#yiv8941337053reco-category {font-size:77%;}#yiv8941337053
#yiv8941337053reco-desc {font-size:77%;}#yiv8941337053 .yiv8941337053replbq
{margin:4px;}#yiv8941337053 #yiv8941337053ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8941337053 #yiv8941337053ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8941337053
#yiv8941337053ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8941337053
#yiv8941337053ygrp-mlmsg select, #yiv8941337053 input, #yiv8941337053 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8941337053
#yiv8941337053ygrp-mlmsg pre, #yiv8941337053 code {font:115%
monospace;}#yiv8941337053 #yiv8941337053ygrp-mlmsg *
{line-height:1.22em;}#yiv8941337053 #yiv8941337053ygrp-mlmsg #yiv8941337053logo
{padding-bottom:10px;}#yiv8941337053 #yiv8941337053ygrp-msg p a
{font-family:Verdana;}#yiv8941337053 #yiv8941337053ygrp-msg
p#yiv8941337053attach-count span {color:#1E66AE;font-weight:700;}#yiv8941337053
#yiv8941337053ygrp-reco #yiv8941337053reco-head
{color:#ff7900;font-weight:700;}#yiv8941337053 #yiv8941337053ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8941337053 #yiv8941337053ygrp-sponsor
#yiv8941337053ov li a {font-size:130%;text-decoration:none;}#yiv8941337053
#yiv8941337053ygrp-sponsor #yiv8941337053ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8941337053
#yiv8941337053ygrp-sponsor #yiv8941337053ov ul {margin:0;padding:0 0 0
8px;}#yiv8941337053 #yiv8941337053ygrp-text
{font-family:Georgia;}#yiv8941337053 #yiv8941337053ygrp-text p {margin:0 0 1em
0;}#yiv8941337053 #yiv8941337053ygrp-text tt {font-size:120%;}#yiv8941337053
#yiv8941337053ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8941337053
