excel makro: markere alle rader mellom to tekst-"strings"

[kjakan2005]

Har så smått begynt å lære meg litt makro-programmering, men sliter veldig med følgende problem:

 

Jeg har en oversikt som kan se ut som følgende:

 

"Department number 150

 

sales income $45

income consultancy $5

total income $50

 

supplies $30

salary $15

total expenses $45

 

profit $5

 

Department number 160

..."

 

Jeg ser etter en makro som klarer å markere all tekst fra og med "deparment number 150" til og med siste cellen over "department number 160". Poenget er at jeg får en oversikt over ulike avdelinger hver måned i et excelark og det er ulikt antall rader i hver avdeling fra måned til måned. Målet mitt med en slik makro er å kunne velge alle cellene/radene som er mellom to "text strings" slik at jeg unngår problemet med at feks "department 150" så og si aldri er å finne i samme rad fra måned til måned.

 

Den ferdige makroen skal klare å markere alle radene mellom disse "text-strings'ene", kopiere teksten til et eget worksheet i samme workbook.

 

Last gjerne opp en eksempel-fil som gjør det lett for meg å forstå tankegangen din...

[wirrumsen]

Litt usikker på om jeg forstår helt hva du mener her. Står dette i excel allerede? Står hver av disse linjene på hver sin rad? Er dette noe du ønsker å konvertere til en bedre tabell?

[kjakan2005]

Jeg har lastet opp et regneark som kan være et eksempel på hva jeg tenker. Jeg sitter med dette regnearket, hvor avdeling 150 begynner i rad 2, avd 160 på rad 147 osv. Jeg ønsker at makroen skal automatisk finne ut hvilke rader som hører til avd 150, lage et nytt worksheet og kopiere inn de aktuelle radene.

 

I dette eksempelet ønsker jeg dermed at det lages tre nye ark hvor du i det første arket vil finne all data fra rad 2 til og med 144, i ark2 vil du få all data fra rad 147 tom 279 og i ark3 vil du finne all data fra rad 282 tom 417. Problemet er at det er ulikt antall rader for hver avdeling fra måned til måned. Eksempelvis slutter avd150 på rad 144 denne måneden, men neste måned slutter den kanskje på rad 151. Da må man ha en makro som tar høyde for dette...

 

Har lagt med to identiske filer (som kan åpnes på ulik måte). Zip-filen må pakkes opp på vanlig måte. Fjerner man *.txt i den andre filen, kan den åpnes i excel.

makro-eksempel.zip

makro-eksempel.xls.txt

[wirrumsen]

Fra 10668 meter over havet:

her har du en løsning som fungerer. Fjern .txt fra fila og importer til VBA. Kjør deretter makroen i regnearket ditt. Lykke til!

eksempel_module.bas.txt

[kjakan2005]

Når du var 10668moh så har du åpenbart brukt flyturen din på å hjelpe meg med makro-jobbingen, setter stor pris på det!

 

Har fått prøvd makroen nå, og ser jo vitterlig veldig bra ut. Jeg får ut et regneark av alle avdelingene som jeg ønsket. Skal prøve å gjøre litt modifikasjoner i arket, så får vi se om det klarer å gjøre alt jeg ønsker =)

[wirrumsen]

Når du var 10668moh så har du åpenbart brukt flyturen din på å hjelpe meg med makro-jobbingen, setter stor pris på det!

 

Har fått prøvd makroen nå, og ser jo vitterlig veldig bra ut. Jeg får ut et regneark av alle avdelingene som jeg ønsket. Skal prøve å gjøre litt modifikasjoner i arket, så får vi se om det klarer å gjøre alt jeg ønsker =)

 

Det var glimrende tidsfordriv der oppe over skyene.

Lykke til videre!

[kjakan2005]

Når du var 10668moh så har du åpenbart brukt flyturen din på å hjelpe meg med makro-jobbingen, setter stor pris på det!

 

Har fått prøvd makroen nå, og ser jo vitterlig veldig bra ut. Jeg får ut et regneark av alle avdelingene som jeg ønsket. Skal prøve å gjøre litt modifikasjoner i arket, så får vi se om det klarer å gjøre alt jeg ønsker =)

 

Det var glimrende tidsfordriv der oppe over skyene.

Lykke til videre!

 

Nå har jeg fått sett litt mer på makroen du lagde i høyden.

 

Ser at den gjør nesten alt jeg ønsker at den skal gjøre, men det mangler litt. Eksempelet jeg brukte var ikke helt reelt siden jeg skrev "avdeling 150/160/170 osv". I praksis er avdelingsnavnene noe annerledes. De er feks "501 forskningsenhet", "505 fellesavdeling" osv. Jeg har kalt arket hvor jeg legger alle tallene for "DUMP".

 

Jeg har lagd filer i en katalog (la oss kalle den c:\rapporter) hvor jeg har lagd en tom fil som heter "avd501 regnskap.xls" (og så videre. Kan muligens fjerne mellomrom hvis det er lettere for makroen). Jeg ønsker et par ting:

- i selve dump filen (hvor makroen er) ligger det kun et ark ("DUMP"). Denne måneden var tallene for august (det aktuelle månedsnavnet står alltid i celle B1 - kan være lurt å knytte navnet til denne cellen). Jeg ønsker at makroen skal splitte på samme måte som tidligere (men klare det samme selv om det nå ikke står "avdeling 150" osv, men "501 forskningsenhet" osv). Så skal makroen kopiere alle radene tilknyttet "501 forskningsenhet", åpne filen c:\rapporter\avd501 regnskap.xls, lage et nytt ark som heter august (som helst legger seg som ark nr 2, hvor ark nr1 blir et oppsummeringsark, ark nr2 blir sist måned, ark nr3 blir nest siste måned osv). Deretter skal makroen lukke c:\rapporter\avd501 regnskap.xls. Så skal makroen gjøre det samme med avdelingen "505 fellesavdeling" og lage et ark i filen "avd505 fellesavdeling.xls"...

 

En mulighet er å markere alle rader knyttet til en avdeling, lage et nytt ark som heter august (eller samme som verdi i celle B1), klippe ut arket slik at det kommer som et nytt ark i den filen til den aktuelle avdelingen (feks "avd501 forskningsenhet.xls") som nr 2 i rekkefølgen der. Deretter kan man gå tilbake til DUMP-regnearket hvor makroen kjøres, markere rader for den neste avdelingen, lage et nytt ark osv osv...En annen måte er å lage en mye smidigere og bedre skrevet makro-tekst som er mer generisk og fungerer bedre (på lik linje med makroen som ble forfattet i skyene...)

 

Det var en lang beskrivelse, men ikke så veldig mye annen funksjonalitet i forhold til makroen som ble foreslått tidligere...Jeg kan gjerne beskrive nærmere hvis nødvendig.

 

Har lagt med en fil (som tidligere i zip og txt format) som viser reelt sett hvordan DUMP filen ser ut...

makro-eksempel3.zip

makro-eksempel3.xls.txt

Endret 18. oktober 2011 av kjakan2005

[wirrumsen]

Da prøver vi på nytt her. Har gjort om litt. Brukte en Select Case for å identifisere avdelingene.

Det kan være du får en subscript out of range-melding når du kjører makroen, og at den viser til der hvor man legger til nytt ark i regnearket. Da er det i tilfelle pga at du har norsk excel og at du da må endre til "Ark" i stedet for "Sheet". Men jeg har skrevet kommentar på det på de to aktuelle linjene. Du vet det sikkert, men sier det likevel: i debug-modus kan du endre på dette og trykke på "play"-knappen for å kjøre gjennom resten av makroen.

eksempel_mod.bas.txt

[kjakan2005]

Makroen fungerer nær perfekt, bortsett fra at når jeg legger inn mer enn 11 avdelinger, så får stopper den opp ved linjen "j = j +1". Etter det jeg vet er det ikke noe max antall ark i excel (har excel 2003), og kan ikke forstå hvorfor det stopper på 11 avdelinger. 10 går greit, men 11 eller mer fungerer ikke...

[wirrumsen]

Makroen fungerer nær perfekt, bortsett fra at når jeg legger inn mer enn 11 avdelinger, så får stopper den opp ved linjen "j = j +1". Etter det jeg vet er det ikke noe max antall ark i excel (har excel 2003), og kan ikke forstå hvorfor det stopper på 11 avdelinger. 10 går greit, men 11 eller mer fungerer ikke...

 

Det er fordi jeg definerte array til bare å inneholde 10 instanser. Skal du ha flere avdelinger, må du redefinere array. Altså de som er Dim avdeling(), start() og ende(). Skriv der høyere antall enn 10, evt bruk redim-prosedyre. Da skal det fungere

[kjakan2005]

makan. så dum kan man være...

har veldig bra løsning på problemet Wirrumsen , betydelig bedre skrevet kode enn jeg noensinne ville fått til.

[wirrumsen]

makan. så dum kan man være...

har veldig bra løsning på problemet Wirrumsen , betydelig bedre skrevet kode enn jeg noensinne ville fått til.

 

Man kan jo ikke vite alt heller, og man lærer så lenge man lever

[kjakan2005]

et par ting ang koden (la den inn i innlegget siden det er like greit)...

 

- hva er poenget med "attribute VB name"? Denne har jeg bare slettet til nå (siden det ikke fungerer å ha den liggende øverst i teksten). Bruker excel 2003 forøvrig.

- "i" teller rader, "j" teller antall avd i bruk". Det må vel derfor være "j" jeg må endre hvis jeg skal ha flere avdelinger? Jeg har prøvd å endre flere av setningene med tallet 10, men får ikke til å ha mere enn 4 avdelinger (jeg har da selvsagt lagt til flere avdelingsnavn under "select case" partiet)

- forstår ikke hele koden 100%, men virker for meg som at koden markerer at starten på den første avdelingen er i rad nr 10, og slutter når det blir to tomme rader (er det riktig?). Ikke helt sikker på hvordan koden finner ut når avdeling nr2 starter osv.

 

- hvilke ting vil du endre hvis du feks skal ha inn 30 avdelinger da? (jeg vil selvsagt kopiere opp "Case "navn på avdeling" isAvd = True" 30 ganger og skrive riktig navn på alle avdelinger) , men er uiskker på hvilke parametre jeg i tillegg må skifte...(tror forøvrig excel skal takle å lage 30 ark i et regneark, så det burde ikke sette en stopper for dette).

 

 

 

Attribute VB_Name = "Module11"

Sub ark_avdelinger()

'Denne makro identifiserer begynnelse og slutt for hver avdeling og kopierer innholdet over i egne ark

 

Dim i As Integer 'teller antall rader

Dim slutt As Boolean 'instruerer loop om  slutte

Dim tom As Integer 'teller tomme rader

Dim j As Integer 'teller antall avdelinger for bruk i array

Dim start(1 To 10) As Integer 'startrad per avdeling

Dim ende(1 To 10) As Integer 'siste rad per avdeling

Dim avdeling(1 To 10) As String

Dim isAvd As Boolean

 

i = 10

j = 0

slutt = False

isAvd = False

 

Do Until slutt = True

If Cells(i, 1).Value = "" Then

tom = tom + 1

Else

tom = 0

End If

 

If tom = 2 Then

ende(j) = i

ElseIf tom = 3 Then

slutt = True

End If

 

Select Case Cells(i, 1).Value

Case "501 FORSKNINGSENHET"

isAvd = True

Case "505 FELLESAVDELING"

isAvd = True

Case "510 UTENLANDSK"

isAvd = True

Case "515 UTENLANDSK2"

isAvd = True

Case Else

isAvd = False

End Select

 

If isAvd = True Then 'Left(Cells(i, 1).Value, 8) = "Avdeling" Then

j = j + 1

start(j) = i

avdeling(j) = Cells(i, 1).Value

End If

i = i + 1

Loop

 

Dim maks As Integer

maks = j

 

Dim l As Integer 'teller seg frem til riktig sheet

For j = 1 To maks

'kopierer innhold for hver avdeling til eget ark

'f¯rst header row, deretter innhold

Sheets("DUMP").Select

Rows("9:9").Select

Selection.Copy

 

Sheets.Add after:=Sheets(Sheets.Count)

l = Application.Sheets.Count - 1

Sheets("Sheet" & l).Select 'hvis norsk excel: endre "Sheet" til "Ark"

Sheets("Sheet" & l).Name = avdeling(j) 'hvis norsk excel: endre "Sheet" til "Ark"

 

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

 

Sheets("DUMP").Select

Rows(start(j) & ":" & ende(j)).Select

Selection.Copy

Sheets(avdeling(j)).Select

Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Next j

End Sub

[wirrumsen]

et par ting ang koden (la den inn i innlegget siden det er like greit)...

 

"Attribute VB name" ser jeg ikke selv vitsen med. Det er vel noe import-greier, regner jeg med, og er ikke noe jeg har kodet selv.

 

Array-lengden bestemmes ikke av j. j teller antall avdelinger fortløpende og holder styr på hvilken posisjon hver avdeling har i array. Men lengden på array (dvs antall posisjoner tilgjengelig i array) bestemmes der hvor du definerer array'et. Med andre ord der hvor du ser det står "Dim start(1 To 10)" , "Dim ende(1 To 10)" og "Dim avdeling(1 To 10)". Disse forteller deg at det da er 10 posisjoner i arrayene. Øk 10 til for eksempel 20 om det skulle holde, eller så mange avdelinger du måtte ønske.

 

Angående koden for øvrig: ja, første avdeling har start i rad nr 10 etter hva jeg så i dump'en. Derav starten. To tomme rader betyr at det ligger an til at ny avdeling kommer. Dersom det blir tre tomme rader på rad, hopper makroen ut av loopen siden det betyr at man har kommet til slutten. Altså:

- en tom rad: helt normalt

- to tomme rader: ny avdeling

- tre tomme rader: slutt

 

Dersom du vil ha inn 30 avdelinger endrer du som du sier Case. I tillegg øker du array-størrelsen (som nevnt over). Du kan fritt legge til så mange ark du vil i Excel, så lenge du har nok systemressurser. Så 30 ark er null problem

[kjakan2005]

God morgen. Jeg endrer til 11 (for å ta et lite steg før jeg går over til 30 avdelinger) avdelinger. Jeg endrer fra 10 til 11 i Dim avdeling(), start() og ende(). Da går koden fint en stund, og nye ark blir lagd for 10 avdelinger. Når det kommer til avdeling nr 11, da stopper koden opp i setningen : Sheets("Sheet" & l).Name = avdeling(j) 'hvis norsk excel: endre "Sheet" til "Ark"

[wirrumsen]

God morgen. Jeg endrer til 11 (for å ta et lite steg før jeg går over til 30 avdelinger) avdelinger. Jeg endrer fra 10 til 11 i Dim avdeling(), start() og ende(). Da går koden fint en stund, og nye ark blir lagd for 10 avdelinger. Når det kommer til avdeling nr 11, da stopper koden opp i setningen : Sheets("Sheet" & l).Name = avdeling(j) 'hvis norsk excel: endre "Sheet" til "Ark"

 

Kan du legge ut en ny dump sammen med den kode du nå har?

[Harald Staff]

Det er noen restriksjoner på navngiving av Excelark. Hva heter avdeling 11?

[kjakan2005]

Her er dump-filen. Dere kommer til å le når dere ser hva jeg videre har gjort. Er en pragmatisk sjel som bruker det lille jeg kan av VBA fra andre makroer. Jeg kopierer hver avdeling inn i et regneark som heter det samme hver måned (men resultatet blir at det kommer et ekstra ark hver måned i hver avdelings-fil). Så har jeg gjort det samme nedover for hver eneste avdeling. Nå har jeg bare satt inn 18 avdelinger (hvorav kun er "aktive", har satt "'" foran avdeling 11 til 18 som dere kan se). Sikkert fin makro-koding av deg helt øverst, så blir det dårligere og dårligere nedover, men det fungerer jo (ihvertfall for inntil 10 avdelinger)...

Endret 21. oktober 2011 av kjakan2005

[wirrumsen]

Her er dump-filen. Dere kommer til å le når dere ser hva jeg videre har gjort. Er en pragmatisk sjel som bruker det lille jeg kan av VBA fra andre makroer. Jeg kopierer hver avdeling inn i et regneark som heter det samme hver måned (men resultatet blir at det kommer et ekstra ark hver måned i hver avdelings-fil). Så har jeg gjort det samme nedover for hver eneste avdeling. Nå har jeg bare satt inn 18 avdelinger (hvorav kun er "aktive", har satt "'" foran avdeling 11 til 18 som dere kan se). Sikkert fin makro-koding av deg helt øverst, så blir det dårligere og dårligere nedover, men det fungerer jo (ihvertfall for inntil 10 avdelinger)...

 

Får ikke lastet ned og åpnet zip-filen. Men dersom du har skrevet avdelingene med en ' så kan det være grunnen til at det blir kluss. Det tegnet er nemlig ulovlig å bruke i navn på arkene.

[kjakan2005]

Her er dump-filen. Dere kommer til å le når dere ser hva jeg videre har gjort. Er en pragmatisk sjel som bruker det lille jeg kan av VBA fra andre makroer. Jeg kopierer hver avdeling inn i et regneark som heter det samme hver måned (men resultatet blir at det kommer et ekstra ark hver måned i hver avdelings-fil). Så har jeg gjort det samme nedover for hver eneste avdeling. Nå har jeg bare satt inn 18 avdelinger (hvorav kun er "aktive", har satt "'" foran avdeling 11 til 18 som dere kan se). Sikkert fin makro-koding av deg helt øverst, så blir det dårligere og dårligere nedover, men det fungerer jo (ihvertfall for inntil 10 avdelinger)...

 

Får ikke lastet ned og åpnet zip-filen. Men dersom du har skrevet avdelingene med en ' så kan det være grunnen til at det blir kluss. Det tegnet er nemlig ulovlig å bruke i navn på arkene.

 

Jeg la inn et innlegg, men etter å ha sett litt mer på saken så fant jeg det ut. Du satte meg på saken ved å snakke om avd navnet. Jeg hadde / i det ene avdnavnet. Helt øverst i makroen la jeg inn et par setninger som automatisk skifter navnet til avdelinger med / i seg (eller har mer enn 31 tegn).

 

Endret innlegget for at du ikke skal bruke tid på noe jeg finner ut av selv. Ser jo lovende ut i forhold til hva jeg trenger dette her =)

Endret 21. oktober 2011 av kjakan2005