Het idee voor de tip van deze week kwam van een gesprek met dr. M, auteur van de geweldige wekelijkse Quicken tips nieuwsbrief.
Ik hou van Quicken, maar het heeft zeker zijn ergernissen. Ik heb een bepaald rapport uit het hoofd in Quicken met categorieën aan de zijkant en maanden aan de bovenkant. Quicken biedt de mogelijkheid om dit rapport af te drukken, maar ik gebruik natuurlijk altijd de opdracht Kopiëren om het rapport naar het klembord te kopiëren en gebruik vervolgens Bewerken> Plakken in Excel om het rapport naar het klembord te kopiëren. Deze functie is veel sneller dan de oudere (en nog steeds beschikbare) optie om naar een .prn-bestand te printen.
Hier komen de ergernissen naar boven. Ten eerste nam Quicken niet de moeite om de kolomkoppen met het rapport te kopiëren. Dus ik moet de maandnamen handmatig invoeren in Excel. Simpel genoeg. Ten tweede bevatten de categorieën die naar het klembord worden gekopieerd, een irritant overzichtsformaat voor categorieën en subcategorieën.
Met Quicken kunt u categorieën en subcategorieën gebruiken om uw uitgaven te classificeren. In het rapport links worden de autokosten verder uitgesplitst naar verzekeringen, benzine, reparaties en kentekenplaten. Misschien heb ik mijn categorieën slecht opgezet, maar ik merk dat ik een aantal categorieën heb waar ik de subcategoriedetails wil zien en andere categorieën waar ik liever alleen het categorietotaal wil zien.
Ik zou dit rapport ook graag in Excel willen kunnen sorteren. Het zou handig zijn om het te sorteren op totale uitgaven en het vervolgens weer op categorie te sorteren. Natuurlijk zou ik ongedaan kunnen maken, maar ik zou graag categorieën willen die alfabetisch van aard zijn. Kortom, het maakt me niet uit hoe Quicken de omtrek opmaakt.
Oude lezers zullen zich mijn minachting herinneren voor het overzichtsformaat dat wordt gebruikt door draaitabellen in Lege cellen van draaitabel invullen met de Ga naar speciaal-tip. We hebben hier dezelfde situatie. Als het Quicken-rapport slechts een tussenstap is en u wilt kunnen sorteren op categorie, is het overzichtsformaat verschrikkelijk. Na het sorteren op totalen en vervolgens op categorie, wordt de categorie Auto: Verzekering verkeerd gesorteerd in het gedeelte "I" van het rapport. Voor de categorieën waarvoor ik alleen het totaal bewaar, worden ze verkeerd gesorteerd in de "T" -sectie van het rapport.
Er waren twee hulpprogramma's waarvan ik dacht dat ze deze situatie zouden verlichten. Hulpprogramma één heet instorten. Wanneer deze macro wordt aangeroepen, wordt een subcategorie samengevouwen tot een enkele regel met een juiste categorienaam. In het bovenstaande voorbeeld zal het uitvoeren van de macro terwijl de celwijzer ergens in de rijen 34 tot en met 38 staat, de categorie in A38 vervangen door "Computer" en rijen 34 tot en met 37 verwijderen.
Hulpprogramma twee is voor de categorieën waarin ik de subcategoriedetails wil zien, maar ik heb de kop, de gestippelde subtotaallijn of het categorietotaal niet nodig. Dit hulpprogramma heet Fill. Het zal de juiste categorienaam vinden en elke subcategorie voorvoegsel met de categorie. In het bovenstaande voorbeeld zal het uitvoeren van de macro terwijl de celwijzer ergens in de rijen 24 tot en met 30 staat, resulteren in het wijzigen van de cellen A25: A28 in een indeling zoals "Auto: Verzekering". Rijen 24, 29 en 30 worden verwijderd.
Aan de rechterkant is mijn verbeterde versie van het rapport. Door Collapse en Fill toe te wijzen aan sneltoetsen, kon ik deze wijzigingen met slechts een paar toetsaanslagen aanbrengen. Het is nu gemakkelijk om het rapport te sorteren, wetende dat het rapport kan terugkeren naar de oorspronkelijke volgorde door de categorie te sorteren.
Als u nieuw bent bij macro's, bekijk dan Introductie van de Excel VBA-editor.
Nadat u de macro heeft gekopieerd, kunt u een sneltoets toewijzen door deze stappen te volgen:
- Kies Macro's en vervolgens Macro in het menu Extra
- Markeer de Vulling-macro. Klik op Opties. Typ een letter in het veld Snelkoppeling. Ik gebruik f voor Fill. Klik OK
- Markeer de macro voor samenvouwen. Klik op Opties. Kies een letter voor een snelkoppeling, maar blijf uit de buurt van c, aangezien Ctrl + c de gebruikelijke snelkoppeling is
- voor Bewerken> Kopiëren. Klik OK
- Sluit het macrodialoogvenster met Annuleren.
Als onderdeel van zijn zoektocht om een add-in voor een dag te ontwerpen, heeft de zomerstagiaire Anhtuan Do de volgende macro's gemaakt.
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
