Ide untuk tip minggu ini datang dari percakapan dengan Dr. M, penulis buletin tips cepat mingguan yang hebat.
Saya suka Quicken, tapi pasti ada gangguannya. Saya memiliki laporan hafalan tertentu di Quicken dengan kategori di samping dan bulan di atasnya. Quicken menawarkan kesempatan untuk mencetak laporan ini, tetapi tentu saja, saya selalu menggunakan perintah Salin untuk menyalin laporan ke papan klip dan kemudian menggunakan Edit> Tempel di Excel untuk menyalin laporan ke papan klip. Fitur ini jauh lebih cepat daripada opsi mencetak ke file .prn yang lebih lama (dan masih tersedia).
Di sinilah gangguan muncul. Pertama, Quicken tidak mau repot menyalin judul kolom dengan laporannya. Jadi, saya harus memasukkan nama bulan secara manual di Excel. Cukup sederhana. Kedua, kategori yang disalin ke clipboard menyertakan format outline yang mengganggu untuk kategori dan subkategori.
Quicken memungkinkan Anda menggunakan kategori dan subkategori untuk mengklasifikasikan pengeluaran Anda. Dalam laporan di sebelah kiri, pengeluaran otomotif selanjutnya dibagi oleh asuransi, bensin, perbaikan dan pelat nomor. Mungkin saya melakukan pekerjaan yang buruk dalam menyiapkan kategori saya, tetapi saya menemukan bahwa saya memiliki beberapa kategori di mana saya ingin melihat detail subkategori dan kategori lain di mana saya lebih suka untuk hanya melihat total kategori.
Saya juga ingin bisa mengurutkan laporan ini di Excel. Akan berguna untuk menyortirnya berdasarkan total biaya, lalu mengurutkannya kembali berdasarkan kategori. Tentu, saya dapat menggunakan urung, tetapi saya ingin kategori yang bersifat alfabet. Singkatnya, saya tidak peduli dengan format garis besar yang digunakan oleh Quicken.
Pembaca lama akan mengingat penghinaan saya untuk format garis besar yang digunakan oleh tabel pivot dalam Mengisi Sel Kosong Tabel Pivot Menggunakan Go To Special tip. Kami memiliki situasi yang sama di sini. Jika laporan Quicken hanyalah langkah perantara dan Anda ingin mengurutkan berdasarkan kategori, format garis besarnya sangat buruk. Setelah mengurutkan menurut total dan kemudian menurut kategori, kategori Otomatis: Asuransi akan salah diurutkan ke dalam bagian "I" di laporan. Untuk kategori yang saya simpan hanya totalnya, kategori tersebut akan salah diurutkan ke dalam bagian "T" di laporan.
Ada dua utilitas yang menurut saya akan meredakan situasi ini. Utilitas satu disebut runtuh. Saat dipanggil, makro ini akan menciutkan sub-kategori menjadi satu baris dengan nama kategori yang sesuai. Dalam contoh di atas, menjalankan makro saat penunjuk sel berada di mana saja di baris 34 hingga 38 akan mengganti kategori di A38 dengan "Komputer" dan menghapus baris 34 hingga 37.
Utilitas dua adalah untuk kategori di mana saya ingin melihat detail subkategori, tetapi tidak memerlukan judul, garis subtotal putus-putus, atau total kategori. Utilitas ini disebut Isi. Ini akan menemukan nama kategori yang tepat, dan mengawali setiap subkategori dengan kategorinya. Dalam contoh di atas, menjalankan makro saat penunjuk sel berada di mana saja di baris 24 hingga 30 akan mengakibatkan sel A25: A28 diubah ke format seperti "Otomatis: Asuransi". Baris 24, 29 dan 30 akan dihapus.
Di sebelah kanan, adalah versi laporan saya yang ditingkatkan. Dengan menetapkan Ciutkan dan Isi ke tombol pintas, saya dapat membuat perubahan ini hanya dengan beberapa penekanan tombol. Sekarang mudah untuk mengurutkan laporan, mengetahui bahwa laporan dapat kembali ke urutan aslinya dengan mengurutkan kategori.
Jika Anda baru mengenal makro, tinjau Memperkenalkan Editor VBA Excel.
Setelah Anda menyalin makro, Anda dapat menetapkan tombol pintas dengan mengikuti langkah-langkah berikut:
- Dari Menu Alat, Pilih Makro lalu Makro
- Sorot makro Isi. Klik Opsi. Di bidang Pintasan, masukkan huruf apa saja. Saya menggunakan f untuk Isi. Klik OK
- Sorot makro penciutan. Klik Opsi. Pilih huruf untuk jalan pintas, tapi menjauhlah dari c, karena Ctrl + c adalah jalan pintas yang umum
- untuk Edit> Salin. Klik OK
- Tutup dialog makro dengan Batal.
Sebagai bagian dari upayanya untuk merancang add-in a day, magang musim panas, Anhtuan Do membuat makro berikut.
Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare Dim CategoryName As String 'Name of the category currently in Sub Collapse() 'To collapse the rows, run this macro Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Replace "TOTAL Category" with "Category" Cells(TotalRow, 1).Value = " " & CategoryName 'Deleting sub-category rows Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp End Sub Sub Fill() 'To add the CategoryName to each of the types, run this macro Dim i As Integer Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Adding CategoryName and colon to the start of each sub category For i = HeaderRow + 1 To TotalRow - 2 TempString = Trim(Cells(i, 1).Value) Cells(i, 1).Value = " " & CategoryName & ": " & TempString Next i 'Deleting Header and Total Row Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp Rows(HeaderRow).Delete Shift:=xlUp End Sub Sub FindHeader() Dim i As Integer TempString = Cells(ActiveCell.Row - Counter, 1).Value 'Checking to see if in first row If (ActiveCell.Row - Counter) = 1 Then MsgBox "You are not in a collapsable row" End If 'Checking to see if in a valid row 'If it reaches a TOTAL before reaching a colon, then error If Left(Trim(TempString), 5) = "TOTAL" Then MsgBox "You are not in a collapsable row" End If 'Checking the String for a colon For i = 1 To Len(TempString) TempTest = Mid(TempString, i, 1) If TempTest = ":" Then CategoryName = TempString Flag = True Exit For End If Next i End Sub Sub FindTotal() Dim i As Integer 'Finding the TOTAL Row TempString = Cells(ActiveCell.Row + Counter, 1).Value TempString = Trim(TempString) If TempString = "TOTAL " & CategoryName Then Flag2 = True End If End Sub
