Range dinamis berubah

  1. 6 tahun lalu

    Berkali-kali kode program tdk mau dijalankan krn ternyata range dinamis yg saya pakai berubah menjadi seperti ini:

    =OFFSET(BahanYgDipilih!#REF!;;;COUNTA(BahanYgDipilih!$A$2:$A$48);COUNTA(BahanYgDipilih!#REF!)).

    Mohon pencerahannya para Master apa sebabnya dan bagmn cara membuat agar range dinamis tersebut bisa tetap...

  2. Mas @Caton tolong dibantuin saya ya Mas... Suwun

  3. Caton

    18 Des 2017 Terverifikasi Indonesia + 20.101 Poin
    =OFFSET(BahanYgDipilih!#REF!;;;COUNTA(BahanYgDipilih!$A$2:$A$48);COUNTA(BahanYgDipilih!#REF!))

    Itu biasanya karena sel acuannya (pada formula range dinamis tersebut di atas adalah sel A1) sudah tidak valid lagi, biasanya karena sel atau range acuannya telah dihapus (di delete). Mungkin mas @Gunsir secara sengaja atau tidak telah menghapus baris pertama pada sheet BahanYgDipilih. Jika mas @Gunsir menggunakan macro, coba periksa kembali baris skrip macro-nya. Mungkin ada baris program yang digunakan untuk menghapus seluruh sel pada sheet BahanYgDipilih...

  4. o bgtu ya mas @Caton... benar saya memang membuat kode agar saat nilai A2 kosong program tdk terjadi error sementara range tsb dimulai dari A2 juga. Supaya range tdk berubah dan program tetap bisa jalan walaupun range acuan bernilai kosong bagmn ya mas @Caton ? Suwun

  5. Caton

    18 Des 2017 Terverifikasi Indonesia + 20.101 Poin

    Bisa upload filenya...?! Mungkin saya atau rekan-ekan lainnya bisa mempelajari dahulu script VBA yang bermasalah... ;)

  6. Jelas bisa Mas hehehe... sebentar ya

  7. Ini filenya Mas...

  8. Terima kasih sebelumnya...

  9. Caton

    18 Des 2017 Terverifikasi Indonesia + 20.101 Poin

    Dicoba dahulu modifikasi formula nama range rBahanBakuYangDipilih menjadi:

    =OFFSET(BahanYgDipilih!$A$1;1;0;COUNTA(BahanYgDipilih!$A:$A)-1;COUNTA(BahanYgDipilih!$1:$1))

    Kemudian untuk script pada frmPilihBahan:

    Private Sub cmdSelesai_Click()
        Dim rgFilter As Range
        
        pesanSelesai = MsgBox("Apakah Anda sudah selesai memilih semua bahan baku untuk meracik Nutrisi?", _
            vbOKCancel + vbCritical, "Bahan Nutrisi Sudah Lengkap")
    
        If pesanSelesai = vbCancel Then
            Exit Sub
        ElseIf pesanSelesai = vbOK Then
            Sheets("BahanYgDipilih").Select
            
            Sheet1.AutoFilterMode = False
            Set rgFilter = Sheet1.Range("rBahanBakuYangDipilih").Offset(-1, 0).Resize(RowSize:=1)
            rgFilter.AutoFilter Field:=6, Criteria1:="<> 1", Operator:=xlAnd
            
            Sheets("BahanYgDipilih").Range("rBahanBakuYangDipilih").Select
            Selection.copy
            Sheets("Tampung").Select
            Range("A2").Select
            ActiveSheet.Paste
            Sheets("BahanYgDipilih").Select
            Application.CutCopyMode = False        
        End If
    End Sub

    Mungkin demikian beberapa modifikasi yang mas @Gunsir bisa coba... ;)

  10. Siappp Mas @Caton akan saya coba terapkan... Matur Suwun

  11. Utk selanjutnya kalo melihat modifikasi tsb di atas, setiap membuat range dinamis maka sel acuannya dinaikkan 1 cell ya mas @Caton shg apabila cell A2 dihapus maka tdk akan menghapus Cell acuan (A1). Apakah sprti itu Mas konsepnya? Suwun

  12. Caton

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

    Iya mas @Gunsir... konsepnya demikian. Sehingga bila baris dibawahnya terhapus, formula dalam definisi nama range tersebut masih aman. Namun perlu diperhatikan, pada formula tersebut:

    [1] Baris data setelahnya harus diisi secara berurutan. Bila antar baris ada baris kosong, range yang dihasilkan tidak akan akurat. Coba saja isi sel A3 dengan sembarang nilai, sementara sel A2 dan mulai sel A4 seterusnya kosong, lihat hasilnya.... ;)

    [2] Jika baris kedua (sel A2) kosong, formula tersebut akan menghasilkan Null Object, yang jika diakses melalui kode VBA akan menghasilkan error. Oleh karenanya, hati-hati saat merujuk ke nama range dinamis. Solusinya bisa beragam. Bisa menggunakan Error Trapping:

    On Error Resume Next
    Set rgDinamis = Sheet1.Range("DefinisiRangeDinamis")
    If Err.Number Then
       ... isi dengan baris kode untuk menangani kesalahan ...
    End if
    Err.Clear
    On Error Goto 0

    Atau mengubah formula range dinamis menjadi:

    =OFFSET(Sheet1!$A$1;1;0;COUNTA(Sheet1!$A:$A);COUNTA(Sheet1!$1:$1))

    Syarat formula di atas, baris pertama (sel A1) harus ada nilainya (atau berupa Header Text). Hasilnya akan berupa satu baris kosong, yang di dalam kode VBA dapat diuji dengan:

    If Application.WorksheetFunction.CountA([DefinisiRangeDinamis]) = 0 Then

    atau:

    JumlahBaris = Application.WorksheetFunction.CountA([DefinisiRangeDinamis])

    Selain hal tersebut di atas, saya sangat menyarankan agar menghindari rujukan kepada Sheet Object menggunakan baris kode:

    Sheets("Nama Tab Sheet")

    atau

    Worksheets("Nama Tab Sheet")

    Jika nama "Nama Tab Sheet" tersebut diubah menjadi "Sheetku" misalnya, akan dapat menyebabkan kesalahan pada program yang kita susun saat baris kode tersebut dieksekusi. Atau repotnya, kita harus mengeksplore kembali satu persatu objek pada VBA Project untuk mengubah acuannya dari "Nama Tab Sheet" menjadi "Sheetku".

    Pada VBA Project, setiap Worksheet merupakan Object dengan nama khusus untuk lingkungan VBA Project. Tidak sama dengan nama pada tab sheet (Sheet Title). Lihat gambar berikut:

    Shoot018.png

    Nama Sheet Object pada jendela Property ada pada properti (Name), sedangkan Sheet Title ada pada properti Name (tanpa tanda buka dan tutup kurung). Meski Sheet Title berubah-ubah, name Sheet Object tidak, karena hanya bisa diubah melalui jendela VBE pada saat Design Time. Bila ingin membuat nama Sheet Object yang lebih berarti, kita bisa mengubahnya melalui properti tersebut, misalkan dari Sheet4 menjadi shtRequiredItems. Dan pada baris kode, akan lebih singkat jika kita menulis:

    Shee4.Range("A1:A5")
    shtRequiredItems.Range("A1:A5")

    daripada harus menulis:

    Worksheets("Sheet1").Range("A1:A5")
    Worksheets("Reuired Items").Range("A1:A5")

    dan tentunya lebih aman dari kesalahan ... kecuali Sheet Object-nya dihapus... :D Demikian pengalaman yang bisa saya bagikan... ;)

  13. Mantab banget pencerahannya Mas @Caton... Matur suwun ilmunya

 

atau Mendaftar untuk ikut berdiskusi!