veranda
04-08-2017, 11:32 AM
Sub TampilkanSemua()
Set DtCari = Sheets("Data")
If DtCari.Range("CI3").Value = "" Then
listCari.Clear
MsgBox "Database pelanggan kosong"
Exit Sub
End If
listCari.Clear
Set Status = DtCari.Range("CI3", DtCari.Range("CI3").End(xlDown))
With listCari
.ColumnCount = 11
.AddItem
.List(.ListCount - 1, 0) = "NOMOR_SPAJ"
.List(.ListCount - 1, 1) = "NOMOR_POLIS"
.List(.ListCount - 1, 2) = "NAMA_TERTANGGUNG"
.List(.ListCount - 1, 3) = "UP"
.List(.ListCount - 1, 4) = "PREMI_DSR"
.List(.ListCount - 1, 5) = "PREMI_TPUP"
.List(.ListCount - 1, 6) = "TOTAL_PREMI"
.List(.ListCount - 1, 7) = "RATE"
.List(.ListCount - 1, 8) = "MPI"
.List(.ListCount - 1, 9) = "PARTIAL"
.List(.ListCount - 1, a) = "ALOKASI INVESTASI"
'.List(.ListCount - 1, 11) = "KODE AGEN"
'.List(.ListCount - 1, 12) = "NAMA AGEN"
'.List(.ListCount - 1, 13) = "SUBMISSION DATE"
'.List(.ListCount - 1, 14) = "EFFECTIVE DATE"
'.List(.ListCount - 1, 15) = "ACCEPT BY UNDERWRITING"
'.List(.ListCount - 1, 16) = "TANGGAL AKHIR ASURANSI"
'.List(.ListCount - 1, 17) = "TANGGAL AKHIR PEMBAYARAN"
.ColumnWidths = 58 & ";" & 62 & ";" & 125 & ";" & 50 & ";" & 50 & ";" & 65 & ";" & 65 & ";" & 30 & ";" & 30 & ";" & 30 & ";" & 30
End With
With DtCari
Set rgTampil = Status.SpecialCells(xlCellTypeVisible)
For Each sTampil In rgTampil
With listCari
.AddItem
.List(.ListCount - 1, 0) = sTampil.Offset(0, -86).Value
.List(.ListCount - 1, 1) = sTampil.Offset(0, -85).Value
.List(.ListCount - 1, 2) = sTampil.Offset(0, -84).Value
.List(.ListCount - 1, 3) = sTampil.Offset(0, -16).Value
.List(.ListCount - 1, 4) = sTampil.Offset(0, -15).Value
.List(.ListCount - 1, 5) = sTampil.Offset(0, -14).Value
.List(.ListCount - 1, 6) = sTampil.Offset(0, -13).Value
.List(.ListCount - 1, 7) = sTampil.Offset(0, -19).Value
.List(.ListCount - 1, 8) = sTampil.Offset(0, -18).Value
.List(.ListCount - 1, 9) = sTampil.Offset(0, -17).Value
'.List(.ListCount - 1, 10) = sTampil.Offset(0, -6).Value
'.List(.ListCount - 1, 11) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 12) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 13) = sTampil.Offset(0, -85).Value
End With
Next sTampil
End With
End Sub
Set DtCari = Sheets("Data")
If DtCari.Range("CI3").Value = "" Then
listCari.Clear
MsgBox "Database pelanggan kosong"
Exit Sub
End If
listCari.Clear
Set Status = DtCari.Range("CI3", DtCari.Range("CI3").End(xlDown))
With listCari
.ColumnCount = 11
.AddItem
.List(.ListCount - 1, 0) = "NOMOR_SPAJ"
.List(.ListCount - 1, 1) = "NOMOR_POLIS"
.List(.ListCount - 1, 2) = "NAMA_TERTANGGUNG"
.List(.ListCount - 1, 3) = "UP"
.List(.ListCount - 1, 4) = "PREMI_DSR"
.List(.ListCount - 1, 5) = "PREMI_TPUP"
.List(.ListCount - 1, 6) = "TOTAL_PREMI"
.List(.ListCount - 1, 7) = "RATE"
.List(.ListCount - 1, 8) = "MPI"
.List(.ListCount - 1, 9) = "PARTIAL"
.List(.ListCount - 1, a) = "ALOKASI INVESTASI"
'.List(.ListCount - 1, 11) = "KODE AGEN"
'.List(.ListCount - 1, 12) = "NAMA AGEN"
'.List(.ListCount - 1, 13) = "SUBMISSION DATE"
'.List(.ListCount - 1, 14) = "EFFECTIVE DATE"
'.List(.ListCount - 1, 15) = "ACCEPT BY UNDERWRITING"
'.List(.ListCount - 1, 16) = "TANGGAL AKHIR ASURANSI"
'.List(.ListCount - 1, 17) = "TANGGAL AKHIR PEMBAYARAN"
.ColumnWidths = 58 & ";" & 62 & ";" & 125 & ";" & 50 & ";" & 50 & ";" & 65 & ";" & 65 & ";" & 30 & ";" & 30 & ";" & 30 & ";" & 30
End With
With DtCari
Set rgTampil = Status.SpecialCells(xlCellTypeVisible)
For Each sTampil In rgTampil
With listCari
.AddItem
.List(.ListCount - 1, 0) = sTampil.Offset(0, -86).Value
.List(.ListCount - 1, 1) = sTampil.Offset(0, -85).Value
.List(.ListCount - 1, 2) = sTampil.Offset(0, -84).Value
.List(.ListCount - 1, 3) = sTampil.Offset(0, -16).Value
.List(.ListCount - 1, 4) = sTampil.Offset(0, -15).Value
.List(.ListCount - 1, 5) = sTampil.Offset(0, -14).Value
.List(.ListCount - 1, 6) = sTampil.Offset(0, -13).Value
.List(.ListCount - 1, 7) = sTampil.Offset(0, -19).Value
.List(.ListCount - 1, 8) = sTampil.Offset(0, -18).Value
.List(.ListCount - 1, 9) = sTampil.Offset(0, -17).Value
'.List(.ListCount - 1, 10) = sTampil.Offset(0, -6).Value
'.List(.ListCount - 1, 11) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 12) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 13) = sTampil.Offset(0, -85).Value
End With
Next sTampil
End With
End Sub