Pak Kid.
dengan data sebanyak 506.286 row.
Tes_14 waktunya 9 detik.
Tes_12 waktunya sudah 30 menit belum selesai.
Sub Tes_14()
Range("A2").Select
Set Rng = Range("A2", Range("A1000000").End(xlUp))
Rng.Offset(0, 20).Formula = "=B2*C2"
Rng.Offset(0, 21).Formula = "=D2*E2"
Rng.Offset(0, 22).Formula = "=B2*D2"
Rng.Offset(0, 23).Formula = "=C2*E2"
Rng.Offset(0, 20).Value = Rng.Offset(0, 20).Value
Rng.Offset(0, 21).Value = Rng.Offset(0, 21).Value
Rng.Offset(0, 22).Value = Rng.Offset(0, 22).Value
Rng.Offset(0, 23).Value = Rng.Offset(0, 23).Value
End Sub
Sub tes_12()
For r = 2 To s
Cells(r, 21).Value = Cells(r, 2) * Cells(r, 3)
Cells(r, 22).Value = Cells(r, 4) * Cells(r, 5)
Cells(r, 23).Value = Cells(r, 2) * Cells(r, 4)
Cells(r, 24).Value = Cells(r, 3) * Cells(r, 5)
Next
Dari hasil diatas . bila di kolom P berisi 3 macam formula .
maka harus di buat 3 macam range : misal
rng_A ---- > untuk formula A
rng_B -----> untuk formula B
rng_C -----> untuk formula C
Atau
rng_D ---> kemudian formula A, B ,C bergantian di input
untuk resize : akan saya pelajari lebih lanjut.
mengenai Temp file . ini akan membebani memory kah ?
Bila file : 'M:\data_saham\LK_WEB_100.xlsm = 55 mb, maka memory yg akan
terpakai juga 55 MB
Mohon pak Kid memberikan contoh : cara penulisannya di macro dengan file :
'M:\data_saham\LK_WEB_100.xlsm untuk di jadikan file temp . dan cara
penghapusnya .
Terima kasih .
Salam
Lukman
2012/11/25 Mr. Kid <[email protected]>
> **
>
>
> Pak Lukman,
>
>
> Bila Di Tes_04 : *Activerange* yg sebagai pedomannya.
> untuk melaksanakan seperti perkalian diatas . (columns(2) *
> colums(3)) = pada rows yg sama.
> Apakah bisa dipakai : activerange.offset(0,8)
> =activerange.offset(0,-6) * activerange.offset(0,-5) = (belum sempat di
> coba )
> rasanya nga berhasil .
> >> Kita coba bersama ya.
> 1. kata activerange harus diganti dengan variabel range yang merujuk ke
> cells penulisan hasil. Itu sebabnya ada set rng=blabla seperti begini :
>
> Set Rng = Range("A2", Range("A1000000").End(xlUp))
> Jadi active range bisa saja ada di range AB1 sampai AB7, tetapi rng
> tetap merujuk ke kolom A pada baris tertentu.
> 2. variabel rng baru ada di kolom A, padahal hasil formula harus ditulis
> di kolom C yang artinya harus lompat 2 kolom kekanan. Setuju ?
> Maka penggunaan variabel range yang akan dipasangi formula bersamaan
> jadi :
> *Rng**.Offset(0, 2)*
> 3. Pada kolom C akan dipasangi formula apa ?
> Misal formulanya adalah nilai kolom X baris itu * nilai kolom G baris
> itu
> Coba ikuti bagian *Pemasangan formula melalui VBA umumnya disusun
> dengan kaidah dasar :*
> pada email sebelumnya.
> a. pasang formula di cell pertama. Berarti di C2 diworksheet (bukan
> VBA) tulis =X2*G2 lalu Enter
> b. coba copy ke semua cell data dan cek apakah sudah oke hasil
> formulanya.
> c. oh sudah oke, maka kembali ke VBA dan buat script :
> *Rng**.Offset(0, 2)**.Formula = "=X2*G2"*
> d. ubah semua karakter " menjadi "" (karena tidak ada karakter "
> dalam tulisan formula =X2*G2 maka hal ini tidak perlu dilakukan)
> 4. didapatlah tes_04 bentuk pemasangan* tanpa loop* dan langsung ke
> seluruh cell hasil.
>
> Bagaimana jika akan menggunakan IF yang buanyuak ?
> Susun saja bagian 3.a. dengan benar dan dicek pada 3.b. sampai ok,
> kemudian salin formula seperti 3.c. dengan finishing seperti 3.d.
>
> Ketika data yang akan dipasangi formula sangat banyak, maka cara tes_04
> akan sangat cepat sampai suatu bunyi formula tertentu yang sangat spesifik
> dengan kebutuhan setempat. Batas inilah yang harus secara cermat diawasi
> dan mulai dipikirkan solusi lainnya.
> Untuk jumlah baris sedikit dan proses kalkulasi yang sederhana, maka cara
> loop cell per cell seperti cara tes_02 sudah akan dirasa cukup. Tapi juga
> harus segera bertindak untuk mencari solusi lain ketika ada kesulitan dalam
> menerapkan formula yang rumit atau sudah mulai terasa lambat prosesnya.
>
>
> Looping . sedianya akan ku pergunakan untuk mencari no acc dari nama acc
> yg nga beraturan . yg bila memakai formula bisa mungkin harus memakai
> beberapa formula IF.
> >> sebelum ke formula yang puanjang dan mungkin sebaiknya di efisienkan
> dulu.
> Kemampuan Excel untuk melookup sangatlah baik. Buanyak sekali kondisi (IF)
> bisa disederhanakan dengan proses LookUp dengan menyusun tabel lookup
> pengkondisian yang baik. Coba deh cari di posting terdahulu tentang lookup
> yang bisa menjadi pengganti proses banyak kondisi.
>
> adapun formula yg hendak saya masuk kan ke dalam VBA :
> =IF(INDEX('M:\[LK_WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100"
> ;'M:\[LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(IDR)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0));IF(INDEX('M:\[LK_WEB_
> 100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100";'M:\[
> LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(USD)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))*$B$2))
> >> Untuk formula seperti ini, bisa dicoba dalam banyak tahap dan dicari
> mana yang paling efisien.
> Contoh :
> *Tahap 1*, memasang formula apa adanya (tanpa ada proses optimasi formula)
> 1. tentukan range yang akan dipasangi formula (misal barisnya mulai 5
> sampai terbawah, sesuaikan dengan keadaan real)
> Set Rng = Range("A5", Range("A1000000").End(xlUp))
> 2. formula dipasang mulai kolom P (offset 15 kolom ke kanan) sampai kolom
> AN (sebanyak 25 kolom)
> *Rng**.Offset(0, 15)***.Resize(,25)
> 3. pasang formula di worksheet pada cell P5 dan copy sampai AN100
> misalnya, dan sudah oke.
> 4. buat baris script pemasangan formula :
> *Rng**.Offset(0, 15)***.Resize(,25).formula = "*disiniformulanya*"
> *disiniformulanya *diganti dengan bunyi formula diatas menjadi :
> *Rng**.Offset(0, 15)***.Resize(,25).formula = "
> =IF(INDEX('M:\[LK_WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100"
> ;'M:\[LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(IDR)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0));IF(INDEX('M:\[LK_WEB_
> 100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100";'M:\[
> LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(USD)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))*$B$2))"
> 5. ganti semua " yang ada dalam formula menjadi ""
> *Rng**.Offset(0, 15)***.Resize(,25).formula = "
> =IF(INDEX('M:\[LK_WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&""A100""
> ;'M:\[LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(IDR)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0));IF(INDEX('M:\[LK_WEB_
> 100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100";'M:\[
> LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(USD)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))*$B$2))"
>
> Sekarang penyusunan proses agar lebih cepat, misal
> 'dimatikan dulu kalkulasi otomatisnya karena cell yang dipasangi
> formula ada banyak
> application.calculation=xlmanual
> 'pasang formulanya :
> Set Rng = Range("A5", Range("A1000000").End(xlUp))
> *Rng**.Offset(0, 15)***.Resize(,25).formula = "
> =IF(INDEX('M:\[LK_WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&""A100""
> ;'M:\[LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(IDR)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0));IF(INDEX('M:\[LK_WEB_
> 100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&"A100";'M:\[
> LK_WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))="(USD)";INDEX('M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$4:$AI$31998;MATCH($B502&P$3;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$A$4:$A$31998;0);MATCH(P$1;'M:\[LK_
> WEB_100.xlsm]DT_LK'!$K$1:$AI$1;0))*$B$2))"
> 'lakukan kalkulasi :
> rng.parent.calculate 'atau set calculation jadi xlautomatic lagi
> 'di-values-ken
> *Rng**.Offset(0, 15)***.Resize(,25).value=*Rng**.Offset(0, 15)***
> .Resize(,25).value
>
> Jika ingin coba tahap lain, bisa buat prosedur yang isinya sama, tapi nama
> prosedur beda. Kemudian ubah alur prosesnya. Misal :
> *Tahap 2* : karena ada rujukan ke file lain dan cell berformula ada
> banyak, salin dulu data rujukan dari file lain itu ke sebuah sheet
> sementara bernama temp.
> Maka proses :
> 1. buat sheet temporary (simpan ke sebuah variabel misal shtTmp dan beri
> nama tetap, misal *Temp *karena akan digunakan oleh formula)
> 2. buka workbook referensi 'M:\[LK_WEB_100.xlsm dan copy dari sheet
> bernama DT_LK dan paste values ke shttmp pada cell yang sama persis
> lokasinya seperti di dt_lk.
> 3. tutup workbook referensi
> 4. pasang formula yang sudah diubah rujukannya, yang tadi merujuk ke
> 'M:\[LK_WEB_100.xlsm]DT_LK' menjadi *Temp*
> 5. selesai proses diakhir dengan menghapus shtTemp karena hasil formula
> sudah di-values-ken
>
> Setelah dibanding-bandingkan, masih kurang cepat, maka coba tahap 3 dengan
> baris bantu atau kolom bantu, sampai bertemu formula lain yang lebih
> sederhana. Dan seterusnya begitu sampai ada banyak pilihan dan bisa memilih
> mana yang akan digunakan.
>
> Wassalam,
> Kid.
------------------------------------
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
[email protected]
posting ke milis, kirimkan ke:
[email protected]
berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran
http://www.mail-archive.com/[email protected]/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: [email protected]
keluar dari membership milis (UnSubscribe):
kirim mail kosong ke [email protected]
---------------------------------------------------------------------Yahoo!
Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/belajar-excel/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/belajar-excel/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
