[belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

HAIRUL ALAM
SalamPara Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa menampilkan rumus di cell excel, seperti file terlampir

Terimakasih
Assalamualaikum
Hairul Alam

menampilkan hasil formula tanpa menampilkan rumus di cell excel.xlsb (19K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

Stephen Saputro
Kalau hanya sekedar look up, terlalu ribet kalau harus memakai VBA, itu
menurut saya hehe.
Kalau kasus seperti ini masih bisa optimalkan pivot, terus tinggal di
tambahin slicer untuk mempercantik.

1. blok semua table, tekan ctrl + T

[image: image.png]

2. klik my table has header

[image: image.png]

3. tetap di blok table nya, terus pilih insert > pivot table

[image: image.png]

5. Selanjut nya tinggal ikut conth nya, Untuk Slicer, ada di tab Analyse >
Insert Slicer > klik "No"
[image: image.png]

6. Langkah selanjutnya tinggal inpu t data dan refresh data




On Tue, Jul 30, 2019 at 7:47 AM HAIRUL ALAM [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> 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
>
>
>

image.png (4K) Download Attachment
image.png (8K) Download Attachment
image.png (26K) Download Attachment
image.png (9K) Download Attachment
Re-menampilkan hasil formula tanpa menampilkan rumus di cell excel (1).xlsx (31K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

Zainul Ulum
In reply to this post by HAIRUL ALAM
Wa’alaikum salaam,
File terlampir menggunakan protected sheet tanpa menggunakan macro.
Apakah sudah sesuai yang diinginkan?

Wassalam
-zainul


Sent from Mail for Windows 10

From: HAIRUL ALAM [hidden email] [belajar-excel]
Sent: Tuesday, July 30, 2019 7:47 AM
To: [hidden email]
Subject: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

 
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



menampilkan hasil formula tanpa menampilkan rumus di cell excel(sheet protected).xlsb (19K) Download Attachment
BE9264A06F6044B5931FAE4989222DAD.png (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

hendrik karnadi
In reply to this post by HAIRUL ALAM
 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 [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    

SalamPara Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa menampilkan rumus di cell excel, seperti file terlampir

Terimakasih
Assalamualaikum
Hairul Alam    
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

HAIRUL ALAM
In reply to this post by HAIRUL ALAM
 Terimakasih
Maaf yang saya maksud untuk hasilnya tidak muncul formula tapi hasil dari vlookup nya saja seperti yang terlampir sebelumnya dan itu cuma 1 contoh dan jika banyak akan tetap hasil saja
Terimakasih
    Pada Selasa, 30 Juli 2019 12.44.47 WIB, Zainul Ulum [hidden email] [belajar-excel] <[hidden email]> menulis:

  


Wa’alaikum salaam,

File terlampir menggunakan protected sheet tanpa menggunakan macro.

Apakah sudah sesuai yang diinginkan?

  

Wassalam

-zainul

  

  

Sent from Mail for Windows 10

  

From: HAIRUL ALAM [hidden email] [belajar-excel]
Sent: Tuesday, July 30, 2019 7:47 AM
To: [hidden email]
Subject: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

  

 

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



  
  #yiv0658632402 #yiv0658632402 -- #yiv0658632402ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0658632402 #yiv0658632402ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0658632402 #yiv0658632402ygrp-mkp #yiv0658632402hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0658632402 #yiv0658632402ygrp-mkp #yiv0658632402ads {margin-bottom:10px;}#yiv0658632402 #yiv0658632402ygrp-mkp .yiv0658632402ad {padding:0 0;}#yiv0658632402 #yiv0658632402ygrp-mkp .yiv0658632402ad p {margin:0;}#yiv0658632402 #yiv0658632402ygrp-mkp .yiv0658632402ad a {color:#0000ff;text-decoration:none;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ygrp-lc {font-family:Arial;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ygrp-lc #yiv0658632402hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ygrp-lc .yiv0658632402ad {margin-bottom:10px;padding:0 0;}#yiv0658632402 #yiv0658632402actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0658632402 #yiv0658632402activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0658632402 #yiv0658632402activity span {font-weight:700;}#yiv0658632402 #yiv0658632402activity span:first-child {text-transform:uppercase;}#yiv0658632402 #yiv0658632402activity span a {color:#5085b6;text-decoration:none;}#yiv0658632402 #yiv0658632402activity span span {color:#ff7900;}#yiv0658632402 #yiv0658632402activity span .yiv0658632402underline {text-decoration:underline;}#yiv0658632402 .yiv0658632402attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0658632402 .yiv0658632402attach div a {text-decoration:none;}#yiv0658632402 .yiv0658632402attach img {border:none;padding-right:5px;}#yiv0658632402 .yiv0658632402attach label {display:block;margin-bottom:5px;}#yiv0658632402 .yiv0658632402attach label a {text-decoration:none;}#yiv0658632402 blockquote {margin:0 0 0 4px;}#yiv0658632402 .yiv0658632402bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0658632402 .yiv0658632402bold a {text-decoration:none;}#yiv0658632402 dd.yiv0658632402last p a {font-family:Verdana;font-weight:700;}#yiv0658632402 dd.yiv0658632402last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0658632402 dd.yiv0658632402last p span...yiv0658632402yshortcuts {margin-right:0;}#yiv0658632402 div.yiv0658632402attach-table div div a {text-decoration:none;}#yiv0658632402 div.yiv0658632402attach-table {width:400px;}#yiv0658632402 div.yiv0658632402file-title a, #yiv0658632402 div.yiv0658632402file-title a:active, #yiv0658632402 div.yiv0658632402file-title a:hover, #yiv0658632402 div.yiv0658632402file-title a:visited {text-decoration:none;}#yiv0658632402 div.yiv0658632402photo-title a, #yiv0658632402 div.yiv0658632402photo-title a:active, #yiv0658632402 div...yiv0658632402photo-title a:hover, #yiv0658632402 div.yiv0658632402photo-title a:visited {text-decoration:none;}#yiv0658632402 div#yiv0658632402ygrp-mlmsg #yiv0658632402ygrp-msg p a span.yiv0658632402yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0658632402 .yiv0658632402green {color:#628c2a;}#yiv0658632402 .yiv0658632402MsoNormal {margin:0 0 0 0;}#yiv0658632402 o {font-size:0;}#yiv0658632402 #yiv0658632402photos div {float:left;width:72px;}#yiv0658632402 #yiv0658632402photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv0658632402 #yiv0658632402photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0658632402 #yiv0658632402reco-category {font-size:77%;}#yiv0658632402 #yiv0658632402reco-desc {font-size:77%;}#yiv0658632402 .yiv0658632402replbq {margin:4px;}#yiv0658632402 #yiv0658632402ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0658632402 #yiv0658632402ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0658632402 #yiv0658632402ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0658632402 #yiv0658632402ygrp-mlmsg select, #yiv0658632402 input, #yiv0658632402 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0658632402 #yiv0658632402ygrp-mlmsg pre, #yiv0658632402 code {font:115% monospace;}#yiv0658632402 #yiv0658632402ygrp-mlmsg * {line-height:1.22em;}#yiv0658632402 #yiv0658632402ygrp-mlmsg #yiv0658632402logo {padding-bottom:10px;}#yiv0658632402 #yiv0658632402ygrp-msg p a {font-family:Verdana;}#yiv0658632402 #yiv0658632402ygrp-msg p#yiv0658632402attach-count span {color:#1E66AE;font-weight:700;}#yiv0658632402 #yiv0658632402ygrp-reco #yiv0658632402reco-head {color:#ff7900;font-weight:700;}#yiv0658632402 #yiv0658632402ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ov li a {font-size:130%;text-decoration:none;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0658632402 #yiv0658632402ygrp-sponsor #yiv0658632402ov ul {margin:0;padding:0 0 0 8px;}#yiv0658632402 #yiv0658632402ygrp-text {font-family:Georgia;}#yiv0658632402 #yiv0658632402ygrp-text p {margin:0 0 1em 0;}#yiv0658632402 #yiv0658632402ygrp-text tt {font-size:120%;}#yiv0658632402 #yiv0658632402ygrp-vital ul li:last-child {border-right:none !important;}#yiv0658632402
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

Zainul Ulum
Kalo pakai macro, bisa menggunakan code di bawah:
 

 Sub v()
     Const RUMUS_C4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,2,FALSE)"
     Const RUMUS_D4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,3,FALSE)"
     Const KOLOM_ISIAN As String = "B", KOLOM_TANGGAL As String = "C", KOLOM_JUMLAH As String = "D"
     Const barisAwal As Long = 4
     Const barisMax As Long = 10000
     Const shtTarget As String = "b"
     
     
     With Worksheets(shtTarget)
     'mencari baris input terakhir
         Dim n As Long
         n = .Range(KOLOM_ISIAN & barisMax).End(xlUp).Row
     'tentukan range tanggal (kolom c) dan range jumlah (kolom d)
         Dim rgTanggal As Range, rgJumlah As Range
         Set rgTanggal = .Range(KOLOM_TANGGAL & barisAwal & ":" & KOLOM_TANGGAL & n)
         Set rgJumlah = .Range(KOLOM_JUMLAH & barisAwal & ":" & KOLOM_JUMLAH & n)
     'mengisi formula
         rgTanggal.Formula = RUMUS_C4
         rgJumlah.Formula = RUMUS_D4
     'rubah menjadi value
         rgTanggal.Value = rgTanggal.Value
         rgJumlah.Value = rgJumlah.Value
     End With
     
     
 End Sub
 


menampilkan hasil formula tanpa menampilkan rumus di cell excel(macro).xlsm (25K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

HAIRUL ALAM
 Terimakasih Ilmunya Master


    Pada Selasa, 30 Juli 2019 16.14.14 WIB, [hidden email] [belajar-excel] <[hidden email]> menulis:

  

Kalo pakai macro, bisa menggunakan code di bawah:
Sub v()    Const RUMUS_C4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,2,FALSE)"    Const RUMUS_D4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,3,FALSE)"    Const KOLOM_ISIAN As String = "B", KOLOM_TANGGAL As String = "C", KOLOM_JUMLAH As String = "D"    Const barisAwal As Long = 4    Const barisMax As Long = 10000    Const shtTarget As String = "b"            With Worksheets(shtTarget)    'mencari baris input terakhir        Dim n As Long        n = .Range(KOLOM_ISIAN & barisMax).End(xlUp).Row    'tentukan range tanggal (kolom c) dan range jumlah (kolom d)        Dim rgTanggal As Range, rgJumlah As Range        Set rgTanggal = .Range(KOLOM_TANGGAL & barisAwal & ":" & KOLOM_TANGGAL & n)        Set rgJumlah = .Range(KOLOM_JUMLAH & barisAwal & ":" & KOLOM_JUMLAH & n)    'mengisi formula        rgTanggal.Formula = RUMUS_C4        rgJumlah.Formula = RUMUS_D4    'rubah menjadi value        rgTanggal.Value = rgTanggal.Value        rgJumlah.Value = rgJumlah.Value    End With        End Sub
  #yiv5631128258 #yiv5631128258 -- #yiv5631128258ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5631128258 #yiv5631128258ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5631128258 #yiv5631128258ygrp-mkp #yiv5631128258hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5631128258 #yiv5631128258ygrp-mkp #yiv5631128258ads {margin-bottom:10px;}#yiv5631128258 #yiv5631128258ygrp-mkp .yiv5631128258ad {padding:0 0;}#yiv5631128258 #yiv5631128258ygrp-mkp .yiv5631128258ad p {margin:0;}#yiv5631128258 #yiv5631128258ygrp-mkp .yiv5631128258ad a {color:#0000ff;text-decoration:none;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ygrp-lc {font-family:Arial;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ygrp-lc #yiv5631128258hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ygrp-lc .yiv5631128258ad {margin-bottom:10px;padding:0 0;}#yiv5631128258 #yiv5631128258actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5631128258 #yiv5631128258activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5631128258 #yiv5631128258activity span {font-weight:700;}#yiv5631128258 #yiv5631128258activity span:first-child {text-transform:uppercase;}#yiv5631128258 #yiv5631128258activity span a {color:#5085b6;text-decoration:none;}#yiv5631128258 #yiv5631128258activity span span {color:#ff7900;}#yiv5631128258 #yiv5631128258activity span .yiv5631128258underline {text-decoration:underline;}#yiv5631128258 .yiv5631128258attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5631128258 .yiv5631128258attach div a {text-decoration:none;}#yiv5631128258 .yiv5631128258attach img {border:none;padding-right:5px;}#yiv5631128258 .yiv5631128258attach label {display:block;margin-bottom:5px;}#yiv5631128258 .yiv5631128258attach label a {text-decoration:none;}#yiv5631128258 blockquote {margin:0 0 0 4px;}#yiv5631128258 .yiv5631128258bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5631128258 .yiv5631128258bold a {text-decoration:none;}#yiv5631128258 dd.yiv5631128258last p a {font-family:Verdana;font-weight:700;}#yiv5631128258 dd.yiv5631128258last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5631128258 dd.yiv5631128258last p span.yiv5631128258yshortcuts {margin-right:0;}#yiv5631128258 div.yiv5631128258attach-table div div a {text-decoration:none;}#yiv5631128258 div.yiv5631128258attach-table {width:400px;}#yiv5631128258 div.yiv5631128258file-title a, #yiv5631128258 div.yiv5631128258file-title a:active, #yiv5631128258 div.yiv5631128258file-title a:hover, #yiv5631128258 div.yiv5631128258file-title a:visited {text-decoration:none;}#yiv5631128258 div.yiv5631128258photo-title a, #yiv5631128258 div.yiv5631128258photo-title a:active, #yiv5631128258 div.yiv5631128258photo-title a:hover, #yiv5631128258 div.yiv5631128258photo-title a:visited {text-decoration:none;}#yiv5631128258 div#yiv5631128258ygrp-mlmsg #yiv5631128258ygrp-msg p a span.yiv5631128258yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5631128258 .yiv5631128258green {color:#628c2a;}#yiv5631128258 .yiv5631128258MsoNormal {margin:0 0 0 0;}#yiv5631128258 o {font-size:0;}#yiv5631128258 #yiv5631128258photos div {float:left;width:72px;}#yiv5631128258 #yiv5631128258photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv5631128258 #yiv5631128258photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5631128258 #yiv5631128258reco-category {font-size:77%;}#yiv5631128258 #yiv5631128258reco-desc {font-size:77%;}#yiv5631128258 .yiv5631128258replbq {margin:4px;}#yiv5631128258 #yiv5631128258ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5631128258 #yiv5631128258ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5631128258 #yiv5631128258ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5631128258 #yiv5631128258ygrp-mlmsg select, #yiv5631128258 input, #yiv5631128258 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5631128258 #yiv5631128258ygrp-mlmsg pre, #yiv5631128258 code {font:115% monospace;}#yiv5631128258 #yiv5631128258ygrp-mlmsg * {line-height:1.22em;}#yiv5631128258 #yiv5631128258ygrp-mlmsg #yiv5631128258logo {padding-bottom:10px;}#yiv5631128258 #yiv5631128258ygrp-msg p a {font-family:Verdana;}#yiv5631128258 #yiv5631128258ygrp-msg p#yiv5631128258attach-count span {color:#1E66AE;font-weight:700;}#yiv5631128258 #yiv5631128258ygrp-reco #yiv5631128258reco-head {color:#ff7900;font-weight:700;}#yiv5631128258 #yiv5631128258ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ov li a {font-size:130%;text-decoration:none;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5631128258 #yiv5631128258ygrp-sponsor #yiv5631128258ov ul {margin:0;padding:0 0 0 8px;}#yiv5631128258 #yiv5631128258ygrp-text {font-family:Georgia;}#yiv5631128258 #yiv5631128258ygrp-text p {margin:0 0 1em 0;}#yiv5631128258 #yiv5631128258ygrp-text tt {font-size:120%;}#yiv5631128258 #yiv5631128258ygrp-vital ul li:last-child {border-right:none !important;}#yiv5631128258  
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

HAIRUL ALAM
In reply to this post by hendrik karnadi
 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 bayakMaaf, jika saya kurang paham masalah code macro    Pada Selasa, 30 Juli 2019 14.04.04 WIB, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> menulis:

  

SalamPara Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa menampilkan rumus di cell excel, seperti file terlampir

Terimakasih
Assalamualaikum
Hairul Alam      #yiv3589765478 #yiv3589765478 -- #yiv3589765478ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3589765478 #yiv3589765478ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3589765478 #yiv3589765478ygrp-mkp #yiv3589765478hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3589765478 #yiv3589765478ygrp-mkp #yiv3589765478ads {margin-bottom:10px;}#yiv3589765478 #yiv3589765478ygrp-mkp .yiv3589765478ad {padding:0 0;}#yiv3589765478 #yiv3589765478ygrp-mkp .yiv3589765478ad p {margin:0;}#yiv3589765478 #yiv3589765478ygrp-mkp .yiv3589765478ad a {color:#0000ff;text-decoration:none;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ygrp-lc {font-family:Arial;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ygrp-lc #yiv3589765478hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ygrp-lc .yiv3589765478ad {margin-bottom:10px;padding:0 0;}#yiv3589765478 #yiv3589765478actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3589765478 #yiv3589765478activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3589765478 #yiv3589765478activity span {font-weight:700;}#yiv3589765478 #yiv3589765478activity span:first-child {text-transform:uppercase;}#yiv3589765478 #yiv3589765478activity span a {color:#5085b6;text-decoration:none;}#yiv3589765478 #yiv3589765478activity span span {color:#ff7900;}#yiv3589765478 #yiv3589765478activity span .yiv3589765478underline {text-decoration:underline;}#yiv3589765478 .yiv3589765478attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3589765478 .yiv3589765478attach div a {text-decoration:none;}#yiv3589765478 .yiv3589765478attach img {border:none;padding-right:5px;}#yiv3589765478 .yiv3589765478attach label {display:block;margin-bottom:5px;}#yiv3589765478 .yiv3589765478attach label a {text-decoration:none;}#yiv3589765478 blockquote {margin:0 0 0 4px;}#yiv3589765478 .yiv3589765478bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3589765478 .yiv3589765478bold a {text-decoration:none;}#yiv3589765478 dd.yiv3589765478last p a {font-family:Verdana;font-weight:700;}#yiv3589765478 dd.yiv3589765478last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3589765478 dd.yiv3589765478last p span.yiv3589765478yshortcuts {margin-right:0;}#yiv3589765478 div.yiv3589765478attach-table div div a {text-decoration:none;}#yiv3589765478 div.yiv3589765478attach-table {width:400px;}#yiv3589765478 div.yiv3589765478file-title a, #yiv3589765478 div.yiv3589765478file-title a:active, #yiv3589765478 div.yiv3589765478file-title a:hover, #yiv3589765478 div.yiv3589765478file-title a:visited {text-decoration:none;}#yiv3589765478 div.yiv3589765478photo-title a, #yiv3589765478 div.yiv3589765478photo-title a:active, #yiv3589765478 div.yiv3589765478photo-title a:hover, #yiv3589765478 div.yiv3589765478photo-title a:visited {text-decoration:none;}#yiv3589765478 div#yiv3589765478ygrp-mlmsg #yiv3589765478ygrp-msg p a span.yiv3589765478yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3589765478 .yiv3589765478green {color:#628c2a;}#yiv3589765478 .yiv3589765478MsoNormal {margin:0 0 0 0;}#yiv3589765478 o {font-size:0;}#yiv3589765478 #yiv3589765478photos div {float:left;width:72px;}#yiv3589765478 #yiv3589765478photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3589765478 #yiv3589765478photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3589765478 #yiv3589765478reco-category {font-size:77%;}#yiv3589765478 #yiv3589765478reco-desc {font-size:77%;}#yiv3589765478 .yiv3589765478replbq {margin:4px;}#yiv3589765478 #yiv3589765478ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3589765478 #yiv3589765478ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3589765478 #yiv3589765478ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3589765478 #yiv3589765478ygrp-mlmsg select, #yiv3589765478 input, #yiv3589765478 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3589765478 #yiv3589765478ygrp-mlmsg pre, #yiv3589765478 code {font:115% monospace;}#yiv3589765478 #yiv3589765478ygrp-mlmsg * {line-height:1.22em;}#yiv3589765478 #yiv3589765478ygrp-mlmsg #yiv3589765478logo {padding-bottom:10px;}#yiv3589765478 #yiv3589765478ygrp-msg p a {font-family:Verdana;}#yiv3589765478 #yiv3589765478ygrp-msg p#yiv3589765478attach-count span {color:#1E66AE;font-weight:700;}#yiv3589765478 #yiv3589765478ygrp-reco #yiv3589765478reco-head {color:#ff7900;font-weight:700;}#yiv3589765478 #yiv3589765478ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ov li a {font-size:130%;text-decoration:none;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3589765478 #yiv3589765478ygrp-sponsor #yiv3589765478ov ul {margin:0;padding:0 0 0 8px;}#yiv3589765478 #yiv3589765478ygrp-text {font-family:Georgia;}#yiv3589765478 #yiv3589765478ygrp-text p {margin:0 0 1em 0;}#yiv3589765478 #yiv3589765478ygrp-text tt {font-size:120%;}#yiv3589765478 #yiv3589765478ygrp-vital ul li:last-child {border-right:none !important;}#yiv3589765478
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro

hendrik karnadi
 Langsung ke TKP aja ya di file terlampir.
Salam,HK
    Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 bayakMaaf, jika saya kurang paham masalah code macro    Pada Selasa, 30 Juli 2019 14.04.04 WIB, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    

SalamPara Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa menampilkan rumus di cell excel, seperti file terlampir

Terimakasih
Assalamualaikum
Hairul Alam            

=?UTF-8?b?cmVfbWVuYW1waWxrYW4gaGFzaWwgZm9ybXVsYSB0YW5wYSAgbWVuYW1waWxrYW4gcnVtdXMgZGkgY2VsbCBleGNlbC54bHNi?= (22K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel dengan script macro [1 Attachment]

HAIRUL ALAM
 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 [hidden email] [belajar-excel] <[hidden email]> menulis:

  



 Langsung ke TKP aja ya di file terlampir.
Salam,HK
    Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 bayakMaaf, jika saya kurang paham masalah code macro    Pada Selasa, 30 Juli 2019 14.04.04 WIB, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> 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 ha_mj76@yahoo...co.id [belajar-excel] <[hidden email]> menulis:

  

SalamPara Master
mohon pencerahahannya dengan code script macro menampilkan hasil formula tanpa menampilkan rumus di cell excel, seperti file terlampir

Terimakasih
Assalamualaikum
Hairul Alam              #yiv6507900507 -- #yiv6507900507ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6507900507 #yiv6507900507ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6507900507 #yiv6507900507ygrp-mkp #yiv6507900507hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6507900507 #yiv6507900507ygrp-mkp #yiv6507900507ads {margin-bottom:10px;}#yiv6507900507 #yiv6507900507ygrp-mkp .yiv6507900507ad {padding:0 0;}#yiv6507900507 #yiv6507900507ygrp-mkp .yiv6507900507ad p {margin:0;}#yiv6507900507 #yiv6507900507ygrp-mkp .yiv6507900507ad a {color:#0000ff;text-decoration:none;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ygrp-lc {font-family:Arial;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ygrp-lc #yiv6507900507hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ygrp-lc .yiv6507900507ad {margin-bottom:10px;padding:0 0;}#yiv6507900507 #yiv6507900507actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6507900507 #yiv6507900507activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6507900507 #yiv6507900507activity span {font-weight:700;}#yiv6507900507 #yiv6507900507activity span:first-child {text-transform:uppercase;}#yiv6507900507 #yiv6507900507activity span a {color:#5085b6;text-decoration:none;}#yiv6507900507 #yiv6507900507activity span span {color:#ff7900;}#yiv6507900507 #yiv6507900507activity span .yiv6507900507underline {text-decoration:underline;}#yiv6507900507 .yiv6507900507attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6507900507 .yiv6507900507attach div a {text-decoration:none;}#yiv6507900507 .yiv6507900507attach img {border:none;padding-right:5px;}#yiv6507900507 .yiv6507900507attach label {display:block;margin-bottom:5px;}#yiv6507900507 .yiv6507900507attach label a {text-decoration:none;}#yiv6507900507 blockquote {margin:0 0 0 4px;}#yiv6507900507 .yiv6507900507bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6507900507 .yiv6507900507bold a {text-decoration:none;}#yiv6507900507 dd.yiv6507900507last p a {font-family:Verdana;font-weight:700;}#yiv6507900507 dd.yiv6507900507last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6507900507 dd.yiv6507900507last p span.yiv6507900507yshortcuts {margin-right:0;}#yiv6507900507 div.yiv6507900507attach-table div div a {text-decoration:none;}#yiv6507900507 div.yiv6507900507attach-table {width:400px;}#yiv6507900507 div.yiv6507900507file-title a, #yiv6507900507 div.yiv6507900507file-title a:active, #yiv6507900507 div.yiv6507900507file-title a:hover, #yiv6507900507 div.yiv6507900507file-title a:visited {text-decoration:none;}#yiv6507900507 div.yiv6507900507photo-title a, #yiv6507900507 div.yiv6507900507photo-title a:active, #yiv6507900507 div.yiv6507900507photo-title a:hover, #yiv6507900507 div.yiv6507900507photo-title a:visited {text-decoration:none;}#yiv6507900507 div#yiv6507900507ygrp-mlmsg #yiv6507900507ygrp-msg p a span.yiv6507900507yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6507900507 .yiv6507900507green {color:#628c2a;}#yiv6507900507 .yiv6507900507MsoNormal {margin:0 0 0 0;}#yiv6507900507 o {font-size:0;}#yiv6507900507 #yiv6507900507photos div {float:left;width:72px;}#yiv6507900507 #yiv6507900507photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv6507900507 #yiv6507900507photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6507900507 #yiv6507900507reco-category {font-size:77%;}#yiv6507900507 #yiv6507900507reco-desc {font-size:77%;}#yiv6507900507 .yiv6507900507replbq {margin:4px;}#yiv6507900507 #yiv6507900507ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6507900507 #yiv6507900507ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6507900507 #yiv6507900507ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6507900507 #yiv6507900507ygrp-mlmsg select, #yiv6507900507 input, #yiv6507900507 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6507900507 #yiv6507900507ygrp-mlmsg pre, #yiv6507900507 code {font:115% monospace;}#yiv6507900507 #yiv6507900507ygrp-mlmsg * {line-height:1.22em;}#yiv6507900507 #yiv6507900507ygrp-mlmsg #yiv6507900507logo {padding-bottom:10px;}#yiv6507900507 #yiv6507900507ygrp-msg p a {font-family:Verdana;}#yiv6507900507 #yiv6507900507ygrp-msg p#yiv6507900507attach-count span {color:#1E66AE;font-weight:700;}#yiv6507900507 #yiv6507900507ygrp-reco #yiv6507900507reco-head {color:#ff7900;font-weight:700;}#yiv6507900507 #yiv6507900507ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ov li a {font-size:130%;text-decoration:none;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv6507900507 #yiv6507900507ygrp-sponsor #yiv6507900507ov ul {margin:0;padding:0 0 0 8px;}#yiv6507900507 #yiv6507900507ygrp-text {font-family:Georgia;}#yiv6507900507 #yiv6507900507ygrp-text p {margin:0 0 1em 0;}#yiv6507900507 #yiv6507900507ygrp-text tt {font-size:120%;}#yiv6507900507 #yiv6507900507ygrp-vital ul li:last-child {border-right:none !important;}#yiv6507900507

menampilkan hasil di cell excel tanpa rumus dengan macro.xlsm (564K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] menampilkan hasil tanpa formula di excel denganscript macro

Zainul Ulum
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 [hidden email] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:


 



Langsung ke TKP aja ya di file terlampir.

Salam,
HK

Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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



A39FC8227CF1454DA9FB9F278E66CB3A.png (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] menampilkan hasil tanpa formula di excel denganscript macro

Zainul Ulum
In reply to this post by HAIRUL ALAM
Sambil menunggu penjelasan rumsunya,
Silakan dipelajari macro terlampir, semoga sesuai harapan.
Wassalam
-zainul


Sent from Mail for Windows 10

From: Zainul Ulum [hidden email] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [hidden email]
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 [hidden email] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:
 
 
 
 


Langsung ke TKP aja ya di file terlampir.
 
Salam,
HK
 
Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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
 



menampilkan hasil di cell excel tanpa rumus dengan macro(znl).xlsm (60K) Download Attachment
1EB2CFEE00704D2FB4B27DDC286CD1F3.png (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel denganscript macro

HAIRUL ALAM
In reply to this post by HAIRUL ALAM
 Terimakasih Master,
Maksud rumusnya seperti yang saya cantumkan di module macropada 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 [hidden email] [belajar-excel] <[hidden email]> menulis:

  


Sambil menunggu penjelasan rumsunya,

Silakan dipelajari macro terlampir, semoga sesuai harapan.

Wassalam

-zainul

  

  

Sent from Mail for Windows 10

  

From: Zainul Ulum [hidden email] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [hidden email]
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 [hidden email] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:

 

 

 

 




Langsung ke TKP aja ya di file terlampir.

 

Salam,

HK

 

Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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

 



  
  #yiv4896027138 #yiv4896027138 -- #yiv4896027138ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4896027138 #yiv4896027138ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4896027138 #yiv4896027138ygrp-mkp #yiv4896027138hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4896027138 #yiv4896027138ygrp-mkp #yiv4896027138ads {margin-bottom:10px;}#yiv4896027138 #yiv4896027138ygrp-mkp .yiv4896027138ad {padding:0 0;}#yiv4896027138 #yiv4896027138ygrp-mkp .yiv4896027138ad p {margin:0;}#yiv4896027138 #yiv4896027138ygrp-mkp .yiv4896027138ad a {color:#0000ff;text-decoration:none;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ygrp-lc {font-family:Arial;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ygrp-lc #yiv4896027138hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ygrp-lc .yiv4896027138ad {margin-bottom:10px;padding:0 0;}#yiv4896027138 #yiv4896027138actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4896027138 #yiv4896027138activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4896027138 #yiv4896027138activity span {font-weight:700;}#yiv4896027138 #yiv4896027138activity span:first-child {text-transform:uppercase;}#yiv4896027138 #yiv4896027138activity span a {color:#5085b6;text-decoration:none;}#yiv4896027138 #yiv4896027138activity span span {color:#ff7900;}#yiv4896027138 #yiv4896027138activity span .yiv4896027138underline {text-decoration:underline;}#yiv4896027138 .yiv4896027138attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4896027138 .yiv4896027138attach div a {text-decoration:none;}#yiv4896027138 .yiv4896027138attach img {border:none;padding-right:5px;}#yiv4896027138 .yiv4896027138attach label {display:block;margin-bottom:5px;}#yiv4896027138 .yiv4896027138attach label a {text-decoration:none;}#yiv4896027138 blockquote {margin:0 0 0 4px;}#yiv4896027138 .yiv4896027138bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4896027138 .yiv4896027138bold a {text-decoration:none;}#yiv4896027138 dd.yiv4896027138last p a {font-family:Verdana;font-weight:700;}#yiv4896027138 dd.yiv4896027138last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4896027138 dd.yiv4896027138last p span.yiv4896027138yshortcuts {margin-right:0;}#yiv4896027138 div.yiv4896027138attach-table div div a {text-decoration:none;}#yiv4896027138 div.yiv4896027138attach-table {width:400px;}#yiv4896027138 div.yiv4896027138file-title a, #yiv4896027138 div.yiv4896027138file-title a:active, #yiv4896027138 div.yiv4896027138file-title a:hover, #yiv4896027138 div.yiv4896027138file-title a:visited {text-decoration:none;}#yiv4896027138 div.yiv4896027138photo-title a, #yiv4896027138 div.yiv4896027138photo-title a:active, #yiv4896027138 div.yiv4896027138photo-title a:hover, #yiv4896027138 div.yiv4896027138photo-title a:visited {text-decoration:none;}#yiv4896027138 div#yiv4896027138ygrp-mlmsg #yiv4896027138ygrp-msg p a span.yiv4896027138yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4896027138 .yiv4896027138green {color:#628c2a;}#yiv4896027138 .yiv4896027138MsoNormal {margin:0 0 0 0;}#yiv4896027138 o {font-size:0;}#yiv4896027138 #yiv4896027138photos div {float:left;width:72px;}#yiv4896027138 #yiv4896027138photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv4896027138 #yiv4896027138photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4896027138 #yiv4896027138reco-category {font-size:77%;}#yiv4896027138 #yiv4896027138reco-desc {font-size:77%;}#yiv4896027138 .yiv4896027138replbq {margin:4px;}#yiv4896027138 #yiv4896027138ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4896027138 #yiv4896027138ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4896027138 #yiv4896027138ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4896027138 #yiv4896027138ygrp-mlmsg select, #yiv4896027138 input, #yiv4896027138 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4896027138 #yiv4896027138ygrp-mlmsg pre, #yiv4896027138 code {font:115% monospace;}#yiv4896027138 #yiv4896027138ygrp-mlmsg * {line-height:1.22em;}#yiv4896027138 #yiv4896027138ygrp-mlmsg #yiv4896027138logo {padding-bottom:10px;}#yiv4896027138 #yiv4896027138ygrp-msg p a {font-family:Verdana;}#yiv4896027138 #yiv4896027138ygrp-msg p#yiv4896027138attach-count span {color:#1E66AE;font-weight:700;}#yiv4896027138 #yiv4896027138ygrp-reco #yiv4896027138reco-head {color:#ff7900;font-weight:700;}#yiv4896027138 #yiv4896027138ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ov li a {font-size:130%;text-decoration:none;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4896027138 #yiv4896027138ygrp-sponsor #yiv4896027138ov ul {margin:0;padding:0 0 0 8px;}#yiv4896027138 #yiv4896027138ygrp-text {font-family:Georgia;}#yiv4896027138 #yiv4896027138ygrp-text p {margin:0 0 1em 0;}#yiv4896027138 #yiv4896027138ygrp-text tt {font-size:120%;}#yiv4896027138 #yiv4896027138ygrp-vital ul li:last-child {border-right:none !important;}#yiv4896027138
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di excel denganscript macro

Kid
Administrator
In reply to this post by HAIRUL ALAM
wkwkwk...
panjang juga trit ini...

dari files terlampir, saya lebih suka gak pake copy formula, tapi pakai
pivot table (pivot consolidation range) seperti di file lampiran ke-2
tapi itu kalo saya loh ya...

Regards,
Kid






On Mon, Aug 5, 2019 at 11:59 PM Zainul Ulum [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Sambil menunggu penjelasan rumsunya,
>
> Silakan dipelajari macro terlampir, semoga sesuai harapan.
>
> Wassalam
>
> -zainul
>
>
>
>
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *Zainul Ulum [hidden email] [belajar-excel]
> <[hidden email]>
> *Sent: *Monday, August 5, 2019 11:19 PM
> *To: *[hidden email]
> *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 <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *HAIRUL ALAM [hidden email] [belajar-excel]
> <[hidden email]>
> *Sent: *Saturday, August 3, 2019 4:35 PM
> *To: *[hidden email]
> *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
> [hidden email] [belajar-excel] <[hidden email]>
> menulis:
>
>
>
>
>
>
>
>
>
>
> Langsung ke TKP aja ya di file terlampir.
>
>
>
> Salam,
>
> HK
>
>
>
> Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email]
> [belajar-excel] <[hidden email]> 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
> [hidden email] [belajar-excel] <[hidden email]>
> 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 [hidden email]
> [belajar-excel] <[hidden email]> 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
>
>
>
>
>
>
>

vbacopasval.xlsm (30K) Download Attachment
re-menampilkan blabla dengan macro.xlsm (37K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] menampilkan hasil tanpa formula di exceldenganscript macro

Zainul Ulum
In reply to this post by HAIRUL ALAM
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 [hidden email] [belajar-excel]
Sent: Tuesday, August 6, 2019 4:30 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:


 
Sambil menunggu penjelasan rumsunya,
Silakan dipelajari macro terlampir, semoga sesuai harapan.
Wassalam
-zainul
 
 
Sent from Mail for Windows 10
 
From: Zainul Ulum [hidden email] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [hidden email]
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 [hidden email] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:
 
 
 
 


Langsung ke TKP aja ya di file terlampir.
 
Salam,
HK
 
Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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
 
 



23BF0537AB034C47B4D96DDFF5E657E0.png (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di exceldenganscript macro

HAIRUL ALAM
In reply to this post by HAIRUL ALAM
 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel]
Sent: Tuesday, August 6, 2019 4:30 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:

  

  

 

Sambil menunggu penjelasan rumsunya,

Silakan dipelajari macro terlampir, semoga sesuai harapan.

Wassalam

-zainul

 

 

Sent from Mail for Windows 10

 

From: Zainul Ulum [hidden email] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [hidden email]
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 [hidden email] [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:

 

 

 

 




Langsung ke TKP aja ya di file terlampir.

 

Salam,

HK

 

Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] menampilkan hasil tanpa formula di exceldenganscript macro

HAIRUL ALAM
In reply to this post by HAIRUL ALAM
 Salam Mas Zainul
Maaf
Terimakasih Mas, rumus B2=REPT(1,COUNTIFS(INPUT!$C2:$P2,B$1)>0)
bisa digunakan, saya kurang perhatikan pada range B$1 yang ada pada rumus, Terimakasih rumus ini cukup sederhana
Assalamualaikum
    Pada Selasa, 6 Agustus 2019 21.23.39 GMT+7, Zainul Ulum [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel]
Sent: Tuesday, August 6, 2019 4:30 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:

  

  

 

Sambil menunggu penjelasan rumsunya,

Silakan dipelajari macro terlampir, semoga sesuai harapan.

Wassalam

-zainul

 

 

Sent from Mail for Windows 10

 

From: Zainul Ulum [hidden email] [belajar-excel]
Sent: Monday, August 5, 2019 11:19 PM
To: [hidden email]
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 ha_mj76@yahoo...co.id [belajar-excel]
Sent: Saturday, August 3, 2019 4:35 PM
To: [hidden email]
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 [hidden email] [belajar-excel] <[hidden email]> menulis:

 

 

 

 




Langsung ke TKP aja ya di file terlampir.

 

Salam,

HK

 

Pada Jumat, 2 Agustus 2019 19.32.08 WIB, HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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

 

 



  
  #yiv3389779990 #yiv3389779990 -- #yiv3389779990ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3389779990 #yiv3389779990ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3389779990 #yiv3389779990ygrp-mkp #yiv3389779990hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3389779990 #yiv3389779990ygrp-mkp #yiv3389779990ads {margin-bottom:10px;}#yiv3389779990 #yiv3389779990ygrp-mkp .yiv3389779990ad {padding:0 0;}#yiv3389779990 #yiv3389779990ygrp-mkp ...yiv3389779990ad p {margin:0;}#yiv3389779990 #yiv3389779990ygrp-mkp .yiv3389779990ad a {color:#0000ff;text-decoration:none;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ygrp-lc {font-family:Arial;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ygrp-lc #yiv3389779990hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ygrp-lc .yiv3389779990ad {margin-bottom:10px;padding:0 0;}#yiv3389779990 #yiv3389779990actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3389779990 #yiv3389779990activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3389779990 #yiv3389779990activity span {font-weight:700;}#yiv3389779990 #yiv3389779990activity span:first-child {text-transform:uppercase;}#yiv3389779990 #yiv3389779990activity span a {color:#5085b6;text-decoration:none;}#yiv3389779990 #yiv3389779990activity span span {color:#ff7900;}#yiv3389779990 #yiv3389779990activity span .yiv3389779990underline {text-decoration:underline;}#yiv3389779990 .yiv3389779990attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3389779990 .yiv3389779990attach div a {text-decoration:none;}#yiv3389779990 .yiv3389779990attach img {border:none;padding-right:5px;}#yiv3389779990 .yiv3389779990attach label {display:block;margin-bottom:5px;}#yiv3389779990 .yiv3389779990attach label a {text-decoration:none;}#yiv3389779990 blockquote {margin:0 0 0 4px;}#yiv3389779990 .yiv3389779990bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3389779990 .yiv3389779990bold a {text-decoration:none;}#yiv3389779990 dd.yiv3389779990last p a {font-family:Verdana;font-weight:700;}#yiv3389779990 dd.yiv3389779990last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3389779990 dd.yiv3389779990last p span.yiv3389779990yshortcuts {margin-right:0;}#yiv3389779990 div.yiv3389779990attach-table div div a {text-decoration:none;}#yiv3389779990 div.yiv3389779990attach-table {width:400px;}#yiv3389779990 div.yiv3389779990file-title a, #yiv3389779990 div.yiv3389779990file-title a:active, #yiv3389779990 div.yiv3389779990file-title a:hover, #yiv3389779990 div.yiv3389779990file-title a:visited {text-decoration:none;}#yiv3389779990 div.yiv3389779990photo-title a, #yiv3389779990 div.yiv3389779990photo-title a:active, #yiv3389779990 div.yiv3389779990photo-title a:hover, #yiv3389779990 div.yiv3389779990photo-title a:visited {text-decoration:none;}#yiv3389779990 div#yiv3389779990ygrp-mlmsg #yiv3389779990ygrp-msg p a span.yiv3389779990yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3389779990 .yiv3389779990green {color:#628c2a;}#yiv3389779990 .yiv3389779990MsoNormal {margin:0 0 0 0;}#yiv3389779990 o {font-size:0;}#yiv3389779990 #yiv3389779990photos div {float:left;width:72px;}#yiv3389779990 #yiv3389779990photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3389779990 #yiv3389779990photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3389779990 #yiv3389779990reco-category {font-size:77%;}#yiv3389779990 #yiv3389779990reco-desc {font-size:77%;}#yiv3389779990 .yiv3389779990replbq {margin:4px;}#yiv3389779990 #yiv3389779990ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3389779990 #yiv3389779990ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3389779990 #yiv3389779990ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3389779990 #yiv3389779990ygrp-mlmsg select, #yiv3389779990 input, #yiv3389779990 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3389779990 #yiv3389779990ygrp-mlmsg pre, #yiv3389779990 code {font:115% monospace;}#yiv3389779990 #yiv3389779990ygrp-mlmsg * {line-height:1.22em;}#yiv3389779990 #yiv3389779990ygrp-mlmsg #yiv3389779990logo {padding-bottom:10px;}#yiv3389779990 #yiv3389779990ygrp-msg p a {font-family:Verdana;}#yiv3389779990 #yiv3389779990ygrp-msg p#yiv3389779990attach-count span {color:#1E66AE;font-weight:700;}#yiv3389779990 #yiv3389779990ygrp-reco #yiv3389779990reco-head {color:#ff7900;font-weight:700;}#yiv3389779990 #yiv3389779990ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ov li a {font-size:130%;text-decoration:none;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3389779990 #yiv3389779990ygrp-sponsor #yiv3389779990ov ul {margin:0;padding:0 0 0 8px;}#yiv3389779990 #yiv3389779990ygrp-text {font-family:Georgia;}#yiv3389779990 #yiv3389779990ygrp-text p {margin:0 0 1em 0;}#yiv3389779990 #yiv3389779990ygrp-text tt {font-size:120%;}#yiv3389779990 #yiv3389779990ygrp-vital ul li:last-child {border-right:none !important;}#yiv3389779990