Ideen til ukens tips kom fra samtale med Dr. M, forfatter av det flotte ukentlige nyhetsbrevet Quicken tips.
Jeg elsker Quicken, men det har absolutt sine irritasjoner. Jeg har en viss memorert rapport i Quicken med kategorier på siden og måneder som går over toppen. Quicken tilbyr muligheten til å skrive ut denne rapporten, men jeg bruker selvfølgelig alltid bare kommandoen Kopier for å kopiere rapporten til utklippstavlen og deretter bruke Rediger> Lim inn i Excel for å kopiere rapporten til utklippstavlen. Denne funksjonen er mye raskere enn den eldre (og fremdeles tilgjengelige) utskriften til et .prn-filalternativ.
Her dukker irritasjonene opp. For det første gadd ikke Quicken å kopiere kolonneoverskriftene med rapporten. Så jeg må legge inn månedens navn manuelt i Excel. Enkelt nok. For det andre inkluderer kategoriene som blir kopiert til utklippstavlen et irriterende disposisjonsformat for kategorier og underkategorier.
Quicken lar deg bruke kategorier og underkategorier for å klassifisere utgiftene dine. I rapporten til venstre brytes bilutgiftene videre av forsikring, bensin, reparasjoner og bilskilt. Kanskje jeg gjorde en dårlig jobb med å sette opp kategoriene mine, men jeg finner ut at jeg har noen kategorier der jeg vil se underkategoridetaljene og andre kategorier der jeg foretrekker å bare se kategorien totalt.
Jeg vil også kunne sortere denne rapporten i Excel. Det ville være nyttig å sortere det etter totale utgifter, og deretter sortere det tilbake etter kategori. Visst, jeg kan bruke angre, men jeg vil ha kategorier som er alfabetiske. Kort fortalt bryr jeg meg ikke om disposisjonsformatet som brukes av Quicken.
I lang tid vil leserne huske min forakt for disposisjonsformatet som brukes av pivottabeller i Fyll ut pivottabelle tomme celler ved hjelp av Gå til spesialtips. Vi har den samme situasjonen her. Hvis Quicken-rapporten bare er et mellomtrinn, og du vil kunne sortere etter kategori, er disposisjonsformatet fryktelig. Etter å ha sortert etter totaler og deretter etter kategori, blir kategorien Auto: Forsikring feil sortert i "I" -delen av rapporten. For kategoriene der jeg bare beholder summen, blir de feil sortert i "T" -delen av rapporten.
Det var to verktøy som jeg trodde ville lette denne situasjonen. Verktøy en kalles kollaps. Når den påkalles, vil denne makroen kollapse en underkategori i en enkelt linje med et riktig kategorinavn. I eksemplet ovenfor vil kjøring av makroen mens cellepekeren er hvor som helst i rad 34 til 38 erstatte kategorien i A38 med "Computer" og slette rad 34 til 37.
Verktøy to er for kategoriene der jeg vil se underkategoridetaljene, men ikke trenger overskriften, den stiple delsummerlinjen eller kategorien totalt. Dette verktøyet heter Fill. Den finner riktig kategorinavn, og foran hver underkategori med kateogrien. I eksemplet ovenfor vil kjøring av makroen mens cellepekeren befinner seg hvor som helst i rad 24 til 30 føre til at celler A25: A28 endres til et format som "Auto: Forsikring". Rad 24, 29 og 30 vil bli slettet.
Til høyre er min forbedrede versjon av rapporten. Ved å tilordne kollaps og fyll til hurtigtaster, var jeg i stand til å gjøre disse endringene med bare noen få tastetrykk. Det er nå enkelt å sortere rapporten, vel vitende om at rapporten kan gå tilbake til den opprinnelige sekvensen ved å sortere kategorien.
Hvis du er ny innen makroer, kan du gå gjennom Introduksjon til Excel VBA Editor.
Når du har kopiert makroen, kan du tilordne en hurtigtast ved å følge disse trinnene:
- Velg Makroer og deretter Makro fra Verktøy-menyen
- Marker Fill-makroen. Klikk Alternativer. I feltet Snarvei skriver du inn en bokstav. Jeg bruker f for Fill. Klikk OK
- Fremhev kollapsmakroen. Klikk Alternativer. Velg et brev for en snarvei, men hold deg unna c, ettersom Ctrl + c er den vanlige snarveien
- for Rediger> Kopier. Klikk OK
- Lukk makrodialogen med Avbryt.
Som en del av sin søken etter å designe et tillegg på en dag, sommerstudent, opprettet 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
