Featured Posts
Menjalankan Macro Otomatis Saat Sebuah Sel Berubah
Menjawab pertanyaan Saudara Huda melalui email tentang macro VBA yang otomatis dijalankan saat sebuah sel berubah nilainya. Berikut gambarannya:
Yang dishading hijau adalah sel-sel yang sudah ada formulanya di mana jika sel A1 berubah nilainya, maka isi tabel tersebut langsung berubah. Jadi bagaimana caranya jika sel A1 dirubah nilainya menjadi 1, 2, atau 3 dan otomatis baris yang nilainya kosong (baris 26 s/d 33) langsung ter-hidden?
Berikut caranya:
- Di Excel silahkan buka Visual Basic Editor pada menu Tools > Macro > Visual basic Editor (Alt + F11).
- Klik ganda pada sheetnya lalu masukkan code VBAnya:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Worksheet_Calculate 'macro yang akan dijalankan saat sel A1 berubah
End If
End Sub
- Pilih Insert > Module dan tuliskan code VBA berikut:
Sub Worksheet_Calculate()
Dim Rng As Integer
Range("BASTB!H24:H33").Select 'Menentukan baris mana saja yang akan di hide
Selection.EntireRow.Hidden = False 'Baris akan di unhide
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = True
For i = 1 To Rng
If ActiveCell.Value = "-" Then 'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
Selection.EntireRow.Hidden = True 'Baris akan di hide
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
'Jika ada baris lain yang akan dihide, maka tambahkan lagi codingnya
Range("BASTB!H41:H50").Select 'Menentukan baris mana saja yang akan di hide
Selection.EntireRow.Hidden = False 'Baris akan di unhide
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = True
For i = 1 To Rng
If ActiveCell.Value = "-" Then 'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
Selection.EntireRow.Hidden = True 'Baris akan di hide
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
'Akhiri dengan menambahkan coding di bawah ini
Range("BASTB!A1").Select 'menempatkan sel kembali ke A1
End Sub
- Jika ada beberapa baris yang akan dihide, maka ulangi code Simpan file tersebut dan silahkan coba
Memisahkan Nama Dengan 3 (Tiga) Kata
Menjawab pertanyaan saudara Yoga pada postingan saya yang berjudul "Memisahkan Nama Depan dan Nama Belakang" dan meminta saya untuk dibuatkan rumusnya, maka berikut adalah rumusnya.
Rumus saya buat dalam dua versi:
- Mencari karakter spasi (" ") sebagai pemisah di antara ketiga nama (kolom B & C) sedang nama yang terpisah terdapat di kolom D, E, dan F.
- Menggabungkan rumus (tanpa kolom B & C)
Lihat ilustrasi berikut:
Versi I:
Rumus:
- Kolom B: =(FIND(" ",A1,1)+1)
- Mencari karakter spasi pertama. Spasi ditemukan di karakter ke-8. Plus 1 (+1) artinya mencari karakter untuk nama kedua, yaitu karakter ke 9.
- Kolom C: =FIND(" ",A1,B1)
- Mencari karakter spasi kedua. Karakter spasi kedua ditemukan dikarakter ke-14.
- Kolom D: =LEFT(A1,FIND(" ",A1,1)-1)
- Mendapatkan nama pertama, yaitu karakter paling kiri sebanyak karakter sebelum (-1) spasi.
- Kolom E: =MID(A1,FIND(" ",A1,1)+1,(C1-B1))
- Mendapatkan nama tengah, yaitu dimulai dari karakter di kolom B, sebanyak karakter kolom C minus kolom B. Kolom C minus kolom B akan mendapatkan nilai sebanyak jumlah karakter nama kedua.
- Kolom F: =MID(A1,FIND(" ",A1,C1)+1,100)
- Mendapatkan nama belakang, yaitu dimulai dari karakter di kolom C, asumsi sebanyak 100 karakter.
Versi II
Jika digabung, maka rumusnya akan sebagai berikut:
- Kolom B: =LEFT(A1,FIND(" ",A1,1)-1)
- Kolom C: =MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1)
- Kolom D: =MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100)
MENGATASI HASIL ERROR (#VALUE)
Ketiga rumus di atas harus berisikan 3 nama seperti "Maulana Malik Ibrahim". Jika namanya hanya "Maulana Malik" atau "Maulana" saja maka hasilnya akan menjadi #VALUE. Untuk mengatasi masalah ini maka rumus harus ditambah dengan rumus ISERROR dan IF.
Syntaxnya seperti ini:
=IF(ISERROR(rumus_awal)=TRUE,rumus_alternatif,rumus_awal)
Artinya: Jika rumus_awal mengasilkan nilai error (TRUE), maka jalankan rumus_alternatif, jika tidak error maka jalankan rumus_awal. Maka rumusnya akan seperti ini:
- Kolom B: =IF(ISERROR(FIND(" ",A1,1))=TRUE,LEFT(A1,100),LEFT(A1,FIND(" ",A1,1)-1))
- Kolom C: =IF(ISERROR(FIND(" ",A1,1))=TRUE,"",IF(ISERROR(MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1))=TRUE,MID(A1,FIND(" ",A1,1)+1,100),MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1)))
- Kolom D: =IF(ISERROR(MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100))=TRUE,"",MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100))
Hasilnya memang akan menjadi panjang, tapi dengan rumus ini hasilnya akan menjadi fleksibel yaitu bisa dengan 1 nama, 2 nama, maupun 3 nama. Selamat mencoba!
