Wa'alaikumussalam wr wb
Hai Hairul,
Coba tambahkan proses untuk mem-values hasil formula. Bisa berupa copy
paste values atau set value dengan value yang ada.
Contoh : formula berisi fungsi vLookUp dan IFError yang berbunyi :
=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),"")
akan dipasang pada area range : e2:e20000 di sheet bernama 'data'
'paksa kalkulasi excel automatic bila tidak mengurangi kecepatan proses
'application.screenupdating=false 'jika proses terasa melambat
karena refresh layar, aktifkan baris ini
application.calculation=xlCalculationAutomatic 'jika prosesnya
melambat, ganti dengan manual xlcalculationmanual
'pasang formula
sheets("data").range("e2:e20000").formula="=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),
"""")"
'cara merujuk range di atas bisa juga menggunakan range("e2" ,
"e20000" )
'sheets("data").calculate 'jika calculation di set manual,
aktifkan baris ini agar formula di kalkulasi excel
'proses mem-values formula di dalam cells
sheets("data").range("e2:e20000").value=sheets("data").range("e2:e20000").value
'tidak pakai Selection karena tidak ada cells yang di-select
selama proses ini
'bisa juga dengan proses copy lalu paste values dengan kode
berikut : (aktifkan bila akan digunakan)
'sheets("data").range("e2:e20000").copy 'proses copy
'sheets("data").range("e2:e20000").pastespecial xlpastevalues
'bisa pilih opsi lain, seperti values n number format, dsb
'kalau pakai proses copas, kosongkan clipboard dengan :
'application.cutcopymode=false
'kalau calculation di atas sana di set manual, set automatic disini
'application.calculation=xlCalculationAutomatic
'kalau screenupdating diaktifkan untuk diset false di atas sana, maka
kembalikan lagi ke true
'application.screenupdating=true
'selesai
Andai diperhatikan dengan lebih seksama, bagian 2 baris ini :
sheets("data").range("e2:e20000").formula="=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),
"""")"
sheets("data").range("e2:e20000").value=sheets("data").range("e2:e20000").value
bisa dinaikkan sedikit performanya dengan memanfaatkan blok with. Syntax
with :
with objectnya
'pemanfaatan object disini
end with
untuk contoh baris di atas :
with sheets("data").range("e2:e20000")
.formula="=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),"""")"
.value=.value
end with
jadi lebih simpel dan meningkat dikit performanya.
Kalau proses pemasangan nilai hasil vlookup dengan methods Evaluate, maka
harus diperintah satu per satu langsung dari VBA alias tidak bisa langsung
banyak cells.
Pemasangan formula di atas juga dilakukan satu per satu, tetapi dilakukan
oleh runtime di dalamnya si Excel dan bukan oleh si script VBA buatan user,
sehingga terkesan lebih cepat.
Pasang pakai evaluate dilakukan dengan loop.
dim lRow as long
for lRow=2 to 20000
sheets("data").cells(lrow,"e").value=evaluate("=IFERROR(VLOOKUP(D" &
lrow & ",PIPE!$B$3:$L$20000,2,FALSE),"""")")
next lrow
Jika akan memanfaatkan methods worksheetfunction alias sarana untuk
memanfaatkan fungsi-fungsi bawaan Excel dari VBA, maka bagian kode di atas
sana yang berbunyi :
sheets("data").range("e2:e20000").formula="=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),
"""")"
sheets("data").range("e2:e20000").value=sheets("data").range("e2:e20000").value
bisa diganti dengan :
sheets("data").range("e2:e20000").value = _
application.worksheetfunction.iferror( _
application.worksheetfunction.vlookup(
sheets("data").range("d2:d20000") _
, sheets("pipe").range("b3:L20000") _
, 2 , 0 ) _
, vbnullstring )
Jika akan memanfaatkan array formula, baris kode yang di atas sana yang
berbunyi :
sheets("data").range("e2:e20000").formula="=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),
"""")"
sheets("data").range("e2:e20000").value=sheets("data").range("e2:e20000").value
bisa diubah menjadi :
sheets("data").range("e2:e20000").formulaarray="=IFERROR(VLOOKUP(
D2:d20000,PIPE!$B$3:$L$20000,2,FALSE),"""")"
sheets("data").range("e2:e20000").value=sheets("data").range("e2:e20000").value
Kira-kiranya begitu...
Wassalamu'alaikum wr wb
Kid
On Wed, Nov 11, 2015 at 5:44 PM, HAIRUL ALAM [email protected]
[belajar-excel] <[email protected]> wrote:
>
>
> Terimakasih para Master
>
> Cara ini
> Sub TRACE_HEAT()
> Worksheets("DATA").Range("E2").Value = Evaluate("=IFERROR(VLOOKUP(D2,MAIN!
> $B$3:$L$5,2,FALSE),"""")")
> End Sub
>
> berhasil namun harus masukkan formula per satu2, apakah bisa bila rangenya
> banyak misal yang saya beri warna kuning agar seperti range
>
> (E2) menjadi E2 sd E20000 dan $B$3:$L$5 menjadi $B$3 sd $L$20000
>
> Maaf & Terimakasih
>
>
>
> Pada Rabu, 11 November 2015 17:27, "Hendrik Karnadi
> [email protected] [belajar-excel]" <[email protected]>
> menulis:
>
>
>
> Coba ganti:
> Formula......
> dengan
> Value = Evaluate ("formulanya")
> Formualanya tidak perlu diberi tanda =(sama dengan) lagi.
>
> Salam,
> HK
>
> Sent from Samsung Mobile
>
>
> -------- Original message --------
> From: "HAIRUL ALAM [email protected] [belajar-excel]"
> Date:11/11/2015 4:11 PM (GMT+07:00)
> To: [email protected]
> Subject: [belajar-excel] code Macro tanpa memunculkan formula pada
> worksheet
>
>
> Assalamualaikum
>
> Yth. Para Master
>
> Maaf Saya ingin bertanya, saya mempunyai masalah tentang code VBA, yang
> saya ingin agar formula tidak muncul pada cell worksheet misalkan code VBA
> sebagai berikut :
> Sub NO_HEAT()
> Worksheets("DATA").Range("E2", "E20000").Formula =
> "=IFERROR(VLOOKUP(D2,PIPE!$B$3:$L$20000,2,FALSE),"""")"
> End Sub
>
> bagaimana codenya agar yang dimunculkan pada worksheet adalah hasil dari
> perhitungan sesuai code VBA diatas dan tidak memunculkan formula pada cell
> worksheet
>
> Terimakasih Banyak
>
> Maaf
> Hairul Alam
>
> Wassalamualaikum
>
>
>
>
