[Excel2003/VBA] Automatisch kolomA selecteren in actieve rij

[dan0s]

Hallo,

Ik ben een totale VBA newbie en heb mij vorige week door een dikke berg VBA googlecode geworsteld. Daarme ben ik een flink eind gekomen maar nu loop ik toch echt vast. Ik weet dat mijn code niet heel erg goed is maar het werkt voor zover ik wil. Ik heb 4 command buttons gemaakt voor het kopieren->plakken van een opgemaakte regel. Die opgemaakte regel staat op de "DATA en INVULSHEET", en wordt gekopieerd naar "Risico-analyse". Tot dusver alles goed. Zie hier de huidige code:

Private Sub hoofdtaak_Click()
'
' Macro4 Macro
' Macro recorded 13-3-2009 by a036822
'

'
Sheets("DATA en INVULSHEET").Select
ActiveSheet.Rows(18).Select
Selection.Copy
Sheets("Risico-analyse").Select
ActiveSheet.Paste

End Sub

Mijn probleem was dat als ik niet eerst kolom A selecteerde de opmaak niet wordt herkend. Dat onderving ik door er het volgende van te maken:

Private Sub hoofdtaak_Click()
'
' Macro4 Macro
' Macro recorded 13-3-2009 by a036822
'

'
Sheets("DATA en INVULSHEET").Select
ActiveSheet.Rows(18).Select
Selection.Copy
Sheets("Risico-analyse").Select
Rows(ActiveCell.Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Dit werkt prima, behalve wanneer ik meerdere rijen selecteer en op het aanmaken van een nieuwe deeltaak druk. Ik wil dan namelijk graag dat alle 5 de rijen als deeltaak worden overgeschreven, nu is dat maar 1 rij.

Voorbeeld:
Ik klik op rij 35, kolomB en klik op "nieuwe deeltaak". Prima, het werkt. Ik selecteer rijen 36+37 kolom B en klik op "nieuwe deeltaak" en krijg alleen een nieuwe deeltaak op rij 36.

Waar gaat het fout? En waar vind ik een oplossing?

Ik zie het even niet meer, en heb echt mijn best gedaan voordat ik heb besloten hier toch om hulp te roepen. Ik verwacht niet van jullie mij even kant en klare code te geven want ik weet hoe het hier werkt dus voor een schop in de goede richting ben ik al bijzonder dankbaar

Alvast bedankt!

Daan

[ Voor 6% gewijzigd door dan0s op 16-03-2009 13:08 ]

[onkl]

Je wilt in het geval van twee rijen eigenlijk twee plak-acties uithalen.
stap 1: Copy de opgemaakte rij.
stap 2: plak naar rij 36
stap 3: plak naar rij 37.
Omdat je natuurlijk ook 3, 4, 10, 100 rijen kan selecteren, los je stap 2 en 3 op in een loop.

Bijvoorbeeld zo:

Visual Basic:

Private Sub hoofdtaak_Click()
'
' Macro4 Macro
' Macro recorded 13-3-2009 by a036822
'

'
Dim cel as range
Sheets("DATA en INVULSHEET").Select
ActiveSheet.Rows(18).Select
Selection.Copy
Sheets("Risico-analyse").Select
'Rows(ActiveCell.Row).Select
for each cel in Sheets("Risico-analyse").Selection
 cel.entirerow.paste
next
Application.CutCopyMode = False

End Sub

Ik heb een aantal dingen veranderd aan je code:
Regel 8: In declareer de variabele "cel" als een range, dat is een (groep) cel(len).
Regel 14 en 16: Do voor iedere cel die in de selectie van "Risico-analyse" zit hetgeen wat op regel 15 staat.
Regel 15: Een cel heeft een "entirerow" eigenschap, die de hele rij waarin die cel zit teruggeeft als range. Tegen die range zeggen we "plak".

edit: algemene tip: door te beginnen met de macrorecorder en met die code te gaan stoeien kan je inderdaad een heel eind komen. Echter, de recorder doet botweg na wat jij ook deed en is dus soms wat inefficient. Je kan je code netter maken door regels als:

code:

Sheets("DATA en INVULSHEET").Select
ActiveSheet.Rows(18).Select
Selection.Copy

te vervangen door

code:

Sheets("DATA en INVULSHEET").Rows(18).Copy

Daardoor zeg je in je code "kopieer die-en-die regel" en niet meer "verplaats eerst de actieve cel naar die en die plek, selecteer dan vanalles en kopieer de selectie." Je code zal daardoor minder afhankelijk worden van de aanvangssituatie op het moment dat je de knop induwde en daardoor stabieler zijn. Deze macro is nog niet zo heel complex, maar het is nuttig zo snel mogelijk een degelijke programmeerstijl aan te leren, zodat je bij een volgend vba projectje niet uren aan het debuggen bent.

[ Voor 30% gewijzigd door onkl op 17-03-2009 13:52 ]

[dan0s]

Onkl, hartstikke bedankt hoor ik ga er meteen mee aan de slag!

Dat van die loop had ik nog niet gevonden, eens kijken of ik dat ook voor andere dingen kan gebruiken. Ik laat nog even weten wat het is geworden.

-- edit --

Runtime error '438'
Object doesn't support this property or method.

For Each cel In Sheets("DATA en INVULSHEET")
cel.EntireRow.Paste

Daar hangt hij dan op.

Heb alweer een tijd zitten zoeken maar ik kom er ff niet meer uit... Ik zal er morgen nog eens naar kijken maar ben er even klaar mee

google is even niet meer mijn vriend.

[ Voor 47% gewijzigd door dan0s op 17-03-2009 16:19 ]

[onkl]

Niet helemaal wakker, zie ik.
Selection is geen eigenschap van een worksheet.
While at it: eenrange.paste werkt meestal ook niet zo lekker.
ik denk dat

For Each cel In Selection
cel.EntireRow.PasteSpecial xlPasteFormats
Next

het beter doet.

[dan0s]

For Each cel In Selection
cel.EntireRow.PasteSpecial xlPasteFormats
Next

Dat was het idd Alleen werden daarbij de validations niet meegenomen. Heb dat opgelost en nu werkt het allemaal wel. De button selecteert nu de rijen van geselecteerde cellen en plakt daar netjes de layout van de de gekopieerde rij overheen. Het is dit geworden:

Dim cel As Range
Application.ScreenUpdating = False
Sheets("DATA en INVULSHEET").Visible = True
Sheets("DATA en INVULSHEET").Rows(20).Copy
Sheets("Risico-analyse").Select
For Each cel In Selection
cel.EntireRow.pastespecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next
Application.CutCopyMode = False
Sheets("DATA en INVULSHEET").Visible = False
Application.ScreenUpdating = True
End Sub

Vind het best leuk om dit te doen. Alleen ken ik te weinig gebruikte termen. Ik weet precies wat ik wil doen, hoe ik dat moet 'vertalen' naar code maar weet niet welke code geschikt is voor de doelen die ik zoek. Ik heb je LOOP advies nog gebruikt voor mn delete button:

Dim cel As Range
Application.ScreenUpdating = False
For Each cel In Selection
Selection.EntireRow.Delete Shift:=xlUp
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

Werkt allemaal prima! Enorm bedankt in ieder geval!


Nog een vraagje:
Kan ik hetzelfde "paste special" trucje ook uithalen voor insert? Deze VBA kopieert namelijk alle cellen in de geselecteerde rijen over dus als iemand zo dom is om een cel te selecteren waar in is geschreven of waardes ingevoerd dan pats boem weg.
--kleine edit-- : Soms moet ik erg lang wachten als ik zeg 100 cellen selecteer en de layout eroverheen knal. Is mijn code dus brak of trekt deze core2duo het niet?

[ Voor 4% gewijzigd door dan0s op 18-03-2009 12:38 ]

[onkl]

dan0s schreef op woensdag 18 maart 2009 @ 12:35:
[...]
Vind het best leuk om dit te doen. Alleen ken ik te weinig gebruikte termen. Ik weet precies wat ik wil doen, hoe ik dat moet 'vertalen' naar code maar weet niet welke code geschikt is voor de doelen die ik zoek. Ik heb je LOOP advies nog gebruikt voor mn delete button:

Als je meer wilt met VBA is het erg handig om er een serieus boek over te lezen. Het is ook wel te leren door telkens nieuwe projecten op te pakken en er op een gegeven moment achter te komen dat het je lukt, maar door er eens een boek over te lezen, zie je ook de structuur, het onderliggende idee, waardoor alles een stuk logischer wordt.

[...]


Werkt allemaal prima! Enorm bedankt in ieder geval!


Nog een vraagje:
Kan ik hetzelfde "paste special" trucje ook uithalen voor insert? Deze VBA kopieert namelijk alle cellen in de geselecteerde rijen over dus als iemand zo dom is om een cel te selecteren waar in is geschreven of waardes ingevoerd dan pats boem weg.

Dat zou moeten kunnen door niet paste, maar insert te gebruiken. Je moet dan wel even prutsen of de nieuwe rij boven, onder etc. t.o.v. de geselecteerde cel staat. Ik zou dan ook even gaan debuggen of dat goed gaat als je een aantal cellen selecteert, omdat de insert kan morrelen aan de selectie die je hebt.Dit gelt overigens ook voor de deletemethode die je liet zien. Test ook met niet-aangesloten ranges (Blok cellen selecteren, ctrl ingedrukt houden en nog een blok selecteren.)

Visual Basic:

Dim cel As Range
Application.ScreenUpdating = False
Sheets("DATA en INVULSHEET").Visible = True
Sheets("DATA en INVULSHEET").Rows(20).Copy
Sheets("Risico-analyse").Select
For Each cel In Selection
cel.EntireRow.Insert Shift:=xlDown
Next
Application.CutCopyMode = False
Sheets("DATA en INVULSHEET").Visible = False
Application.ScreenUpdating = True
End Sub

--kleine edit-- : Soms moet ik erg lang wachten als ik zeg 100 cellen selecteer en de layout eroverheen knal. Is mijn code dus brak of trekt deze core2duo het niet?

Vaak is driekwart van de processortijd niet nodig omdat de berekening nou zo moeilijk is, maar omdat Excel probeert tijdens de uitvoering het scherm te verversen. Zet bovenin je code:
Application.Screenupdating=False
en onderaan:
Application.screenupdating=true
Zal allicht schelen.

[dan0s]

Als je meer wilt met VBA is het erg handig om er een serieus boek over te lezen.

Ik heb gekeken in de Inholland database en ik heb een aantal boeken gereserveerd. Hopelijk kan ik deze dan binnenkort ophalen (in stagetijd uiteraard )

[...]

Ik zou dan ook even gaan debuggen of dat goed gaat als je een aantal cellen selecteert, omdat de insert kan morrelen aan de selectie die je hebt.Dit gelt overigens ook voor de deletemethode die je liet zien. Test ook met niet-aangesloten ranges (Blok cellen selecteren, ctrl ingedrukt houden en nog een blok selecteren.)

Strak plan. Heb zojuist de insert code gecheckt maar die werkt nog voor geen meter. Na 10 minuten had excel 65.536 nieuwe regels gepaste (wel netjes onder elkaar ) voordat de debugger begon te mekkeren. Van het hele 'debuggen' heb ik natuurlijk nog weinig kaas gegeten omdat ik er te weinig van af weet.


[...]

Application.screenupdating=true
Zal allicht schelen.

Tot nu toe zeer weinig Toch blij dat hier een duo in ligt zodat ik iig nog kan surfen tijdens het berekenen.


Laatste vraag:
Ik wil op de 4e sheet de gegevens (tekst&cijfers) van 3 kolommen laten verschijnen als iemand op sheet2 in kolom K een waarde van 1-200 ingeeft. Om het wat duidelijker te maken: zie screenshot

Dus: iemand vult in K25 een 1 in. Vervolgens worden K25, L25 (merged cells) en S25 geselecteerd (zitten dus nog andere cellen tussenin die niet mee moeten) en gekopieerd.

dan naar sheet 4: screenshot

Ik heb er over na zitten denken maar ben er niet uit wat ik nu precies moet doen. Mijn idee is een macro (met een button?) met een code iets van:
Zoek in sheet 2 kolom K
if cel value =>1 (groter dan)
dan
copy cells offset 0,0(cel zelf) EN 0,1(cel rechts) en 0,6(cel met Reductiewaarde)
ga naar sheet Risico-reductie
special paste selectie in kolomA vanaf rij16 (offset vanaf kolomK= -7, (cellen links))
bij volgende paste paste onder vorige input?


Zit ik zo enigzins in de goede richting of gaat dit helemaal de verkeerde kant op als ik hiermee aan de slag ga? Als dit namelijk mogelijk is wil ik proberen via http://www.vbaexpress.com hulp te krijgen. Ik heb gezien dat mensen daar zeer behulpzaam zijn (net als jij!) en graag meehelpen met code schrijven. Alleen zitten daar constant meer dan 50 users in het forum en hier niet

Alvast ontzettend bedankt voor je tijd maar weer!

[onkl]

dan0s schreef op woensdag 18 maart 2009 @ 16:04:
[...]


Ik heb gekeken in de Inholland database en ik heb een aantal boeken gereserveerd. Hopelijk kan ik deze dan binnenkort ophalen (in stagetijd uiteraard )

[...]

[...]


Strak plan. Heb zojuist de insert code gecheckt maar die werkt nog voor geen meter. Na 10 minuten had excel 65.536 nieuwe regels gepaste (wel netjes onder elkaar ) voordat de debugger begon te mekkeren. Van het hele 'debuggen' heb ik natuurlijk nog weinig kaas gegeten omdat ik er te weinig van af weet.

Debuggen is een absolute must bij dit soort dingen, dus lees je daar iig. even over in. Op deze pagina wordt het uitstekend uitgelegd. Zijn hele site is trouwens een aanrader.
In dit specifieke geval lijkt het erop dat de selectie wordt uitgebreid door de insert. Wat je zou kunnen proberen is je selection in een range variabele zetten
Dim myvar as range
set myvar = bladnaam.selection
en je loopje door myvar ipv selection laten lopen.
Denk er trouwens om dat screenupdating=false tijdens debuggen nogal slecht voor je humeur kan zijn, dus misschien moet je die er even tussenuit halen.

[...]

[...]


Tot nu toe zeer weinig Toch blij dat hier een duo in ligt zodat ik iig nog kan surfen tijdens het berekenen.


Laatste vraag:

Je kan dit oplossen zonder VBA en puur met formules, kijk even in [Excel] selectie regels uit sheet1 aaneengesloten in sheet2
Eigenlijk hoef je op de tweede sheet niets te doen en kan je het geheel in de 4e sheet afhandelen:
Als je in A1 in de 4e sheet een formule als "=IF(ISERROR(VERT.LOOKUP(ROW(A1);2esheet!K:K;1;FALSE);"";ROW(A!)) neerzet in in B1 =IF(A1="";"";VERT.LOOKUP(A1;2eSheet!K:L;2;false)) en die trek je naar beneden door, dan ben je er al.

(Nog een VBA tip: Eén van de eerste dingen die je moet leren is wanneer VBA nuttig is en wanneer het het spreekwoordelijke kanon voor de mug is )

[dan0s]

Bedankt voor alle tips en nuttige informatie, ik ga er morgen weer mee aan de slag!

Ik laat het uiteraard nog even weten als het allemaal gelukt is Alle buttons werken in ieder geval, nu nog die laatste autofill functie op sheet 4 en dan kan er gewerkt worden.

Jammer dat je geen sterren of kuddos aan users kan toedelen anders had je er van mij een berg gekregen.

[dan0s]

Bedankt Onkl voor de hulp. Het is me inmiddels gelukt om mijn wensen voor elkaar te krijgen met behulp van vlookup: =VLOOKUP(A16;'Risico-analyse'!K25:L44;2;FALSE)

Alles is nu naar wens

[ Voor 14% gewijzigd door dan0s op 23-03-2009 14:17 ]