tag:blogger.com,1999:blog-59967500879208969822024-03-13T09:33:43.039-07:00Otak - KidalI Nyoman Shttp://www.blogger.com/profile/13225417254642591980noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-5996750087920896982.post-37588902777236436452013-01-31T09:28:00.003-08:002013-01-31T09:28:59.545-08:00excel macro & data validation
<br />
<div style="margin-bottom: 0.14in;">
Halo … Bro,.. Sis,…
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Sekedar berbagi tips
excel menggunakan macro dan data validation sebelumnya pembaca pasti
udah pada tau tentang macro di excel , yaitu visual basic
application yang memungkinkan kita dapat menggunakan code Visual
basic pada excel dengan tujuan mempermudah pekerjaan. Untuk info
lebih jelasnya mengenai data validation silahkan Googling aja ya.
</div>
<div style="margin-bottom: 0.14in;">
Beberapa bulan lalu ane dapet tugas
dari atasan untuk membuat laporan packing list (daftar pengiriman)
yang formatnya kayak gini .</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-n8UrUkjWPD0/UQqX_dLUxII/AAAAAAAAACE/CoDegsOv70c/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="364" src="http://3.bp.blogspot.com/-n8UrUkjWPD0/UQqX_dLUxII/AAAAAAAAACE/CoDegsOv70c/s640/1.png" width="640" /></a></div>
<div style="margin-bottom: 0.14in;">
<br /></div>
<div style="margin-bottom: 0.14in;">
</div>
<div style="margin-bottom: 0.14in;">
Idenya, bos ane maunya rekan-rekan
di cabang input datanya hanya pada baris SEND DATE , ATTN, CC,
DRIVER, PHONE NO, VEHICLE NO, SOS, TUID, STAND TYPE, REFF DA dan
CHARGE CODE dengan cara manual atau di ketik sedangkan baris ORIGIN,
DESTINATION, PN dan Store input nya menggunakan data validation.</div>
<div style="margin-bottom: 0.14in;">
Kolom yang di warnai Ungu: MODEL, SN
– PREFIX, DESCRIPTION, WEIGHT serta TRANSPORT COST input nya
menggunakan macro .</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-rpoVwGILhyk/UQqY35eVO-I/AAAAAAAAACM/2JQE8s7b1Kc/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://3.bp.blogspot.com/-rpoVwGILhyk/UQqY35eVO-I/AAAAAAAAACM/2JQE8s7b1Kc/s640/2.png" width="640" /></a></div>
<div style="margin-bottom: 0.14in;">
<br /></div>
<div style="margin-bottom: 0.14in;">
</div>
<div style="margin-bottom: 0.14in;">
Keterangan gambar yang di tandai
kotak biru input datanya manual , yang ditandai kotak merah input
datanya dengan data validation, label kolom yang di warnai Ungu data
di input dengan macro.</div>
<div style="margin-bottom: 0.14in;">
Langsung aja kita mulai, keterangan
singkat Office yang digunakan adalah Microsoft office 2010 ,
Operating system mengunakan Windows XP sp 3 berikut mengenai data validation:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
-pada baris
“ORIGIN” data validationnya di letakkan di kolom M baris 104
sampai baris 124.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
-pada baris
“DESTINATION” data validationnya di letakkan di kolom M baris 104
sampai baris 124</div>
<div style="line-height: 100%; margin-bottom: 0in;">
-Pada baris “Store”
data validationnya di letakkan di kolom L baris 104 sampai baris 124.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
-Pada Kolom “PN”
data validationnya di letakkan di kolom CP baris 22 sampai baris 214.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-MiM2Pz041x0/UQqZ805kbUI/AAAAAAAAACU/qZzg1OZ6l_4/s1600/3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="362" src="http://2.bp.blogspot.com/-MiM2Pz041x0/UQqZ805kbUI/AAAAAAAAACU/qZzg1OZ6l_4/s640/3.png" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-bHLRBFEbo7M/UQqapyWk6SI/AAAAAAAAACc/R2y9g31Kyfo/s1600/4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="356" src="http://2.bp.blogspot.com/-bHLRBFEbo7M/UQqapyWk6SI/AAAAAAAAACc/R2y9g31Kyfo/s640/4.png" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<br />
<div style="margin-bottom: 0.14in;">
</div>
<div style="margin-bottom: 0.14in;">
Untuk Baris “ORIGIN” Klik kiri
pada kolom F baris 7 kemudian arahkan kursor ke menu “Data”
klik kiri, kemudian arahkan kursor ke toolbar “Data Validation”
pilih data validation.</div>
<div style="margin-bottom: 0.14in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Setelah Data
validation di klik kiri maka akan muncul menu seperti berikut pada
menu Allow: pilih “list”</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Kemudian Source nya
ketik “ =$M$104:$M$124 ” ini adalah tempat data validation di
letakkan kolom M baris 104 sampai baris 124.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-pZaGMhXdlnw/UQqfEcwLKaI/AAAAAAAAACk/q34Y7JxtrTQ/s1600/5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="http://1.bp.blogspot.com/-pZaGMhXdlnw/UQqfEcwLKaI/AAAAAAAAACk/q34Y7JxtrTQ/s640/5.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-2520J8BcZ8I/UQqfhWLiCbI/AAAAAAAAACs/Ty09AnYiE9A/s1600/6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="http://3.bp.blogspot.com/-2520J8BcZ8I/UQqfhWLiCbI/AAAAAAAAACs/Ty09AnYiE9A/s640/6.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Untuk Baris “Store”
Klik kiri pada kolom K baris 3 langkah selanjutnya sama dengan
sebelumnya pada menu Data validation untuk “Allow” pilih list dan
Sourcenya ketik “ =$L$104:$L$124 ” ini adalah tempat data
validation di letakkan kolom L baris 104 sampai baris 124.</div>
<div style="margin-bottom: 0.14in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-oBY1Mx53OHo/UQqhnv25GWI/AAAAAAAAAC0/j17M7-UuSRE/s1600/8.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="http://4.bp.blogspot.com/-oBY1Mx53OHo/UQqhnv25GWI/AAAAAAAAAC0/j17M7-UuSRE/s640/8.jpg" width="640" /></a></div>
<div style="margin-bottom: 0.14in;">
<br /></div>
<div style="margin-bottom: 0.14in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Pada baris
“DESTINATION” klik kiri pada kolom F baris 11 langkah selanjutnya
sama dengan sebelumnya untuk Allow: pilih “list” kemudian Source
nya ketik “ =$M$104:$M$124 ”.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Pada kolom “PN”
silahkan klik kiri kolom D baris 19 dan seret sampai pada baris 28
langkah selanjutnya sama dengan sebelumnya untuk Allow: pilih “list”
kemudian Source nya ketik “ =$CP$22:$CP$214 ”.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Data validation
sudah kita selesaikan berikut hasilnya jika di coba, pada contoh
dibawah klik kolom F baris 7 yang merupakan data validation “ORIGIN”
maka muncul data data yang kita letakkan di kolom M baris 104 sampai
baris 124, tinggal kita pilih data yang ingin di munculkan. Sebagai
catatan pada data validation tidak diperkenankan menginput data
dengan cara di ketik. Pada baris DESTINATION (F11), PN (D19-D28) dan
Store (K3) silahkan dicoba.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-E6_hJsjb-yc/UQqiZwBTOpI/AAAAAAAAAC8/hoDm3fwPspA/s1600/9.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="http://3.bp.blogspot.com/-E6_hJsjb-yc/UQqiZwBTOpI/AAAAAAAAAC8/hoDm3fwPspA/s640/9.jpg" width="640" /></a></div>
<div style="margin-bottom: 0.14in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Pada Project ini
macro di letakan pada tiga tempat pertama di letakan di Sheet1 yang
kedua di Thisworkbook dan yang terakhir di letakan di Module1. Macro
yang di letakan di Sheet1 digunakan untuk mendeteksi setiap perubahan
nilai yang terjadi akan menjalankan macro secara otomatis sedangkan
macro yang di letakan di Thisworkbook digunakan untuk menjalankan
macro pada saat file Packing list di open atau di close, seperti
menghidupkan timer dan mematikannya. Yang terakhir macro di letakan
di Module1, timer yang di hidupkan pada saat file di open di setting
setiap satu detik akan menjalankan macro pada Module1.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
ORIGIN dengan Store
saling terhubung karena Store adalah kode angka dari ORIGIN , maka
timbul ide untuk me link kan antara keduanya dalam artian jika kita
rubah data ORIGIN maka data Store akan berubah juga secara otomatis
begitu juga jika kita rubah data Store maka data ORIGIN akan berubah
juga secara otomatis. Untuk merealisasikannya kita menggunakan macro.
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Potongan code pada
Module1:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(7, 6).Value
<> Cells(5, 10).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(5,
10).Value = Cells(7, 6).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 104</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until
Cells(i, 12).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(i,
13).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(3, 11).Value = Cells(i, 12).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = i + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Potongan code pada
Sheet1:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells.Value <> "" And Target.Cells = Cells(3,
11) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
cari:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 104</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until
Cells(i, 12).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(i,
12).Value = Cells(3, 11).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(5, 10).Value = Cells(i, 13).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(7, 6).Value = Cells(i, 13).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
……...</div>
<div style="line-height: 100%; margin-bottom: 0in;">
……….</div>
<div style="line-height: 100%; margin-bottom: 0in;">
………</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End if</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = i+1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End if</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Keterangan singkat
mengenai kode pada Module1 di atas, jika nilai cells baris 5 kolom 10
tidak sama dengan nilai baris 7 kolom 6 maka ubah nilai baris 5 kolom
10 tersebut di sama kan dengan nilai baris 7 kolom 6 kemudian cari
kode ORIGIN tersebut di kolom M baris 104 sampai 124 jika kodenya
sudah ditemukan maka ubah nilai baris 3 kolom 11 dengan kode
tersebut. Macro pada Sheet1 juga sama cara kerjanya jika nilai cells
baris 5 kolom 10 tidak sama dengan nilai baris 7 kolom 6 maka ubah
nilai baris 7 kolom 6 tersebut di sama kan dengan nilai baris 5
kolom 10 kemudian cari kode ORIGIN tersebut di kolom M baris 104
sampai 124 jika kodenya sudah ditemukan maka ubah nilai baris 3 kolom
11 dengan kode tersebut. Perbedaanya pada Sheet1 macronya
menggunakan instruksi “Target.Cells.Value” untuk mendeteksi
perubahan nilai di cell target jadi setiap perubahan nilai yang
terjadi macro akan secara otomatis dijalankan. Sedangkan macro di
Module1 dijalankan setiap satu detik oleh timer. Dengan
mengkombinasikan kedua teknik ini maka ORIGIN dengan Store saling
terhubung nilainya. Setiap kali nilai ORIGIN di rubah maka nilai
Store pun akan berubah otomatis begitu juga sebaliknya.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-9YB7j_6b33M/UQqjVOwv5gI/AAAAAAAAADE/kNUmsxu3bSw/s1600/10.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="http://2.bp.blogspot.com/-9YB7j_6b33M/UQqjVOwv5gI/AAAAAAAAADE/kNUmsxu3bSw/s640/10.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Data yang di input
di kolom MODEL, SN – PREFIX , DESCRIPTION ,TRANSPORT COST di
lakukan oleh macro data ini tergantung pada data yang di input pada
kolom, ORIGIN, DESTINATION dan PN. Jadi setelah data ORIGIN ,
DESTINATION dan PN di input maka macro akan mencari data MODEL, SN –
PREFIX , DESCRIPTION ,TRANSPORT COST dan WHEIGHT yang sesuai di kolom
CP baris 22 sampai baris 214.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Input data pada
kolom PN selain melalui data validation bisa juga dengan cara copy
paste dan agar macro dapat mendeteksi copy paste ini kita gunakan
timer pada macro, interval waktu yang kita setting di timer adalah
satu detik. Jadi setiap satu detik timer akan menjalankan macro pada
Module1 yang akan mengecek perubahan pada kolom PN Timer di aktifkan
secara otomatis pada saat file Packing list di buka dan timer di
matikan pada saat file Packing list di tutup dan macro yang
melakukannya di letakan di di Thisworkbook dan berikut adalah
macronya.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Option Explicit</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim timer_enabled As
Boolean</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Private Sub
Workbook_Open()
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"),
Procedure:="Otakkidal"</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Private Sub
Workbook_BeforeClose(Cancel As Boolean)
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"),
Procedure:="Otakkidal", Schedule:=False
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Untuk input data
melalui data validation macro mendeteksi setiap perubahan nilai pada
kolom PN menggunakan fungsi Private Sub Worksheet_Change(ByVal Target
As Range) pada macro di Sheet1. Jadi setiap perubahan nilai pada
kolom PN macro akan di jalankan secara otomatis dan proses pencarian
data akan di jalankan.</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
Jadi pendeteksian
perubahan nilai pada kolom PN menggunakan dua teknik yang di jalankan
bersamaan b<span style="line-height: 100%;">erikut code
silahkan di pelajari,</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Copy paste code
berikut di Sheet1 seperti gambar klik kiri Developer kemudian klik
kiri visual basic kemudian klik kiri Sheet1 selanjutnya paste code di
bawah</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-uZFBLEnMGbw/UQqkADY-7aI/AAAAAAAAADM/4FGtyLpptV8/s1600/11.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="http://2.bp.blogspot.com/-uZFBLEnMGbw/UQqkADY-7aI/AAAAAAAAADM/4FGtyLpptV8/s640/11.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'=================================</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'WE ARE just
Ordinary People</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'=================================</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Option Explicit</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim OldCellValue As
String</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim i As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim ii As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim aa As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim jh As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim hy As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim jj As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim cel As Range,
match1 As Range, match2 As Range, rg As Range, targ As Range</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
Private Sub
Worksheet_Change(ByVal Target As Range)</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'ini adalah sekurity
untuk mencegah multi cells</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells.Count > 1 Then Exit Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells.Text = "" Then Exit Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(3,
11).Value = "" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo yuhu</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'disini autocomplete
nya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mencari
deskripsi store</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells.Value <> "" And Target.Cells = Cells(3,
11) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
cari:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 104</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until
Cells(i, 12).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(i,
12).Value = Cells(3, 11).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(5, 10).Value = Cells(i, 13).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(7, 6).Value = Cells(i, 13).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mencari harganya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Cells(19, 4).Value <> "" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
For hy = 19 To 28</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="line-height: 100%;"><br /></span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="line-height: 100%;">jj = 22</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(jj, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (Cells(jj, 94).Value = Cells(hy, 4).Value) And
(Cells(jj, 97).Value = Cells(hy, 6).Value) And (Cells(jj, 98).Value =
Cells(hy, 7).Value) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = 103</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(21, jh).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(21, jh).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 15).Value = Cells(jj, jh).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="line-height: 100%;">'GoTo yuhu</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
jj = jj + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Next hy</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End
If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'batassssssssssssss kesabaran</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo
yuhu</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = i + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mencari price nya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells.Value <> "" And (Target.Cells =
Cells(19, 4) Or Target.Cells = Cells(20, 4) Or Target.Cells =
Cells(21, 4) Or Target.Cells = Cells(22, 4) Or Target.Cells =
Cells(23, 4) Or Target.Cells = Cells(24, 4) Or Target.Cells =
Cells(25, 4) Or Target.Cells = Cells(26, 4) Or Target.Cells =
Cells(27, 4) Or Target.Cells = Cells(28, 4)) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
ii = 22</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 0</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until
Cells(ii, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Cells(ii, 94).Value = Target.Cells.Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
OldCellValue = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'cek
Modelnya apa??</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(19, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
19</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(20, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 20</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(21, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 21</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(22, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
22</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(23, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
23</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(24, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
24</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(25, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
25</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(26, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
26</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(27, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
27</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Target.Cells = Cells(28, 4) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i =
28</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'ini tempat
untuk mengecek prefix</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'Application.Wait Now + TimeValue("00:00:02")</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'If
MsgBox(" INPUT SN-PREFIX UNIT APAKAH " & Cells(ii,
98).Value, vbYesNo) = vbYes Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'
Cells(i, 7).Value = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'Else:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'MsgBox "Delete all comments?"</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'End
If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
(MsgBox(Cells(i, 3).Value & ". " & " PN "
& Cells(ii, 94).Value & " MODEL UNIT APAKAH " &
Cells(ii, 97).Value & " DAN SN-PREFIXNYA APAKAH " &
Cells(ii, 98).Value & " ?", vbYesNo) = vbYes And
Cells(i, 4).Value <> "") Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 6).Value = Cells(ii, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 7).Value = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 8).Value = Cells(ii, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 11).Value = Cells(ii, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Else:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = ii</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(jh, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (Cells(jh, 94).Value = Target.Cells.Value And Cells(jh,
98).Value <> OldCellValue) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (MsgBox(Cells(i, 3).Value & ". " & "
PN " & Cells(ii, 94).Value & " MODEL UNIT APAKAH "
& Cells(jh, 97).Value & " DAN SN-PREFIXNYA APAKAH "
& Cells(jh, 98).Value & " ?", vbYesNo) = vbYes And
Cells(i, 4).Value <> "") Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 6).Value = Cells(jh, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 7).Value = Cells(jh, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 8).Value = Cells(jh, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 11).Value = Cells(jh, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo lagi</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(jh, 94).Value = "" And Cells(i, 4).Value <>
"" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
MsgBox Cells(i, 3).Value & ". " & " PN
" & Cells(ii, 94).Value & " MODEL UNIT AKAN KITA
ISI " & Cells(ii, 97).Value & " SN-PREFIX AKAN KITA
ISI " & Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 6).Value = Cells(ii, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 7).Value = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 8).Value = Cells(ii, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 11).Value = Cells(ii, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
lagi:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End
If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mengecek
nilai cost nya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh =
103</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do
Until Cells(21, jh).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If
Cells(21, jh).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(i, 15).Value = Cells(ii, jh).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo yuhu</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End
If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh =
jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo
yuhu</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
ii = ii + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' If aa = 0
Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' MsgBox "
PASTIKAN PENULISAN PART NO SUDAH BENAR "</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'
GoTo yuhu</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'End If
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'set target</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
yuhu:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Copy paste code
berikut pada Thisworkbook
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-pOMax6S3T6w/UQqljP70SAI/AAAAAAAAADY/gcqPT9m80nk/s1600/12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="http://3.bp.blogspot.com/-pOMax6S3T6w/UQqljP70SAI/AAAAAAAAADY/gcqPT9m80nk/s640/12.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<br />
<br />
<br />
<br />
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'##############################################</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'##############################################</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Option Explicit</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim timer_enabled As
Boolean</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Private Sub
Workbook_Open()</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"),
Procedure:="Otakkidal"</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Private Sub
Workbook_BeforeClose(Cancel As Boolean)</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"),
Procedure:="Otakkidal", Schedule:=False</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Copy paste code
berikut pada Module1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-WgQg1Oy7Prs/UQql_cQCQuI/AAAAAAAAADk/77IBBBpjz_o/s1600/13.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="http://2.bp.blogspot.com/-WgQg1Oy7Prs/UQql_cQCQuI/AAAAAAAAADk/77IBBBpjz_o/s640/13.jpg" width="640" /></a></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'00000000000000000000000000000000000000000</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'000000000000000000000000000000000000000000</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Sub Otakkidal()</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim i As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim hy As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim a As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim jj As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim jh As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim ii As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim aa As Double</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Dim OldCellValue As
String</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(7, 6).Value
<> Cells(5, 10).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(5,
10).Value = Cells(7, 6).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = 104</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until
Cells(i, 12).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(i,
13).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(3, 11).Value = Cells(i, 12).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
i = i + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
For hy = 19 To 28</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mencari harganya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(hy,
4).Value <> "" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' MsgBox "
TImer kedua "</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
a = 0</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jj = 22</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(jj, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (Cells(jj, 94).Value = Cells(hy, 4).Value) And
(Cells(jj, 97).Value = Cells(hy, 6).Value) And (Cells(jj, 98).Value =
Cells(hy, 7).Value) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
a = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = 103</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(21, jh).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(21, jh).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 15).Value = Cells(jj, jh).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo yuhua</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jj = jj + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If a = 0 Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mencari price nya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
ii = 22</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 0</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(ii, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(ii, 94).Value = Cells(hy, 4).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' If OldCellValue = "" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
OldCellValue = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'cek
Modelnya apa??</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'ini tempat
untuk mengecek prefix</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (MsgBox(Cells(hy, 3).Value & ". " & "
PN " & Cells(ii, 94).Value & " MODEL UNIT APAKAH "
& Cells(ii, 97).Value & " DAN SN-PREFIXNYA APAKAH "
& Cells(ii, 98).Value & " ?", vbYesNo) = vbYes And
Cells(hy, 4).Value <> "") Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 6).Value = Cells(ii, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 7).Value = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 8).Value = Cells(ii, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 11).Value = Cells(ii, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Else:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = ii</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(jh, 94).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (Cells(jh, 94).Value = Cells(hy, 4).Value And
Cells(jh, 98).Value <> OldCellValue) Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If (MsgBox(Cells(hy, 3).Value & ". "
& " PN " & Cells(ii, 94).Value & " MODEL
UNIT APAKAH " & Cells(jh, 97).Value & " DAN
SN-PREFIXNYA APAKAH " & Cells(jh, 98).Value & "
?", vbYesNo) = vbYes And Cells(hy, 4).Value <> "")
Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 6).Value = Cells(jh, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 7).Value = Cells(jh, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 8).Value = Cells(jh, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 11).Value = Cells(jh, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo lagix</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(jh, 94).Value = "" And
Cells(hy, 4).Value <> "" Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
MsgBox Cells(hy, 3).Value & ". "
& " PN " & Cells(ii, 94).Value & " MODEL
UNIT AKAN KITA ISI " & Cells(ii, 97).Value & "
SN-PREFIX AKAN KITA ISI " & Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
aa = 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 6).Value = Cells(ii, 97).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 7).Value = Cells(ii, 98).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 8).Value = Cells(ii, 100).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 11).Value = Cells(ii, 102).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
lagix:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'mengecek nilai cost nya</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = 103</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Do Until Cells(21, jh).Value = ""</div>
<div style="line-height: 100%; margin-bottom: 0in;">
If Cells(21, jh).Value = Cells(7, 6).Value Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Cells(hy, 15).Value = Cells(ii, jh).Value</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo yuhua</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
jh = jh + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
GoTo yuhua</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
ii = ii + 1</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Loop</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'If aa = 0 Then</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'MsgBox " PASTIKAN PENULISAN PART NO SUDAH BENAR "</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' GoTo yuhua</div>
<div style="line-height: 100%; margin-bottom: 0in;">
' End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End If</div>
<div style="line-height: 100%; margin-bottom: 0in;">
yuhua:</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Next hy</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'reset timer
menjadi 4 detik</div>
<div style="line-height: 100%; margin-bottom: 0in;">
timer_enabled =
True</div>
<div style="line-height: 100%; margin-bottom: 0in;">
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"),
Procedure:="Otakkidal"</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'Application.EnableEvents = True</div>
<div style="line-height: 100%; margin-bottom: 0in;">
'timer_enabled =
True</div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
End Sub</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br />
</div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
I Nyoman Shttp://www.blogger.com/profile/13225417254642591980noreply@blogger.com5