[Løst] Excel: Flytte rad automatisk til et annet ark om verdi i kolonne blir oppnådd

[Fahlar]

Hei,

 

Spør dette spørsmålet her da jeg tidligere har fått god hjelp på dette forumet og jeg håper på en gjentagelse.

 

Jeg har et problem på jobb. Jeg forsøker å få til noe som i mitt hode burde være enkelt. Men da jeg kun er selvlært i excel kan det være noen løsninger jeg rett og slett ikke vet om. 

 

Jeg forsøker å lage et skjema som med en gang "OK" eller lignende blir ført inn i en celle i D-kolonnen flytter den aktuelle raden automatisk over til Ark 2. Det er også en fordel om den aktuelle D-cellen får grønn fyllfarge. 

 

Da jeg ikke fikk til å få dette inn i en HVIS-formel prøvde jeg meg på en makro. Jeg har klart å lage en makro som automatiserer det, men da må jeg bruke en avhukingsboks under "fra kontroller" under "Utvikler". Jeg klarer å tilordne en makro til den, slik at med en gang den blir huka av flytter raden seg over til Ark 2. 

 

Og det er der jeg står fast nå. For når jeg tilegner den makroen til alle avhukingsboksene på skjemaet blir alle automatisk flyttet over til rad 2 av Ark 2, og dermed overstyrer det som var der fra før. 

 

Jeg er fullstendig klar over at min løsning nok ikke er den beste, men er det noen som har en god løsning til meg? Gjerne en som jeg kan skrive "OK" eller "x" eller lignende i cellene i D-kollonen, og det da setter i gang en automatisert handling som farger den aktuelle D-kolonnen grønn og flytter raden over i Ark 2 nedover etter tur og sletter raden som blir stående tom i Ark 1.

[Horge]

Høres ut som du er på jakt etter "Worksheet_SelectionChange". Da kan du få en makro til å trigges når (noen definerte) celler endrer verdi.

[Harald Staff]

Beklager at jeg er sent på denne. Til gjengjeld skal du få et lengre kronglete svar

 

Først, Worksheet_SelectionChange er den som kjører når du går fra en celle eller et område til et annet. Den som kjører ved endring av celleverdi heter Worksheet_Change.

 

Men ikke gjør dette. Ingen andre steder i en datamaskin skjer sånt ved innskrivning, så dette er ikke brukervennlig, knapt forståelig. Man klikker vanligvis på "Overfør" eller "OK" eller noe tilsvarende. Det kan være en knapp eller tekstboks med tilordnet makro på frossen rad 1, eller oppe på båndet. Eller noe.

 

Her er en makro som skriver kolonne A-D fra Ark1 over i ledig rad nedenfra i Ark2, og spør så om skjemaet i Ark1 skal tømmes. Du sier ingenting om hvor dataene dine er, så jeg håper du kan tilpasse dette eller spørre tilbake. I alle fall, tilordne denne til en knapp eller et objekt (Høyreklikk, Tilordne makro):

Sub Overfoer()

Dim Src As Worksheet 'innskrivningsark

Dim Trg As Worksheet 'ark det overføres til

Dim Rsrc As Long 'rad det overføres fra

Dim Rtrg As Long 'rad det skrives til

Dim C As Long

Set Src = ThisWorkbook.Sheets("Ark1") 'endre til rett arknavn

Set Trg = ThisWorkbook.Sheets("Ark2") 'endre denne og

Rsrc = ActiveCell.Row 'raden du står i i Ark1

Rtrg = Trg.Cells(Trg.Rows.Count, 1).End(xlUp).Row + 1 'ledig rad under

For C = 1 To 4 'kolonne A tom D

    Trg.Cells(Rtrg, C).Value = Src.Cells(Rsrc, C).Value
    
Next

If MsgBox("Raden ble oveført. Skal vi tømme skjemaet?", vbYesNo + vbQuestion) = vbYes Then

    For C = 1 To 4 'kolonne A tom D
    
        Src.Cells(Rsrc, C).Value = ""
        
    Next
    
End If

End Sub

Beste hilsen Harald

[Fahlar]

Takk for hjelpen, men det fungerer ikke helt slik jeg ville. Det er mulig jeg rett og slett kan for lite og forsto for lite av ditt svar... Det er egentlig et simpelt evalueringsskjema jeg vil lage. Dataene vil se slik ut:

 

 

             Kolonne A     Kolonne B     Kolonne C     Kolonne D

Rad 1                           Kan ikke          Kan litt            Kan

Rad 2      Mål 1

Rad 3      Mål 2

Rad 4      Mål 3

Rad 5      Mål 4

 

 

Dette befinner seg i Ark 1. Det jeg forsøker er at med en gang jeg skriver en X for eksempel i Rad 2, Kolonne D vil hele rad nummer 2 flytte seg til Ark2. Samtidig bør den nå tomme raden slettes slik at det ikke blir unødvendige huller i Ark1. Jeg kan ikke bruke en makro som sveiper hele Ark1 for "X", fordi jeg vil også putte X i kolonne B og C uten at noe skal skje. 

 

Ok. Jeg forsøkte å registrere bevegelsene jeg gjorde, de kom ut slik: 

 

Sub Makro1()

'

' Makro1 Makro

' Forsøk 1

'

 

'

    ActiveCell.Offset(-8, 0).Range("A1").Select

    ActiveCell.FormulaR1C1 = "X"

    ActiveCell.Rows("1:1").EntireRow.Select

    Selection.Cut

    Sheets("Ark2").Select

    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

    Selection.Insert Shift:=xlDown

    Sheets("Ark1").Select

    Selection.Delete Shift:=xlUp

End Sub

 

 

Jeg har tidligere laget en tilsvarende, som brukte en knapp. Ulempen slik jeg med min begrensede kunnskap ser det er at den putter alle radene som blir overført fra Ark1 til den samme raden i Ark2, og dermed blir hele poenget borte. Er det mulig å tilpasse denne til å kopiere radene inn nedover i Ark2? Og er det mulig å tilordne en makro til en tom celle slik at med en gang "X" blir skrevet inn i cellen (I dette tilfelle alle cellene i Kolonne D) blir den aktuelle raden cellen befinner seg på flyttet over til Ark2? Og når jeg først er i gang med spørring; hva er den enkleste måten å putte inn knapper på i dokumentet slik at det ser pent ut, om det er nødvendig?

 

Jeg setter stor pris på tilbakemeldinger, og det blir lettere for meg om det er mulig å forklare det litt lett Heh

Endret 18. juni 2015 av Fahlar

[Harald Staff]

Jeg skjønte godt hva du spurte etter, og jeg prøvde å forklare hvorfor det er en dårlig idé. Men hvis du absolutt vil heter eventet Worksheet_Change og befinner seg i det enkelte arks modul.

 

Hint om hvordan du skriver til neste ledige rad og ikke rad to finner du i linjen som inneholder ordene End(xlUp).Row. Har du i det hele tatt prøvd koden min eller bare lest den?

[Fahlar]

Jeg skjønte godt hva du spurte etter, og jeg prøvde å forklare hvorfor det er en dårlig idé. Men hvis du absolutt vil heter eventet Worksheet_Change og befinner seg i det enkelte arks modul.

 

Hint om hvordan du skriver til neste ledige rad og ikke rad to finner du i linjen som inneholder ordene End(xlUp).Row. Har du i det hele tatt prøvd koden min eller bare lest den?

 

Jeg prøvde koden din, med fikk den ikke til å fungere. Jeg forsøkte å tilpasse den, men kunnskapen kom nok til kort. Jeg er som sagt på et nybegynnernivå i makroer, og har ingen form for kursing i excel fra før.

For å starte med det helt basic slik at jeg kanskje kan få koden din til å fungere; 

- forstår jeg det rett at din kode skal brukes som en makro til en "kontroll"-knapp hentet fram inne på "utvikler"-fanen (vi bruker Office 2010 på jobben)?

- jeg antok at det var på disse stedene i koden jeg skulle endre navn til korrekt navn på arkene (Ark1  heter "Evaluering", Ark2 heter "Ferdig")

 

Dim Src As Worksheet 'innskrivningsark

 

Dim Trg As Worksheet 'ark det overføres til

 

og 

 

Set Src = ThisWorkbook.Sheets("Ark1") 'endre til rett arknavn

Set Trg = ThisWorkbook.Sheets("Ark2") 'endre denne og

 

Har en svak følelse at det var feil å endre den første. Stemmer det? Og med de nye navnene, hvordan skal denne delen av koden egentlig stå? Blir det da

 

Set Src = ThisWorkbook.Sheets("Evaluering") 

Set Trg = ThisWorkbook.Sheets("Ferdig") 

 

uten å endre på den første delen? Er det bare det som behøves endres, så bør koden din gjøre det jeg ønsker?

 

Takk for at du tar deg bryet og er overbærende med en newbie som tok seg vann over hodet

[Harald Staff]

Ja du er nær.

Først, åpne VBA-editoren. Sett inn en standard modul med menyen Insert > Module.

 

I det blanke arket som dukker opp, lim inn koden min. Og helt riktig, endre i koden til

 

Set Src = ThisWorkbook.Sheets("Evaluering")

Set Trg = ThisWorkbook.Sheets("Ferdig")

 

Så er vi ferdige i editoren. Lukk og gå til arket Evaluering. Sett inn der et tegneobjekt fra Sett inn-menyen, en tekstboks, rektangel, noe sånt, på arket. Så, høyreklikk på den, velg Tilordne makro, og velg makro Overfoer.

 

Så klikker du en celle i raden du vil overføre, og så klikker du på tingen du har tilordnet makroen til. Meld tilbake når dette virker, så tar vi finpuss og pynt derfra.

 

Beste hilsen Harald

[Fahlar]

Ok, rotet litt, så forsøkte å tilordne makroen din til 4 ulike typer knapper. Med unntak av den siste fikk jeg det samme resultatet.

1. Tekstboks fra "sett inn"-fanen,

2. En firkantet figur fra "Figurer" under "sett inn"-fanen

3. En Knapp fra under "fra kontroller" under "Sett inn" under "utvikler"-fanen

4. En Kommandoknapp fra under "ActiveX-kontroller" under "Sett inn" under "utvikler"-fanen

 

Det som skjer er at den aktuelle raden jeg har markert blir overført. De blir også overført løpende nedover, akuratt slik jeg ville. Men det er to ting som ikke fungerer helt slik jeg forestilte meg:

 

1. Jeg må først markere raden, for så å trykke en knapp et annet sted for å overføre. Litt av det jeg ønsket meg var at jeg kunne overføre hele raden bare ved å taste inn for eksempel "X" i kolonne D, som markerer punktene jeg er ferdig med. Altså unngå det siste steget i din kode. Er det mulig å komme rundt det, eller er vi da over på Worksheet_Change som tydeligvis ikke var den beste løsningen?

2. Skjeamet blir tømt, men raden blir ikke slettet. Jeg har forstått det slik at det visstnok er vanskeligere. Men det som skjer i mitt skjema er da at det blir en haug med huller. Poenget med skjemaet fra min side var at listen i "Evaluering" skal bli mindre, mens listen i "Ferdig" skal bli større. Det skjer jo i praksis, men visuelt sett ser den ikke mindre ut, da det istedet for å slette raden tilfører huller i listen. Det er selvfølgelig ikke verre enn at jeg sletter raden manuelt, men igjen, poenget med å forsøke dette var jo å automatisere det. Er det mulig å skrive om din kode litt slik at den sletter raden istedet for å tømme den?

 

Et siste spørsmål som er mer nysgjerrighet; i mitt forsøk med ActiveX-kontrolleren fikk jeg inntrykk av at denne knappen kjører igang en kode som ligger i selve arket, og at det dermed ikke er en kode som er direkte koblet til ActiveX-kontrolleren. Har jeg rett i det?'

 

Igjen, takk for all hjelp så langt og håper du kan hjelpe meg videre

 

 

 

 

Edit: Vi hadde en diskusjon vi som skal bruke verktøyet og kom frem til at det kan ha en funksjon i seg selv at vi må fysisk markere det man er ferdig med for så å klikke det vekk. Da flytter jeg første punktet over til "nysgjerrig"-kategorien, men vil gjerne fortsatt ha svar på om det er mulig å få til punkt 2

Endret 22. juni 2015 av Fahlar

[Harald Staff]

Hei igjen

 

Ok, takk. klikkeboks, som du nå får til. Endre koden til riktig navn og til sletting av rad slik:

Sub Overfoer()

Dim Src As Worksheet 'innskrivningsark

Dim Trg As Worksheet 'ark det overføres til

Dim Rsrc As Long 'rad det overføres fra

Dim Rtrg As Long 'rad det skrives til

Dim C As Long

Set Src = ThisWorkbook.Sheets("Evaluering")

Set Trg = ThisWorkbook.Sheets("Ferdig")

Rsrc = ActiveCell.Row 'raden du står i i Ark1

Rtrg = Trg.Cells(Trg.Rows.Count, 1).End(xlUp).Row + 1 'ledig rad under

For C = 1 To 4 'kolonne A tom D

    Trg.Cells(Rtrg, C).Value = Src.Cells(Rsrc, C).Value
    
Next

Src.Rows(Rsrc).Delete

End Sub

Så er vi over på posten for nyskjerrig. Da må du først motta kilderad som en ekstern parameter, og dermed ikke definere den i koden. Det blir sånn:

Sub Overfoer(Rsrc As Long)

Dim Src As Worksheet 'innskrivningsark

Dim Trg As Worksheet 'ark det overføres til

Dim Rtrg As Long 'rad det skrives til

Dim C As Long

Set Src = ThisWorkbook.Sheets("Evaluering")

Set Trg = ThisWorkbook.Sheets("Ferdig")

Rtrg = Trg.Cells(Trg.Rows.Count, 1).End(xlUp).Row + 1 'ledig rad under

For C = 1 To 4 'kolonne A tom D

    Trg.Cells(Rtrg, C).Value = Src.Cells(Rsrc, C).Value
    
Next

Src.Rows(Rsrc).Delete

End Sub

Så går du til modulen i arket Evaluering og limer inn denne:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Target(1).Column = 4 Then
    
    If UCase(Target(1).Value) = "X" Then Call Overfoer(Target(1).Row)
    
End If

End Sub

Så overføres ved X eller x i kolonne D. Men altså, sånt pleier foregå med et klikk, ike en tekst.

 

Merk også at når du bruker makroløsninger så har du ikke angrefunksjon. Det er fler grunner til det, men sånn er det altså.

 

HTH. Beste hilsen Harald

[Fahlar]

Tusen takk Harald! 

 

Nå fungerer skjemaet som jeg vil og vi kan gå i gang med å bruke det aktivt. Takk for all hjelp! Jeg har også lært masse her  

 

Med vennlig hilsen

Vegard van der Zalm Gjerløv

[Harald Staff]

Fine greier. Hyggelig, takk for tilbakemelding.

 

Jeg ser at jeg har oversett et spørsmål. ActiveX-kontroller har egne forhåndsdefinerte makroer ja. Du kommer til dem ved å høyreklikke kontrollen og velge "vis kode"/"view code". I tilfellet knapp som heter CommandButton1 kommer du til makroen CommandButton1_Click. Men det er flere, som f.eks. CommandButton1_MouseMove, som kjører når du beveger muspekeren over knappen. Over modulen (det hvite arket) er to nedtrekksmenyer. Velg CommandButton1 i det venstre så kan du velge mellom alle eventene (dvs det som utløser makroen) i det høyre. Det er forskjellige events for rullefelter, tekstbokser og knapper rimeligvis. I tilfellet Textbox kjører ett event ved keydown (begynner å trykke tast), ett annet ved keypress(trykker tast) og et tredje ved keyup (slipper tast). Veldig kraftfulle ting dette.

 

Derfra er organiseringen av kode et spørsmål om stil og vedlikeholdsvennlighet. Ved småoperasjoner skriver jeg makroen rett i eventet, men ved større operasjoner; databasesøk, rapportgenerering og sånt, liker jeg å skrive prosedyren for seg i en standard modul (som vi har gjort i denne tråden), og så bare kalle den fra eventet slik:

Private Sub CommandButton1_Click()

Call Overfoer

End Sub

Så skal nevnes at ActiveX-kontrollene er de mest fleksible og programmerbare, mens skjemakontrollene, og klossene fra tegneverktøymenyen er mer robuste. Nevnes men ikke forklares, det er lørdag :-)

 

Beste hilsen Harald