@Lexs194 ...
Jika mas @Lexs194 memberikan pertanyaan yang jelas sejak awal, tentu diskusinya akan menjadi lebih fokus... :)
Sebelumnya, kita samakan dahulu persepsi mengenai target. Yang dimaksud target adalah tujuan pengisian nilai dari hasil proses pencarian. Jadi kolom A Sheet10 itu bukan target. Yang menjadi target adalah kolom ke-9 (kolom I) dan kolom ke-10 (kolom J), karena kedua kolom ini yang akan diisi dengan nilai hasil pencarian. Kemudian mengenai Primary Key, oleh karena bahasan kita adalah fungsi VLOOKUP, maka Primary Key disini adalah LOOKUP VALUE, dan parameter Table Array kita sebuat LOOKUP TABLE.
Pada dasarnya, script yang sudah saya jelaskan sebelumnya:
...
With Sheet10
Set xlValuesRange = .Range("A4:A" & .Columns(1).Rows(.Rows.Count).End(xlUp).Row)
End With
With Sheet12
Set xlLookupRange = .Range("A4:J" & .Columns(1).Rows(.Rows.Count).End(xlUp).Row)
End With
On Error Resume Next
For Each xlCell In xlValuesRange
If xlCell <> vbNullString Then
'+-- Pencarian pertama.
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
'+-- Pencarian kedua.
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 10, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=9).Value = xResult
Err.Clear
End If
Next
...
dapat digunakan untuk memenuhi apa yang mas @Lex194 inginkan, oleh karena pengulangan setiap sel pada objek xlValuesRange
akan memproses setiap baris data pada Sheet10, mulai dari baris ke-4 sampai dengan baris data terakhir pada kolom A.
Nilai dari setiap sel pada kolom A yang diambil melalui variabel xlCell
digunakan sebagai LOOKUP VALUE yang akan dicari pada LOOKUP TABLE. Jadi, kolom A bukanlah target, karena hasil dari pencarian nantinya akan disimpan/ditulis ke kolom I dan atau kolom J (dan kedua kolom inilah yang menjadi target).
Untuk mencegah proses pencarian terhadap sel kosong (Empty Cell/Null Value), yang sudah dapat dipastikan akan menghasilkan kesalahan dan tentunya akan buang-buang waktu saja, kita gunakan baris script:
If xlCell <> vbNullString Then
sebagaimana yang sudah saya jelaskan di atas. Coba mas @Lex194 bandingkan kedua script berikut. Script pertama:
...
On Error Resume Next
For Each xlCell In xlValuesRange
'+-- Pencarian pertama.
If xlCell <> vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
End If
'+-- Pencarian kedua.
If xlCell <> vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 10, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=9).Value = xResult
Err.Clear
End If
Next
...
dan script kedua:
...
On Error Resume Next
For Each xlCell In xlValuesRange
If xlCell <> vbNullString Then
'+-- Pencarian pertama.
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
'+-- Pencarian kedua.
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 10, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=9).Value = xResult
Err.Clear
End If
Next
...
mana script yang lebih baik dari kedua script di atas? Lalu bagaimana jika variabel xlCell
ada isinya (tidak kosong)? Maka baris script di dalam blok IF ... END IF tersebut yang akan dieksekusi. Nah, tinggal dimodifikasi kembali baris script di dalam blok tersebut.
Pertanyaannya, bagaimana pengujian sel kolom I atau kolom J akan dilakukan? Dalam hal ini, ada beberapa kondisi yang mungkin diterapkan, yakni:
[1]. Jika kolom I kosong maka lakukan pencarian. Kemudian jika kolom J kosong maka lakukan pencarian. Maka scriptnya bisa berupa:
...
If xlCell.Offset(ColumnOffset:=8) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
End If
If xlCell.Offset(ColumnOffset:=9) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
End If
...
[2]. Jika kolom I kosong maka lakukan pencarian. Jika kolom I tidak kosong, maka jika kolom J kosong maka lakukan pencarian. Maka scriptnya bisa berupa:
...
If xlCell.Offset(ColumnOffset:=8) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
ElseIf xlCell.Offset(ColumnOffset:=9) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
End If
...
[3]. Hanya jika kolom I kosong maka lakukan pencarian dan jika kolom J kosong lakukan pencarian. Untuk kondisi ini, scriptnya bisa berupa:
...
If xlCell.Offset(ColumnOffset:=8) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
If xlCell.Offset(ColumnOffset:=9) = vbNullString Then
xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
If Err.Number = 0 Then xlCell.Offset(ColumnOffset:=8).Value = xResult
Err.Clear
End If
End If
...
Silahkan disesuaikan dan diaplikasikan dengan kondisi aktual yang dibutuhkan.
Untuk setiap baris pada kolom I atau kolom J pada Sheet10, apabila ada sel yang kosong, maka lakukan pencarian data dengan LOOKUP VALUE pada kolom A Sheet10 dan LOOKUP TABLE ada pada Sheet12.
Jika kondisinya lebih kurang seperti tersebut, maka nilai yang diuji adalah nilai pada kolom I dan atau kolom J pada Sheet10. Hanya saja, yang perlu ditentukan pertama kali adalah bagaimana proses pemeriksaan setiap baris pada kedua kolom tersebut akan dilakukan.
Asumsikan, bila kolom A pada Sheet10 dapat dijadikan acuan untuk mendapatkan baris data terakhir, maka kita bisa menentukan kolom A sebagai pembatas barisnya, sedangkan pengujian akan tetap dilakukan terhadap kolom I dan kolom J. Perhatikan contoh script berikut:
With Sheet10
Set xlCheckRange = .Range("X5:Y" & .Columns(1).Rows(.Rows.Count).End(xlUp).Row)
End With
Pada script tersebut, properti .Columns(1)
menjadi penentu baris akhir yang akan dibaca nilainya. Selain dengan notasi tersebut, kita juga bisa menggunakan properti .Columns
untuk menentukan kolom pertama dari Sheet Object bila properti .Columns
digunakan tanpa parameter Index (saya pribadi lebih suka menggunakan properti tersebut dengan parameter Index ditentukan karena lebih jelas kolom yang diambil). Andai baris data pada kolom A ada pada baris ke-15, maka script tersebut akan membuat Range Object xlCheckRange
ditetapkan ke range X5:Y15.
Selanjutnya adalah membaca nilai dari Range Object xlCheckRange
tersebut. Dalam tahap ini, tentukan bagaimana data pada Range Object xlCheckRange
akan dibaca. Perhatikan contoh script berikut:
...
'+-- Contoh A.
For Each xlCell In xlCheckRange
...
Next
...
'+-- Contoh B.
For Each xlCell In xlCheckRange.Rows
...
Next
...
Jika Range Object xlCheckRange
ditetapkan ke range X5:Y15, maka pada:
— Contoh A, objek xlCell
akan ditetapkan ke setiap sel (EACH CELL) yang ada pada Range Object xlCheckRange
, yakni X5, Y5, X6, Y6, ... X15, Y15.
— Contoh B, objek xlCell
akan ditetapkan ke setiap baris (EACH ROW) yang ada pada Range Object xlCheckRange
, yakni X5:Y5, X6:Y6, ... X15:Y15. Untuk mengambil nilai per kolom, maka dapat digunakan properti .CELLS, misalkan xlCell.Cells(1, 1)
untuk kolom X dan xlCell.Cells(1, 2)
untuk kolom Y.
Oleh karena cara memproses objek xlCell
berbeda untuk setiap contoh, maka metode mana yang lebih tepat tergantung kepada bagaimana nilai pada objek xlCell
tersebut akan diproses nantinya.
Demikian yang dapat saya jelaskan. Semoga dapat dipahami dan bermanfaat.