Pindah data cell ke worksheet lain

  1. 6 tahun lalu

    Om sekalian, mudah2 ada yg bantu.
    saya punya pertanyaan. misalnya saya punya beberapa worksheet excel yg indentik semua baris, kolom sama.
    misal di worksheet A cell (cell acak) yg ada masih kosong tidak ada nilainya
    dan di worksheet B data cell yg kosong di worksheet A tersedia atau ada.
    bisakah macro excel dibuat untuk memindahkan data cell dari worksheet B ke A yg kosong?

    biar ngak manual pindahin satu2 datanya atau ngelookup hehe!
    trims.

  2. Caton

    26 Mar 2018 Terverifikasi Indonesia + 20.101 Poin

    @Lexs194 ...

    Saya kurang paham yang dimaksud. Pada dasarnya, menyalin data antar sheet bisa dilakukan dengan menyalin seluruh sel pada Worksheet sumber ke Worksheet tujuan atau menyalin sebagian range pada Worksheet sumber ke Worksheet tujuan pada range yang sama maupun berbeda. Saya kasih contoh saja seperti berikut:

    Sub TesSaja()
        Dim xlCell As Range
        
        For Each xlCell In Sheet1.Range("A2:C10")
            If xlCell <> vbNullString Then
                If Sheet2.Range(xlCell.Address) = vbNullString Then
                    Sheet2.Range(xlCell.Address) = xlCell
                End If
            End If
        Next
    End Sub

    Pada script di atas, jika sel pada Sheet1 memiliki isi, maka isi sel pada Sheet1tersebut akan disalin ke alamat sel yang sama pada Sheet2 jika pada sel pada Sheet2 tersebut kosong. Sederhannya, jika Sheet1.Range("A2") tidak kosong, maka nilainya akan disalin ke Sheet2.Range("A2") jika Sheet2.Range("A2") kosong.

    Demikian.

  3. Di sunting 6 tahun lalu oleh Lexs194

    @Caton
    mirip yg om caton bilang diatas.
    misalnya saya mau ambil dari kolom "I4:J4" sampai kebawah (barisnya ngak tentu) dari workbook A
    ke workbook B. bgmn om

  4. Caton

    26 Mar 2018 Terverifikasi Indonesia + 20.101 Poin

    @Lexs194 ...

    Mungkin bisa dicoba dengan mendapatkan baris terbawah antara kolom I dan kolom J tersebut. Misalkan saja contohnya saya modifikasi dari script komentar sebelumnya:

    Sub TesSaja()
        Dim xlCell As Range
        Dim lRow As Long
    
        lRow = WorksheetFunction.Max( _
            Sheet1.Columns(1).Rows(Sheet1.Rows.Count).End(xlUp).Row, _
            Sheet1.Columns(2).Rows(Sheet1.Rows.Count).End(xlUp).Row,
            Sheet1.Columns(3).Rows(Sheet1.Rows.Count).End(xlUp).Row)
        
        For Each xlCell In Sheet1.Range("A2:C" & lRow)
            If xlCell <> vbNullString Then
                If Sheet2.Range(xlCell.Address) = vbNullString Then
                    Sheet2.Range(xlCell.Address) = xlCell
                End If
            End If
        Next
    End Sub

    Pada script di atas, tinggi (jumlah baris) range sumber ditentukan berdasarkan baris terbawah antara kolom A, kolom B dan kolom C. Misalkan data terbawah ada pada sel A15, maka range sumber akan menjadi A2:C15. Demikian juga seandainya data terbawah berada pada sel C12, maka range sumber akan menjadi A2:C12.

    Demikian.

  5. ok om besok saya coba lanjutkan.
    edit sedikit lagi om. diatas scriptnya yg dalam satu worksheet. kalo dari beda workbook gmn om

  6. Caton

    26 Mar 2018 Terverifikasi Indonesia + 20.101 Poin

    @Lexs194 ...

    Khan tinggal direferensikan saja sebuah variabel objek ke range yang diinginkan pada Worksheet dari Workbook yang dituju, misalkan:

    Sub TesSaja()
        Dim xlWorkbook As Workbook
        Dim xlSheet As Worksheet
        Dim xlCell As Range
        Dim lRow As Long
    
        On Error Resume Next
        Set xlWorkbook = Application.Workbooks("Workbook Berbeda.xlsx")
        If Err.Number Then
            Err.Clear
            Set xlWorkbook = Application.Workbooks.Open(ThisWorkbook.Path & "\" & "Workbook Berbeda.xlsx")
        End If
    
        If Err.Number = 0 Then
            Set xlSheet = xlWorkbook.Worksheets("Sheet Yang Dituju")
            
            With xlSheet
                lRow = WorksheetFunction.Max( _
                    .Columns(1).Rows(.Rows.Count).End(xlUp).Row, _
                    .Columns(2).Rows(.Rows.Count).End(xlUp).Row, _
                    .Columns(3).Rows(.Rows.Count).End(xlUp).Row)
        
                For Each xlCell In .Range("A2:C" & lRow)
                    If xlCell <> vbNullString Then
                        If Sheet2.Range(xlCell.Address) = vbNullString Then
                            Sheet2.Range(xlCell.Address) = xlCell
                        End If
                    End If
                Next
            End With
        End If
        
        Err.Clear
        On Error GoTo 0
    End Sub

    Demikian.

  7. setelah saya coba sukses om,
    tapi kalo sengaja saya hapus satu baris di sheet1 otomatis hasil di sheet2 bakalan bergeser. hehee
    jadi total baris harus sama ya kalo mau pake ini.
    sip om @Caton mantap

  8. om @Caton

    Sub TesSaja()
        Dim xlCell As Range
        Dim lRow As Long
    
        lRow = WorksheetFunction.Max( _
            Sheet1.Columns(1).Rows(Sheet1.Rows.Count).End(xlUp).Row, _
            Sheet1.Columns(2).Rows(Sheet1.Rows.Count).End(xlUp).Row,
            Sheet1.Columns(3).Rows(Sheet1.Rows.Count).End(xlUp).Row)
        
        For Each xlCell In Sheet1.Range("A2:C" & lRow)
            If xlCell <> vbNullString Then
                If Sheet2.Range(xlCell.Address) = vbNullString Then
                    Sheet2.Range(xlCell.Address) = xlCell
                End If
            End If
        Next
    End Sub

    kalo diubah ke vlookup gmn om?kemarin coba2 malah gagal.
    misalnya primary key ada di sheet2 "A4" sampai kebawah, target tabel di sheet1 "A4" kebawah

  9. Caton

    31 Mar 2018 Terverifikasi Indonesia + 20.101 Poin

    @Lexs194 ...

    Sintaks fungsi VLOOKUP dalam VBA pada dasarnya sama saja dengan fungsi VLOOKUP dalam formula, yakni sebagai berikut:

    Object.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

    dimana notasi Object bisa berupa Application atau Application.WorksheetFunction (atau cukup WorksheetFunction). Dari sintaks fungsi VLOOKUP tersebut, dapat dilihat argumen atau parameter yang diperlukan, sehingga alur proses yang dapat dilakukan adalah sebagai berikut:

    [1]. Tentukan parameter yang akan digunakan. Dalam hal ini, parameter Lookup Value dan parameter Table Array didefinisikan kepada range pada Sheet1 dan Sheet2, misalkan:

    Dim xlLookupRange As Range, xlCell As Range
    Dim sAddress As String
    Dim xResult
    
    '+-- Range Pencarian Nilai.
    sAddress = "A4:A" & Sheet1.Columns(1).Rows(Sheet1.Rows.Count).End(xlUp).Row
    Set xlLookupRange = Sheet1.Range(sAddress)
    
    '+-- Alamat range nilai yang akan dicari.
    sAddress = "A4:A" & Sheet2.Columns(1).Rows(Sheet2.Rows.Count).End(xlUp).Row
    ...

    [2]. Oleh karena parameter Lookup Value hanya dapat menerima nilai yang bukan berupa array, sedangkan pada tahap pertama nilai yang akan dicari sudah didefinisikan sebagai Range Object, maka perlu proses untuk mengambil nilai per sel, misalkan:

    ...
    '+-- Alamat range nilai yang akan dicari.
    sAddress = "A4:A" & Sheet2.Columns(1).Rows(Sheet2.Rows.Count).End(xlUp).Row
    '+-- Ambil nilai per sel.
    On Error Resume Next
    For Each xlCell In Sheet2.Range(sAddress)
        ...
        '+-- Susun script untuk memproses variabel xlCell.
        ...
    Next
    ...

    [3]. Proses setiap sel yang diambil melalui pengulangan di atas sebagai parameter Lookup Value. Misalkan saja untuk setiap nilai dari sel yang diambil melalui variabel xlCell di atas, apabila pencarian nilai tersebut menghasilkan sebuah nilai, maka tulis hasilnya pada sel kedua pada baris yang sama. Jadi, misalkan alamat varibel xlCell adalah A4, maka jika pencarian nilai sel A4 pada range xlLookupRange menghasilkan sebuah nilai, maka akan ditulis pada sel B4. Scriptnya dapat disusun misalnya seperti berikut:

    ...
    On Error Resume Next
    For Each xlCell In Sheet2.Range(sAddress)
        If xlCell <> vbNullString Then
            xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 1, 0)
            If Err.Number = 0 Then
                xlCell.Offset(ColumnOffset:=1).Value = xResult
            End If
            Err.Clear
        End If
    Next
    ...

    Oleh karena fungsi VLOOKUP pada script di atas menggunakan objek WorksheetFunction, yang akan menghasilkan kesalahan jika nilai tidak ditemukan pada parameter Table Array, maka kita bisa memanfaatkan kesalahan yang muncul sebagai flag (penanda) apakah nilai ditemukan atau tidak. Pada script di atas, notasi 1 adalah nilai untuk parameter Column Index. Oleh karena range xlLookupRange ditetapkan pada satu kolom saja, maka parameter Column Index tidak dapat ditetapkan ke indeks kolom selain 1. Sedangkan notasi 0 adalah nilai untuk parameter Range Lookup yang nilainya berisi TRUE atau FALSE (coba ubah nilainya menjadi 1 jika proses tidak menghasilkan hasil sama sekali).

    Pada dasarnya demikian saja proses intinya. Coba susun kembali potongan script di atas ke dalam sebuah prosedur, misalkan Sub TesVlookup() dan lakukan modifikasi sesuai kebutuhan. Letakkan kursor edit di dalam prosedur tersebut lalu tekan tombol F5 untuk mengeksekusi prosedur tersebut.

    Demikian, semoga bermanfaat.

  10. Di sunting 6 tahun lalu oleh Lexs194

    saya ubah seperti ini om @Caton

    Sub syncRemark()
        Dim xlLookupRange As Range, xlCell As Range
        Dim sAddress As String
        Dim xResult
        
        'ini target tabel
        sAddress = "A4:J" & Sheet12.Columns.Rows(Sheet12.Rows.Count).End(xlUp).Row
        Set xlLookupRange = Sheet12.Range(sAddress)
        
        'ini tabel yg ingin saya isi nilainya
        sAddress = "A4:J" & Sheet10.Columns(1).Rows(Sheet10.Rows.Count).End(xlUp).Row
        
        On Error Resume Next
            For Each xlCell In Sheet10.Range(sAddress)
                If xlCell <> vbNullString Then
                    xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
                    If Err.Number = 0 Then
                        xlCell.Offset(columnoffset:=8).Value = xResult
                    End If
                    Err.Clear
                End If
            Next
    End Sub


    saya ubah seperti ini om, bener gak ya.
    jadi disini saya mengisi nilai di sheet10, kolom ke 9
    dan saya coba test dapat nilai sesuai yg ada di sheet12
    *oiya untuk code ini om
    If xlCell <> vbNullString Then
    seharusnya hanya isi cell yg kosong kan ya, soalnya yg ada isinya juga bisa dicari lagi nilai

    kalo saya mau tambahin lookup lagi ke sheet10 kolom ke 10 bagaimana om,
    dgn primary key yg sama seperti diatas hanya lookup range nya yg berbeda

  11. Caton

    31 Mar 2018 Terverifikasi Indonesia + 20.101 Poin

    @Lexs194 ...

    ... saya ubah seperti ini om, bener gak ya ...

    Sudah dicoba belum scriptnya? Kalau sudah, apakah sesuai hasilnya dengan yang diharapkan? Sepertinya sudah khan...?! Sedikit komentar tentang script di atas adalah pada script berikut:

    'ini tabel yg ingin saya isi nilainya
    sAddress = "A4:J" & Sheet10.Columns(1).Rows(Sheet10.Rows.Count).End(xlUp).Row

    Pertanyaannya, mengapa range nilai yang akan dicari begitu lebar? Jika saya HANYA butuh mencari nilai pada 1 kolom saja (misalnya hanya pada kolom A), mengapa saya harus mendefinisikan alamat range menjadi 10 kolom? Saya cukup mendefinisikan alamat rangenya menjadi:

    sAddress = "A4:A" & Sheet10.Columns(1).Rows(Sheet10.Rows.Count).End(xlUp).Row

    Karena pada baris script:

    For Each xlCell In Sheet10.Range(sAddress)

    seluruh sel pada alamat range yang sudah didefinisikan akan diambil/dibaca. Jika kolom A terdiri dari 1.000 baris data, maka jika definisi rangenya adalah pada kolom A sampai dengan kolom J, maka akan ada 1.000 x 10 = 10.000 sel yang akan diproses, padahal yang ingin diproses pada dasarnya hanyalah 1.000 sel saja.

    ... kalo saya mau tambahin lookup lagi ke sheet10 kolom ke 10 bagaimana om, dgn primary key yg sama seperti diatas hanya lookup range nya yg berbeda ...

    Sederhananya, masukkan script yang sudah dibuat ke dalam pengulangan, misalkan:

    Sub syncRemark()
        Dim xlLookupRange As Range, xlCell As Range
        Dim sAddress As String
        Dim xResult, xLoop
        
        For xLoop = 1 To 2
            'ini target tabel
            If xLoop = 1 Then
                sAddress = "A4:J" & Sheet12.Columns.Rows(Sheet12.Rows.Count).End(xlUp).Row
                Set xlLookupRange = Sheet12.Range(sAddress)
            Else
                '+-- Atur kembali alamat rangenya agar tidak konflik
                '+-- dengan baris script selanjutnya di bawah ini.
                sAddress = "A4:J" & Sheet10.Columns.Rows(Sheet10.Rows.Count).End(xlUp).Row
                Set xlLookupRange = Sheet10.Range(sAddress)
            End If
            
            'ini tabel yg ingin saya isi nilainya
            sAddress = "A4:J" & Sheet10.Columns(1).Rows(Sheet10.Rows.Count).End(xlUp).Row
            
            On Error Resume Next
            For Each xlCell In Sheet10.Range(sAddress)
                If xlCell <> vbNullString Then
                    xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
                    If err.Number = 0 Then
                        xlCell.Offset(columnoffset:=8).Value = xResult
                    End If
                    err.Clear
                End If
            Next
        Next
    End Sub

    Saya tidak tahu apakah mas @Lex194 benar-benar mau menetapkan parameter Table Array kepada sheet yang sama dengan sheet dimana nilai akan diproses/dicari. Saran saya, perhatikan alamat range yang akan didefinisikan, karena pada saat nilai variabel xLoop = 2, mas @Lex194 akan mereferensikan objek xlLookupRange ke Sheet Object yang sama dengan referensi objek dimana nilai yang akan dicari diambil, dan hal ini mungkin bisa menyebabkan masalah...

    Demikian.

  12. om @Caton
    saya ambil resultnya seperti ini kira2 aman ngak ya om,

    Sub syncRemark()
        Dim xlLookupRange As Range, xlCell As Range
        Dim sAddress As String
        Dim xResult, xResult1, xLoop
        
        For xLoop = 1 To 2
        'ini target tabel
        If xLoop = 1 Then
            sAddress = "A4:J" & Sheet12.Columns.Rows(Sheet12.Rows.Count).End(xlUp).Row
            Set xlLookupRange = Sheet12.Range(sAddress)
        Else
        'ini tabel yg ingin saya isi nilainya
            sAddress = "A4:A" & Sheet10.Columns(1).Rows(Sheet10.Rows.Count).End(xlUp).Row
            Set xlLookupRange = Sheet10.Range(sAddress)
        End If
        
        On Error Resume Next
            For Each xlCell In Sheet10.Range(sAddress)
                If xlCell <> vbNullString Then
                    xResult = WorksheetFunction.VLookup(xlCell, xlLookupRange, 9, 0)
                    xResult1 = WorksheetFunction.VLookup(xlCell, xlLookupRange, 10, 0)
                    If Err.Number = 0 Then
                        xlCell.Offset(columnoffset:=8).Value = xResult
                        xlCell.Offset(columnoffset:=9).Value = xResult1
                    End If
                    Err.Clear
                End If
            Next
        Next
    End Sub

  13. Di sunting 6 tahun lalu oleh Lexs194

    oiya om untuk code ini
    If xlCell <> vbNullString Then
    seharusnya cuma ngisi yg kosong saja kan ya, tapi yg ada isi atau ada nilai ke lookup juga datanya.
    enaknya gmn ya om

  14. Caton

    31 Mar 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    @Lexs194 ...

    ... If xlCell <> vbNullString Then seharusnya cuma ngisi yg kosong saja kan ya, tapi yg ada isi atau ada nilai ke lookup juga datanya ...

    Maksudnya apa sih mas @Lexs194 ...? Mas @Lexs194 paham khan kalau operator <> berarti TIDAK SAMA DENGAN. Jadi script:

    If xlCell <> vbNullString Then

    artinya JIKA xlCell TIDAK SAMA DENGAN String Kosong (Empty atau Null Cell). Script tersebut sama saja dengan:

    If xlCell <> "" Then

    Pada dasarnya, script tersebut bertujuan untuk menghindari proses pencarian jika sel yang dibaca isinya kosong. Kalau tidak ada nilai yang akan dicari, maka hasilnya akan berupa kesalahan (#N/A). Jadi untuk apa proses pencarian dilakukan jika nantinya hanya akan menghasilkan kesalahan? Agar lebih jelas, perhatikan gambar berikut:

    Untitled.png

    Jika proses akan mengambil nilai pada kolom A mulai dari baris ke-1 sampai dengan baris ke-6, maka pada saat proses sampai pada baris ke-3, dengan script di atas, proses tidak akan mengeksekusi script yang ada di dalam blok IF tersebut karena sel A3 tidak ada isinya alias kosong (Empty Cell).

    ... saya ambil resultnya seperti ini kira2 aman ngak ya om ...

    Jangan tanya saya aman atau tidaknya. Seharusnya, kalau sudah DICOBA dan hasilnya ternyata tidak aman, baru kita diskusikan. Kalau saya lihat, script yang mas @Lexs194 maksudkan tersebut kemungkinan besar akan bermasalah. Namun karena ada perangkap kesalahan sebelum proses, maka tidak akan diketahui kesalahannya...

    Saya tidak terlalu paham apa yang mas @Lexs194 targetkan. Jadi, coba kita ulangi kembali tahap demi tahap kriteria dan prosesnya berdasarkan apa yang saya pahami, sebagai berikut:

    [1]. Parameter LOOKUP VALUE diambil dari Sheet10 pada kolom A, mulai dari baris ke-4 sampai dengan baris terakhir yang ada isinya. Dalam hal ini, script yang akan digunakan misalkan seperti ini:

    With Sheet10
        Set xlValuesRange = .Range("A4:A" & .Columns(1).Rows(.Rows.Count).End(xlUp).Row)
    End With

    Oleh karena parameter LOOKUP VALUE pada fungsi VLOOKUP tidak dapat menerima nilai berupa array, maka nilai pada variabel objek xlValuesRange akan diambil per sel melalui pengulangan, dalam hal ini melalui fungsi FOR EACH ... NEXT.

    [2]. Nilai yang akan dicari pada parameter LOOKUP VALUE nantinya akan dicari pada dua TABLE ARRAY yang berbeda berdasarkan script pada penjelasan di atas, yakni:

    — Sheet12 kolom A, dimana kolom nilai yang akan dihasilkan diambil dari kolom ke-9 (kolom I).
    — Sheet12 kolom A, dimana kolom nilai yang akan dihasilkan diambil dari kolom ke-10 (kolom J).

    maka script yang bisa kita susun untuk mendefinisikan Range Object-nya adalah sebagai berikut:

    With Sheet12
        Set xlLookupRange = .Range("A4:J" & .Columns(1).Rows(.Rows.Count).End(xlUp).Row)
    End With

    Di sini, cukup satu Range Object saja yang kita definisikan karena range yang kita rujuk pada script tersebut sudah mencakup kolom ke-9 (kolom I) dan kolom ke-10 (kolom J).

    [3]. Sebagaimana penjelasan pada tahap pertama, untuk memproses nilai yang akan dicari pada range xlValuesRange akan dilakukan melalui pengulangan, maka scriptnya dapat disusun sebagai berikut:

    ...
    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
    ...

    Pada dasarnya, demikian yang bisa kita susun. Sekali lagi, draft script di atas berdasarkan apa yang saya pahami dari penjelasan mas @Lexs194. Jika tidak sesuai, silahkan di modifikasi dan di adaptasikan sesuai keinginan dan kebutuhan. Jika ada yang tidak sesuai, silahkan jelaskan dengan detil bagaimana target atau kriteria yang diinginkan.

    Perlu dipahami, sengaja saya berikan scriptnya per bagian agar mas @Lex194 bisa menyusun sendiri scriptnya secara utuh, sehingga paham apa yang harus dikerjakan dan bagaimana susunan script yang terbaik. Satu hal lagi, script yang sudah disusun DICOBA terlebih dahulu. Apabila menemukan kesalahan, atau ada hal-hal yang tidak sesuai dengan target yang diinginkan, baru didiskusikan.

    Demikian, semoga bermanfaat.

  15. Di sunting 6 tahun lalu oleh Lexs194
    If xlCell <> vbNullString Then


    ini pemahaman saya sama dengan null. haha. :D maaf om salah tangkap saya.

    gini om maunya saya, dikolom I dan J atau di kolom 9 dan 10 (sheet 10)
    Jika ada cell yang kosong maka jalankan fungsi vlookup
    dengan primary key yang ada di "A4:A" sheet10 dengan target range sheet12

    dari penjelasannya om @Caton yang

    For Each xlCell In xlValueRange
            If xlCell <> vbNullString Then


    ini berarti targetnya di kolom "A" ya om yang tidak sama dngan null

  16. Caton

    1 Apr 2018 Terverifikasi Indonesia + 20.101 Poin

    @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.

  17. om @Caton emang terbaik tau saja yang saya inginkan :)
    mantap terima kasih om

 

atau Mendaftar untuk ikut berdiskusi!