30. marts 2014 - 19:45Der er
27 kommentarer og 1 løsning
Rangering af tabeller
Rangering
Jeg har i et regneark ca 250 tabeller. De er på 10 rækker hver og mellem 5-10 kolonner. Kolonne 1 er afdelingsnavn Efterfølgende kolonner er budget mål, opnået salg periode og salg åtd. Den sidste kolonne er målopfyldelse.
Tal til tabellerne hentes via opslag fra en stor datafil.
Pt. er alle tabeller sorteret efter kolonne 1 = afdelingsnavn Set ønsker jeg at lave om så så de er sorteret efter målopfyldelse.
Hvordan gør jeg det smartest, så jeg ikke skal til at flytte alle opslag til et nyt område i regnearket og så lave rangering med Plads() i den nuværende tabel? Kan det gøres med en VBA kode eller lægge noget sortering henover det eksisterende?
Jeg har prøvet at stille spørgsmålet tidligere og her blev jeg opfordret til at finde en løsning med VBA: http://www.eksperten.dk/spm/993184
Har set dit upload. Hvad mener du med følgende"" ""?: "Løsningen med at sortere efter målopfyldelse er der, men da der er 250 tabeller og det ""sorteringen kan være forskellig fra gang til gang"" er det noget omstændigt."
Supertekst> Jeg mener at løsningen skal håndtere 250 selvstændige tabeller. Havde der kun været 1 ville jeg lave det manuelt fra gang til gang. Men når der er 250, stå tager det meget tid. Rangeringen ændres hver måned, så det er en løsning der skal håndteres løbende, så en manuel løsning vil betyde 250 * 12 = 3000 rangeringer pr. år.
Tabeller betyder små overblik på 10 rækker med 5-10 kolonner. Dvs. der typisk er 15 tabeller på et regneark.
Rem Anbringes i ThisWorkbook Rem ======================== Public Sub sorteringAfTabeller() Const sortOverskrift = "Målopfyldelse"
Dim nn As Name, arkNavn As String, ræk1 As Integer, rækX As Integer, antalKol As Integer, sortKol As Integer antalNavne = ActiveWorkbook.Names.Count Application.ScreenUpdating = False
For Each nn In ActiveWorkbook.Names Rem marker tabel arkNavn = isolerArkNavn(nn) ActiveWorkbook.Sheets(arkNavn).Select
UdførSortering arkNavn, ræk1 + 1, rækX, antalKol, sortKol Next End Sub Private Sub UdførSortering(arkNavn, fraRæk, tilRæk, antalKol, sortKol) ActiveWorkbook.Worksheets(arkNavn).Sort.SortFields.Clear ActiveWorkbook.Worksheets(arkNavn).Sort.SortFields.Add Key:=Range(Cells(fraRæk + 1, sortKol), Cells(tilRæk, sortKol)), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(arkNavn).Sort .SetRange Range(Cells(fraRæk, 1), Cells(tilRæk, antalKol)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Private Function findSortKolonne(ræk1, antalKol, overskrift) Dim kol As Integer For kol = antalKol To 1 Step -1 If Cells(ræk1 + 1, kol) = overskrift Then findSortKolonne = kol Exit Function End If Next kol Rem ** Fejl ** Overskrift kan ikke identificeres Stop End Function Private Function isolerArkNavn(nn) Dim wt As Variant, arkNavn As String wt = Split(nn, "!") arkNavn = Replace(wt(0), "=", "") arkNavn = Replace(arkNavn, "'", "") isolerArkNavn = arkNavn End Function
Jeg har prøvet at teste og har følgende kommentarer:
- Jeg har svært ved at se om makroen sorterer alle tabellerne - Den stopper ved "stop". Når jeg så prøver at starte den igen, så får jeg en fejl 400?? - Skal jeg indsætte den på alle ark? Kan den kører på alle ark på en gang, så jeg er fri for at starte den 30 gange? - Jeg har i et andet spørgsmål uploaded en prøvefil: http://gupl.dk/709060/ - Her har jeg opslag. Jeg tror man bliver nødt til at indsætte hele arket som værdier for at få det til at virke. - Kan du prøve at beskrive lidt mere hvad den gør - evt. som hjælpetekst i makroen
- VBA-koden indsættes i ThisWorkbook i VBA-vinduet (Alt+F11) - Alle tabeller skal navngives - Kolonne med Målopfyldelse skal have denne overskrift - kan den overskrift ikke findes - stopper processen
Du kan prøve at åbne VBA-vinduet og så "steppe" igennem koden med F8. I den første linje efter erklæringerne øverst = "antalNavne = ActiveWorkbook.Names.Count" kan du ved at pege på antalNavne aflæse det antal oprettede navne, der findes i filen. Processen skal blot være nået til linjen efter denne.
Har en model der har tabeller på 2 ark - den kan du få hvis du sender en mail. @-adresse under min profil.
Følgende kommentarer: - Du har indsat afdelingsnummer ud for hver tabel. Det er der ikke i "virkeligheden". Der står de kun en gang og så laves der opslag ud fra dem. - Det betyder også at makroen ikke virker hos mig, da opslagene hele tiden modvirker rangeringen. - Derfor er det nok nødvendigt at "kopier" og "indsætte som vædier" på de ark, hvor der er tabeller der skal rangeres.
Jeg kan sagtens leve med denne løsning ved at man kopier arkene og indsætter værdier.
MEN Jeg får en fejl om at jeg er Out of range. Hvad kan det betyde? Det virker som om makroen slet ikke starter på at gå i gang. Kan det være fordi der er angivet 2 områder (tabeller) navngivet som lapper over de tabeller der skal rangeres?
Prøv at se på de navne, der er oprettet. Koden udtrækker arknavn af for de navne der er oprettet - idet dette indgår i definitionen af navnet: Ark Navn & område.
Det er i den sidste funktion arknavn uddrages: Private Function isolerArkNavn(nn) Dim wt As Variant, arkNavn As String wt = Split(nn, "!") arkNavn = Replace(wt(0), "=", "") arkNavn = Replace(arkNavn, "'", "") isolerArkNavn = arkNavn End Function
Opdeler udtrykket for navnet efter "!" Arknavn udtrækkes fra det første element i opdelingen "=" erstattes af "" "'" erstattes af ""
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.