Salam Maz Zainul
Maaf Mas Zainul, mungkin sama tapi yang saya maksudkan di INPUT itu adalah code
no yang jumlahnya kebetulan 100 bisa saja bukan angka misalnya saya kasi
masukkan C-01 terus di kolom B1 (1 atau C-01) nilai baris B2 = 1
berarti kalau pake rumus IF pada OUTPUT cell B2 : =IF(INPUT!B2="C-01",1,"") dan
seterusnya Colom C1 (2 atau C-02) maka saya gunakan rumus C2 :
=IF(INPUT!B2="C-02",1,""), begitulah kira-kira, cuma kalau seperti Rumus yang
sama buat di macro kan agak panjang Mas, makanya penyederhanaannya yang saya
bingung
TerimakasihAssalamualaikum
Pada Selasa, 6 Agustus 2019 21.23.39 GMT+7, Zainul Ulum
[email protected] [belajar-excel] <[email protected]> menulis:
Mas Hairul Anam,.
Kalo rumus di B2=REPT(1,COUNTIFS(INPUT!$C2:$P2,B$1)>0)
Sama nggak hasilnya?
Thanks,
-zainul
Sent from Mail for Windows 10
From: HAIRUL ALAM [email protected] [belajar-excel]
Sent: Tuesday, August 6, 2019 4:30 PM
To: [email protected]
Subject: Re: [belajar-excel] menampilkan hasil tanpa formula di
exceldenganscript macro
Terimakasih Master,
Maksud rumusnya seperti yang saya cantumkan di module macro
pada OUTPUT menggunakan logika IF
Sheets("OUTPUT").Range("B2").FORMULA =
"=IF(INPUT!C2=1,1,IF(INPUT!D2=1,1,IF(INPUT!E2=1,1,IF(INPUT!F2=1,1,IF(INPUT!G2=1,1,IF(INPUT!H2=1,1,IF(INPUT!I2=1,1,IF(INPUT!J2=1,1,IF(INPUT!K2=1,1,IF(INPUT!L2=1,1,IF(INPUT!M2=1,1,IF(INPUT!N2=1,1,IF(INPUT!O2=1,1,IF(INPUT!P2=1,1,""""))))))))))))))"
jadi intinya bila input cell C sampai P itu diisi angka misal 1 sampai 100 maka
di OUTPUT cell B sampai CW (1-100) dimana salah satu angka yang masuk di INPUT
cell C - P maka di OUTPUT akan ada nilai 1
cuma rumus tersebut diatas terlalu panjang dan saya bingung mengaplikasikan ke
macro sederhana
dengan macro lampiran yang dikirimkan mas zainul, bisa membantu agar hasilnya
saja yang terinput dan rumus tidak ikut
Terimakasih banyak, untuk rinciannya, dan syukur kalau ada penyederhanaan lagi
Pada Selasa, 6 Agustus 2019 00.00.01 WIB, Zainul Ulum [email protected]
[belajar-excel] <[email protected]> menulis:
Sambil menunggu penjelasan rumsunya,
Silakan dipelajari macro terlampir, semoga sesuai harapan.
Wassalam
-zainul
Sent from Mail for Windows 10
From: Zainul Ulum [email protected] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [email protected]
Subject: RE: [belajar-excel] menampilkan hasil tanpa formula di excel
denganscript macro
Mas Hairul Anam,
Apakah maksud atau penjelasan rumusnya bagaimana? Barangkali rekan-rekan bias
bantu menyederhanakan.
Terimakasih,
-zainul
Sent from Mail for Windows 10
From: HAIRUL ALAM [email protected] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [email protected]
Subject: Re: [belajar-excel] menampilkan hasil tanpa formula di excel
denganscript macro [1 Attachment]
Maaf
Bila bukan vlookup melainkan rumus seperti file terlampir, seperti apa code
macro copy pastenya agar rumusnya tidak tampil, dan hanya hasil formulanya saja
Terimakasih
Pada Jumat, 2 Agustus 2019 23.31.56 WIB, hendrik karnadi
[email protected] [belajar-excel] <[email protected]>
menulis:
Langsung ke TKP aja ya di file terlampir.
Salam,
HK
Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [email protected]
[belajar-excel] <[email protected]> menulis:
Makasih Mas HK
Maaf, Jika menggunakan
code Sheets("b").Range("c4").Value2 = Evaluate("=VLOOKUP(B4,a!B4:D6,2,FALSE)")
hanya apa digunakan untuk 1 baris, kalau misalkan barisnya sampai 1000 code
copy paste hasilnya seperti apa ya
Terimakasih bayak
Maaf, jika saya kurang paham masalah code macro
Pada Selasa, 30 Juli 2019 14.04.04 WIB, hendrik karnadi
[email protected] [belajar-excel] <[email protected]>
menulis:
Coba ganti codenya spt ini :
Sheets("b").Range("c4")...Value2 = Evaluate("=VLOOKUP(B4,a!B4:D6,2,FALSE)")
Salam,
HK
Pada Selasa, 30 Juli 2019 07.47.34 WIB, HAIRUL ALAM [email protected]
[belajar-excel] <[email protected]> menulis:
Salam
Para Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa
menampilkan rumus di cell excel, seperti file terlampir
Terimakasih
Assalamualaikum
Hairul Alam
#yiv8203903492 #yiv8203903492 -- #yiv8203903492ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8203903492
#yiv8203903492ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8203903492
#yiv8203903492ygrp-mkp #yiv8203903492hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8203903492 #yiv8203903492ygrp-mkp #yiv8203903492ads
{margin-bottom:10px;}#yiv8203903492 #yiv8203903492ygrp-mkp .yiv8203903492ad
{padding:0 0;}#yiv8203903492 #yiv8203903492ygrp-mkp .yiv8203903492ad p
{margin:0;}#yiv8203903492 #yiv8203903492ygrp-mkp .yiv8203903492ad a
{color:#0000ff;text-decoration:none;}#yiv8203903492 #yiv8203903492ygrp-sponsor
#yiv8203903492ygrp-lc {font-family:Arial;}#yiv8203903492
#yiv8203903492ygrp-sponsor #yiv8203903492ygrp-lc #yiv8203903492hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8203903492
#yiv8203903492ygrp-sponsor #yiv8203903492ygrp-lc .yiv8203903492ad
{margin-bottom:10px;padding:0 0;}#yiv8203903492 #yiv8203903492actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8203903492
#yiv8203903492activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8203903492
#yiv8203903492activity span {font-weight:700;}#yiv8203903492
#yiv8203903492activity span:first-child
{text-transform:uppercase;}#yiv8203903492 #yiv8203903492activity span a
{color:#5085b6;text-decoration:none;}#yiv8203903492 #yiv8203903492activity span
span {color:#ff7900;}#yiv8203903492 #yiv8203903492activity span
.yiv8203903492underline {text-decoration:underline;}#yiv8203903492
.yiv8203903492attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8203903492 .yiv8203903492attach div a
{text-decoration:none;}#yiv8203903492 .yiv8203903492attach img
{border:none;padding-right:5px;}#yiv8203903492 .yiv8203903492attach label
{display:block;margin-bottom:5px;}#yiv8203903492 .yiv8203903492attach label a
{text-decoration:none;}#yiv8203903492 blockquote {margin:0 0 0
4px;}#yiv8203903492 .yiv8203903492bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8203903492
.yiv8203903492bold a {text-decoration:none;}#yiv8203903492 dd.yiv8203903492last
p a {font-family:Verdana;font-weight:700;}#yiv8203903492 dd...yiv8203903492last
p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8203903492
dd.yiv8203903492last p span.yiv8203903492yshortcuts
{margin-right:0;}#yiv8203903492 div.yiv8203903492attach-table div div a
{text-decoration:none;}#yiv8203903492 div.yiv8203903492attach-table
{width:400px;}#yiv8203903492 div.yiv8203903492file-title a, #yiv8203903492
div.yiv8203903492file-title a:active, #yiv8203903492
div.yiv8203903492file-title a:hover, #yiv8203903492 div.yiv8203903492file-title
a:visited {text-decoration:none;}#yiv8203903492 div.yiv8203903492photo-title a,
#yiv8203903492 div.yiv8203903492photo-title a:active, #yiv8203903492
div.yiv8203903492photo-title a:hover, #yiv8203903492
div.yiv8203903492photo-title a:visited {text-decoration:none;}#yiv8203903492
div#yiv8203903492ygrp-mlmsg #yiv8203903492ygrp-msg p a
span.yiv8203903492yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8203903492
.yiv8203903492green {color:#628c2a;}#yiv8203903492 .yiv8203903492MsoNormal
{margin:0 0 0 0;}#yiv8203903492 o {font-size:0;}#yiv8203903492
#yiv8203903492photos div {float:left;width:72px;}#yiv8203903492
#yiv8203903492photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv8203903492
#yiv8203903492photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8203903492
#yiv8203903492reco-category {font-size:77%;}#yiv8203903492
#yiv8203903492reco-desc {font-size:77%;}#yiv8203903492 .yiv8203903492replbq
{margin:4px;}#yiv8203903492 #yiv8203903492ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8203903492 #yiv8203903492ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8203903492
#yiv8203903492ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8203903492
#yiv8203903492ygrp-mlmsg select, #yiv8203903492 input, #yiv8203903492 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8203903492
#yiv8203903492ygrp-mlmsg pre, #yiv8203903492 code {font:115%
monospace;}#yiv8203903492 #yiv8203903492ygrp-mlmsg *
{line-height:1.22em;}#yiv8203903492 #yiv8203903492ygrp-mlmsg #yiv8203903492logo
{padding-bottom:10px;}#yiv8203903492 #yiv8203903492ygrp-msg p a
{font-family:Verdana;}#yiv8203903492 #yiv8203903492ygrp-msg
p#yiv8203903492attach-count span {color:#1E66AE;font-weight:700;}#yiv8203903492
#yiv8203903492ygrp-reco #yiv8203903492reco-head
{color:#ff7900;font-weight:700;}#yiv8203903492 #yiv8203903492ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8203903492 #yiv8203903492ygrp-sponsor
#yiv8203903492ov li a {font-size:130%;text-decoration:none;}#yiv8203903492
#yiv8203903492ygrp-sponsor #yiv8203903492ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8203903492
#yiv8203903492ygrp-sponsor #yiv8203903492ov ul {margin:0;padding:0 0 0
8px;}#yiv8203903492 #yiv8203903492ygrp-text
{font-family:Georgia;}#yiv8203903492 #yiv8203903492ygrp-text p {margin:0 0 1em
0;}#yiv8203903492 #yiv8203903492ygrp-text tt {font-size:120%;}#yiv8203903492
#yiv8203903492ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8203903492
