Ideen til denne uges tip kom fra samtale med Dr. M, forfatter til det store ugentlige Quicken tip-nyhedsbrev.
Jeg elsker Quicken, men det har bestemt sine irritationer. Jeg har en vis memoreret rapport i Quicken med kategorier på siden og måneder, der går på toppen. Quicken giver mulighed for at udskrive denne rapport, men selvfølgelig bruger jeg altid bare kommandoen Kopiér til at kopiere rapporten til udklipsholderen og derefter bruge Rediger> Indsæt i Excel til at kopiere rapporten til udklipsholderen. Denne funktion er meget hurtigere end den ældre (og stadig tilgængelige) udskrivning til en .prn-filindstilling.
Her er hvor irritationerne dukker op. For det første gik Quicken ikke med at kopiere kolonneoverskrifterne med rapporten. Så jeg skal manuelt indtaste månedens navne i Excel. Enkelt nok. For det andet inkluderer de kategorier, der bliver kopieret til udklipsholderen, et irriterende dispositionsformat for kategorier og underkategorier.
Quicken giver dig mulighed for at bruge kategorier og underkategorier til at klassificere dine udgifter. I rapporten til venstre brydes biludgifterne yderligere af forsikring, benzin, reparationer og nummerplader. Måske gjorde jeg et dårligt stykke arbejde med at oprette mine kategorier, men jeg finder ud af, at jeg har nogle kategorier, hvor jeg vil se underkategoridetaljerne og andre kategorier, hvor jeg foretrækker bare at se kategorien i alt.
Jeg vil også gerne være i stand til at sortere denne rapport i Excel. Det ville være nyttigt at sortere det efter den samlede udgift og derefter sortere det tilbage efter kategori. Sikker på, jeg kunne bruge fortryd, men jeg vil gerne have kategorier, der er alfabetiske. Kort sagt er jeg ligeglad med det dispositionsformat, der bruges af Quicken.
Længere tid vil læsere huske min foragt for det dispositionsformat, der bruges af pivottabeller i Udfyld pivottabelle tomme celler ved hjælp af Gå til specielt tip. Vi har den samme situation her. Hvis Quicken-rapporten kun er et mellemliggende trin, og du vil kunne sortere efter kategori, er omridsformatet forfærdeligt. Efter sortering efter totaler og derefter efter kategori, kategoriseres Auto: Forsikring forkert i "I" -sektionen i rapporten. For de kategorier, hvor jeg kun gemmer det samlede antal, sorteres de forkert i "T" -sektionen i rapporten.
Der var to værktøjer, som jeg troede ville lette denne situation. Utility en kaldes sammenbrud. Når den påberåbes, kollapser denne makro en underkategori i en enkelt linje med et korrekt kategorinavn. I eksemplet ovenfor erstatter kategorien i A38 med "Computer" og sletter række 34 til 37 ved at køre makroen, mens cellemarkøren er hvor som helst i række 34 til 38.
Hjælpeprogram to er for de kategorier, hvor jeg gerne vil se underkategoridetaljerne, men ikke har brug for overskriften, den stiplede subtotelinje eller kategoritotalen. Dette værktøj kaldes Udfyld. Det finder det rigtige kategorinavn og præfikser hver underkategori med kateogrien. I eksemplet ovenfor vil kørsel af makroen, mens cellemarkøren er hvor som helst i række 24 til 30, resultere i, at celler A25: A28 ændres til et format som "Auto: Forsikring". Række 24, 29 og 30 slettes.
Til højre er min forbedrede version af rapporten. Ved at tildele genvejstasterne Skjul og udfyld var jeg i stand til at foretage disse ændringer med blot et par tastetryk. Det er nu let at sortere rapporten, vel vidende at rapporten kan vende tilbage til den oprindelige rækkefølge ved at sortere kategorien.
Hvis du er ny inden for makroer, skal du gennemgå Introduktion til Excel VBA Editor.
Når du har kopieret makroen, kan du tildele en genvejstast ved at følge disse trin:
- Fra menuen Funktioner skal du vælge makroer og derefter makro
- Fremhæv Fill-makroen. Klik på Indstillinger. I feltet Genvej skal du indtaste ethvert bogstav. Jeg bruger f til Fill. Klik på OK
- Fremhæv makroen for kollaps. Klik på Indstillinger. Vælg et bogstav til en genvej, men hold dig væk fra c, da Ctrl + c er den almindelige genvej
- for Rediger> Kopiér. Klik på OK
- Luk makrodialogen med Annuller.
Som en del af hans søgen efter at designe en tilføjelse på en dags sommerstuderende oprettede Anhtuan Do følgende makroer.
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
