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.
2012/11/25 lkm jktind <[email protected]>
> **
>
>
> Pak Kid.
>
> Bila memakai range. maka yg di pakai adalah offset dari activecell yg
> sebagai pedomannya. (di Tes_01 dan Tes_03) di kerjakan baris kebaris
> berikutnya
> cells(1,1).select
> *ActiveCell*.Offset(0, 2).FormulaR1C1 =
> "=TEXT(RC[-2],""dd/mm/yy"")"
> di Tes_01
> With* ActiveCell * .Offset(i - 1, 4).FormulaR1C1 =
> "=TEXT(RC[-4],""dd/mm/yy"")"
> pemakaian* Offset* menjadi sangat dominan. misalnya :
> activecell.offset(0,6) = activecell.offset(0,-4) * activecell.offset(0,-3)
>
> Bila di Tes_02 : Dim r As Long *Active rows* yg sebagai
> pedomannya. di kerjakan baris kebaris berikutnya
> cells(1,1).select
>
> Cells(r, 4) = Left(Cells(r, 1), 10)
> untuk perkalian . menjadi : Cells(r,7) = cells(r,2) *
> Cells(r,3)
>
> 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 .
>
> dari penjelasan Pak Kid . tanpa sengaja saya menemukan cara memasuk
> formula excel kedalam VBA yg didalam formula nya ada tanda " -------- yg
> harus dirubah menjadi "" (dua kali)
> walaupun belum saya coba . (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))
>
> 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.
> dari sumber file PDF setelah di conver menjadi Excel. yg kemudian di
> ambil nilai nya.
>
> Terima kasih . Pak Kid.
>
> Selamat siang.
>
> Salam dari
>
> Lukman
>
>
>
>
>
