[Excel] formule overnemen bij Insert Row

[TheRebell]

Hallo,

ik ben met excel (2003) aan het spelen en loop tegen een probleem aan. Ik heb een sheet met (onder andere) een kolom (A) waar een drop-down in staat en een kolom (B ) met een formule. De inhoud van de cellen in B is afhankelijk van de keuze in A. Tevens zit er een conditional format op cellen in kolom B. (er zijn nog veel meer kolommen in de sheet maar die zijn hiervoor niet van toepassing)

probleem is nu dat als je een nieuwe regel wilt invoegen, via welke manier dan ook, dat excel netjes een rij invoegt en de drop-down overneemt van de regel erboven. Ook de formating wordt overgenomen maar de formule juist niet
In mijn opties heb ik "extended data range formats and formulas" aangevinkt. Ook wat MS hierover zegt (minimaal 3 van de 5 voorgaande regels moet de formule hebben) is van toepassing maar helpt niets.

Mijn idee was om evt het insert row event af te vangen en dan een nieuwe regel via een macro toe te voegen, al heb ik geen idee hoe ik dat event af vang (eea geprobeerd via de onChange).

heeft iemand dit probleem ook (eens) gehad en opgelost of weet iemand een manier? Handig om nog te vermelden dat kolom B (met de formule) locked is en mensen de formule dus niet zelf kunnen doortrekken, Evnige restricties tbv de format met formule met de beveiliging van de kolom zijn er niet (tested).

[Reptile209]

Edit je bericht hierboven even en zet een spatie tussen de B en de ) in de tweede regel; die staat daar zo stom

Misschien kan je hier wat mee: een voorbeeld om het verwijderen van een rij te detecteren via een OnChange event. Als je bijhoudt hoeveel rijen je hebt, en hoeveel het er zijn bij een onchange, moet je volgens mij kunnen bepalen dat er een rij is ingevoegd. Via Target vind je dan de nieuwe rij om daar je formules in te stoppen.
Eventueel kan je macro tijdelijk de locking opheffen en daarna weer aanzetten.

[ Voor 12% gewijzigd door Reptile209 op 15-12-2008 22:24 ]

[TheRebell]

hallo,

an sich heb ik nu een stukje wat kijkt of er een row wordt toegevoegd en welke rij dit nu is.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.UsedRange.Rows.Count > glOldRows Then
            'MsgBox "Row added cell: A" & Target.Row

            Rows(Target.Row).Select
            Selection.Insert Shift:=xlDown
            Range("B" & Target.Row - 1).Select
            Selection.AutoFill Destination:=Range("B" & Target.Row - 1, "B" & Target.Row), Type:=xlFillDefault
            Range("B" & Target.Row - 1, "B" & Target.Row).Select
    End If
    
    glOldRows = Me.UsedRange.Rows.Count


End Sub

Private Sub Worksheet_Activate()
    glOldRows = Me.UsedRange.Rows.Count
End Sub

Wat ik nu alleen nog goed moet hebben is dat als ik een rij invoeg deze de formule die erin staat ook over moet nemen. Het huidige stukje (zie boven) voegt een hele lading regels toe wat weer teveel van het goede is maar doet ook niks met de formule (in de 2e kolom).

Ik had een gedeelte met de macro recorder opgenomen om de formatting goed te krijgen maar met het aanpassen zodat hij de pas toegevoegde row gebruikt gaat het fout

[Verwijderd]

Je kunt hier de property FormulaR1C1 voor gebruiken.

[ Voor 5% gewijzigd door Verwijderd op 16-12-2008 17:49 ]

[TheRebell]

ik heb het voor elkaar door het volgende er tussen te zetten

code:

Rows(Target.Row).Select
Range("B" & Target.Row - 1).Select
Selection.AutoFill Destination:=Range("B" & Target.Row - 1, "B" & Target.Row), Type:=xlFillDefault
Range("B" & Target.Row - 1, "B" & Target.Row).Select

het enige wat ik nu krijg is dat de regel waar ik wat op wil invoegen als een gek gaat knipperen voor ~1sec. De optie ScreenUpdating op False zetten (en daarna op true) helpt helaas niet. Sterker; het zorgt er dan voor dat heel excel enen knippert ipv alleen de regel die je wilt toevoegen.

Iemand een idee?

[Verwijderd]

lijn 1 & 4 zijn overbodig. 2 & 3 voeg je samen tot

Visual Basic:

Range("B" & Target.Row - 1).AutoFill Destination:=Range("B" & Target.Row - 1, "B" & Target.Row), Type:=xlFillDefault

en de propertytip van playroll is meer dan het onderzoeken waard

[TheRebell]

Verwijderd schreef op dinsdag 16 december 2008 @ 18:22:
lijn 1 & 4 zijn overbodig. 2 & 3 voeg je samen tot

Visual Basic:

Range("B" & Target.Row - 1).AutoFill Destination:=Range("B" & Target.Row - 1, "B" & Target.Row), Type:=xlFillDefault

en de propertytip van playroll is meer dan het onderzoeken waard

Hmm, die property ziet er inderdaad wel interessant uit. Alleen snap ik 1 ding niet. Ik heb niet puur een formule (zoals een SORT of een SUM oid) maar een vergelijking tov de pulldown waarde:

Een voorbeeldje van in dit geval rij 21 maar het moet natuurlijk voor elke nieuw toe te voegen rij gelden (die overigens ergens tussen of direct aansluitend wordt toegevoegd).

code:

=IF(A21="done"; "done"; IF(A21="not done"; "not done"; IF(A21="bzzy"; "bzzy"; "n/a")))

Hoe zorg je er dan nu voor dat hij die kolom/rij waarden over neemnt met die property

*zucht*
ik ben het niet helemaal goed aan het uitleggen: het is de bedoeling dat alle formules worden overgenomen, dus over de gehele regel (ongeveer van kolom A tm EZ)
Het voorgaande deed dit alleen voor kolom B...

[ Voor 10% gewijzigd door TheRebell op 16-12-2008 20:22 ]

[Reptile209]

De makkelijkste truuk met VBA is om een macro op te nemen (Tools > Macro > Record new macro) en dan handmatig de handelingen uit te voeren. Voor het "doortrekken" van B10:C10 naar rij 11 krijg ik iets als dit:

Visual Basic:

Range("B10:C10").Select
Selection.AutoFill Destination:=Range("B10:C11"), Type:=xlFillDefault

Als alternatief kan je ook op basis van het rijnummer (dat je al hebt vanuit je onchange) de formule helemaal samenstellen, maar dat is een stukje meer werk denk ik.

[TheRebell]

bijkomend probleem is nu dat niet alleen de pull-downs en formules worden gekopieerd naar de nieuwe regel maar ook normale platte tekst uit cellen en, misschien nog wel minder, ook de selectie van de pull-down.
Zou je dat niet kunnen "resetten" oid na de autofill

[Reptile209]

Pfff... dan pak je met Range().Select toch alleen de delen die je wèl wil kopieeren? Desnoods met een apartie kopieer-actie per gedeelte? En met je macro kan je gewoon een andere waarde in de cellen met de drop-downs geven, zodat je je gewenste default hebt.
Dit had je allemaal kunnen ontdekken door een macro op te nemen met alle handmatige acties, kan eigenlijk niet missen. Kortom: een beetje uitproberen kan geen kwaad!

[TheRebell]

Reptile209 schreef op woensdag 17 december 2008 @ 20:08:
Dit had je allemaal kunnen ontdekken door een macro op te nemen met alle handmatige acties, kan eigenlijk niet missen. Kortom: een beetje uitproberen kan geen kwaad!

true true. Eea heb ik ook gedaan met de recorder. Wat het allemaal iets lastiger maakt is dat die formules dus niet altijd in dezelfde kolom staan (er zijn meerdere bestanden waar die code in komt), maar het is opgelost. Na de copy actie loop ik door de cellen van de range heen en verwijder in inhoud ervan behalve als er een formule in staat. Niet de meest mooie oplossing maar hij werkt wel

[Verwijderd]

Knippert je geselecteerde cel dan nog steeds zo veel? In dat geval kun je dan misschien beter PasteSpecial icm. xlPasteFormulas gebruiken.

[TheRebell]

Verwijderd schreef op vrijdag 19 december 2008 @ 21:18:
Knippert je geselecteerde cel dan nog steeds zo veel? In dat geval kun je dan misschien beter PasteSpecial icm. xlPasteFormulas gebruiken.

hij knippert volgens mij niet meer (of niet veel meer tenminste). Ik zal er nog eens naar kijken en ook eens naar xlPasteFormulas/PasteSpecial kijken. Thnx