Vlookup dengan VBA

  1. 6 tahun lalu

    Met malam para Master... saya sedang buat aplikasi tetapi mentok di pembuatan program utk menyalin dan mencocokkan data dari satu sheet dengan sheet yang lain. Contoh dokumen sdh saya sertakan. Dalam dokumen tersebut saya ingin saat command button "Hitung" diklik otomatis mengambil data berupa nilai Target PPM dari sheet Target PPM untuk setiap kriteria dari satu kolom dari sheet Tampung dan apabila sdh berhasil maka row PPM Hitung di dlm sheet Tampung otomatis menghitung jumlah PPM Hitung yang diperoleh dari Kandungan ("E") x Target PPM ("H"). Saya blm punya ilmu utk membuat kolom PPM Target pada sheet Tampung dapat sesuai dengan kriteria "Unsur Kimia" nya dari Sheet Target PPM. Atas petunjuknya sama mengucapkan banyak terima kasih.

  2. Sudah saya coba dengan script sbb:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    Range("H9").Select

    tapi kenapa kok hasil di H8 bernilai #N/A ya Master...

  3. Om @Caton tolongin saya donk... :)

  4. filenya ga ada mas @gunsir . mas catonnya sedang istirahat seperti nya,...

    mungkin nanti rekan @Caton atau yg lainnya bisa bantu setelah filenya terupload

  5. iya mas @Fujiansyah92 ini saya sertakan filenya.

  6. Di sunting 6 tahun lalu oleh Fujiansyah92

    1). bukannya memang NH4 itu tdk ada di TARGET PPM. Yg ada hanya NH aja tanpa angka 4 .makanya jadi N/A
    2). koreksi untuk named range rgTargetPPM ,silahkan rubah menjadi kode berikut agar tdk berpindah cell =OFFSET(TargetPPM!$A$1,0,0,COUNTA(TargetPPM!$A:$A),2)

  7. o iya mas @Fujiansyah92 ... harusnya yg NH itu NH4 tadi saya coba2 ganti hilangin angka 4 tapi tetap ndak bisa. Hasil di H7 masih #N/A itu bgmn Mas apkh pengaruh pindah2nya cell ya

  8. Di sunting 6 tahun lalu oleh Fujiansyah92

    kan saya bilang ganti mas dynamic range nya Formulas>name manager > pilih rgTargetPPM =OFFSET(TargetPPM!$A$1,0,0,COUNTA(TargetPPM!$A:$A),2)
    cek file terlampir ya yg sudah diperbaiki
    TanyaMilis.xlsm

  9. Oke Mas saya cek dulu ya... Suwun

  10. @gunsir Oke Mas saya cek dulu ya... Suwun

    sipp

  11. Hooorrreeee... dah bisa terima kasih mas @Fujiansyah92

  12. Caton

    8 Des 2017 Terverifikasi Indonesia + 20.101 Poin

    Ikutan nimbrung dikit mas @Fujiansyah92 ... :) Saran sedikit. Untuk data pada sheet TargetPPM, baiknya di sort secara Ascending dahulu. Kemudian, sebagaimana koreksi mas @Fujiansyah92 , Named Range rgTargetPPM dikoreksi kembali. Sedangkan untuk kode VBA-nya (objek Sheet15):

    Private Sub cmdHitung_Click()
        With Me
            .Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H3").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H4").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H5").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H6").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H7").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
            .Range("H8").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
        End With
    End Sub

    Demikian ide saya... ;)

  13. Siap mas @Caton ini stlh dirubah kodenya:

    Private Sub cmdHitung_Click()
    Range("A2:H8").Select
    ActiveWorkbook.Worksheets("Tampung").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tampung").Sort.SortFields.Add Key:=Range("D3:D8") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Tampung").Sort
    .SetRange Range("A2:H8")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    With Me
    .Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H3").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H4").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H5").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H6").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H7").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    .Range("H8").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    End With
    Range("I2").Select

    Jadi lebih mudah memahami ya mas... Tadi terus terang kode awal mprk hasil dari record macro saking pusingnya gak bisa2 jalan sempurna. Terima kasih mas @Caton dan mas @Fujiansyah92 atas bantuan dan ilmunya.... belajarexcel.org mantaaaappp....
    End Sub

  14. Caton

    8 Des 2017 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Iya mas @Gunsir... untuk mempercepat proses, kode VBA-nya juga bisa dimodifikasi menjadi:

    Private Sub cmdHitung_Click()
        Application.ScreenUpdating = False
        Range("A2:H8").Select
        With Me
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("D3:D8"), SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("A2:H8")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
        
        Dim lIdx As Long
        For lIdx = 2 To 8
            Range("H" & lIdx).FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
        Next
        Application.ScreenUpdating = True
    End Sub

    Demikian... ;)

  15. ini juga akan berlaku seandainya data pada sheets("Tampung") bertambah ya mas @Caton ?

  16. Caton

    8 Des 2017 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Saya malah berasumsi jika datanya bertambah... :) Coba saja bedakan, jika ada 100 baris data (range H2:H101), akan berapa banyak baris kode:

    With Me
       .Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
       .Range("H3").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
       ...
       .Range("H101").FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,0)"
    End With

    yang akan kita tulis. Kemudian baris:

    Private Sub cmdHitung_Click()
      Application.ScreenUpdating = False
    
      ...
    
      Application.ScreenUpdating = True
    End Sub

    juga memberikan perbedaan kecepatan proses. Demikian pengalaman saya...

  17. iya.. iya betul itu juga yg menjadi pertanyaan saya...
    mas @Caton boleh tahu ndak arti dari

    For lIdx = 2 To 8

    itu bgmn? Suwun

  18. Caton

    8 Des 2017 Terverifikasi Indonesia + 20.101 Poin

    Misalkan kita menulis baris kode VBA:

    For A = 1 To 10
       ...
    Next

    artinya untuk variabel A, ditentukan nilai dari 1 sampai dengan 10. Proses akan berlangsung berulang, mulai dari A = 1 sampai dengan A = 10. Setiap kali proses perulangan terjadi, perintah di dalam blok FOR ... NEXT akan dieksekusi. Oleh karenanya FOR ... NEXT merupakan kelompok Perulangan. Sama halnya dengan DO ... LOOP WHILE, DO WHILE ... LOOP, DO UNTIL ... LOOP, DO ... LOOP UNTIL, FOR EACH ... NEXT. Jadi pada baris kode:

        Dim lIdx As Long
        For lIdx = 2 To 8
            Range("H" & lIdx).FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
        Next

    akan terjadi perulangan untuk nilai lIdx = 2 sampai lIdx = 8. Dengan demikian baris kode:

       ...
       Range("H" & lIdx).FormulaR1C1 = "=VLOOKUP(RC[-4],rgTargetPPM,2,1)"
       ...

    akan terisi dengan nilai variabel lIdx yang terus berubah. Range("H" & lIdx) berubah menjadi Range("H2"), Range("H3"), Range("H4") sampai terakhir Range("H8"). Demikian... ;)

  19. Matur suwun mas @Caton atas penjelasannya.... Penjelasan yg mudah dipahami

  20. mas @Caton ... ternyata data yg harus diisi tidak sampai sampai Range("H8") tetapi bisa sampai Range ("H20") dan ke atas bahkan bisa juga hanya sampai Range ("H4"). Bagaimana jadinya kode programnya mas? Suwun sebelumnya...

  21. Newer ›
 

atau Mendaftar untuk ikut berdiskusi!