Als cel leeg cel dan: opvullen met cel erboven

[rzeeuw]

Goedenmiddag,

Ik zoek een hopelijk eenvoudige ALS functie in VBA met dit doel:

Ik heb in kolom B verschillende data staan die om de ongeveer 20 cellen (kan varieren) een dag verder gaan. Hier zitten echter ook enkele lege cellen tussen. Deze wil ik opvullen. Als er een lege cel is, dan moet deze gevuld worden met dezelfde datum als de cel erboven. vb:

23-06-2005
23-06-2005

24-06-2005
24-06-2005
24-06-2005
etc. etc.

De lege cel moet dan de waarde van de cel erboven krijgen, ofwel '23-06-2005' in dit geval.
Volgens mij best simpel alleen mij lukt het niet.

Alvast bedankt en met vriendelijke groet,

Roel



UPDATE: Ik heb het wondebaarlijk genoeg zelf voor elkaar gekregen.

 

[LucHeyndrickx]

Misschien kan je nog 's controleren of het even snel gaat als dit:

With Columns("A:A")
.Select
.SpecialCells(xlCellTypeBlanks). _
FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

Luc

 

[rzeeuw]

Dank, die versie werkte ook. Ik had zelf deze al gevonden:

Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Probleem die beide echter geven is dat ze inderdaad de lege cellen vullen, maar dit ook blijven doen. Oftewel ze vullen ook de cellen onder de tabel die leeg horen te zijn. Is er een manier om te zorgen dat alleen die cellen gevuld worden die zich bevinden in het bereik van de tabel?

Daarnaast heb ik nog een probleem. In de macro zit deze functie:

Cells(RijUit, 23) = tijd
uur = Hour(tijd)
Cells(RijUit, 24) = uur

Deze zorgt ervoor dat in kolom 23 de tijd wordt geplaatst en in kolom 24 alleen de uren die bij die tijden horen. Eenzelfde wilde ik bereiken met weekdagen (maandag, dinsdag etc) toevoegen in een kolom. Ik heb al de kolom datum en daarnaast wilde ik de weekdagen behorende bij die datum toevoegen. Als ik echter een vergelijkbare manier gebruik:

Cells(RijUit, 14) = datum
weekdag = TEXT((datum), ""dddd"")
Cells(RijUit, 15) = weekdag

wordt bij enteren het gedeelte dddd geselecteerd en krijg ik de foutmelding:

Compileerfout: Verwacht: lijstscheidingsteken of )

Wat doe ik fout?

 

[LucHeyndrickx]

Voor uw eerste gedeelte (Uit uw antwoord maak ik op dat je nog cellen in gebruik hebt, onder je tabel)

With Range("A1:A100")

en voor de rest mijn code.

Is vrijwel identiek als de jouwe, met dit verschil dat ik nog 's vaste waardes maak van de gevonden datums. In dit geval beter dan formules denk ik.

En uw tweede deel: Je zou op een andere manier de weekdag moeten bepalen. Misschien met de functie:

Application.Worksheetsfunction.Weekday .... enz.

Ik zou het zelf moeten zoeken, maar heb er nu geen tijd voor.

Luc

 

[rzeeuw]

Het bereik tot waar de functie moet worden uitgevoerd verschilt per keer. Ik kan dus niet een specifiek bereik invoeren. De functie moet zelf op een of andere manier tot het einde van de tabel werken en niet verder. Is er niet toevallig een manier om de lengte van de functie af te laten hangen van de lengte van een andere kolom in de tabel. Dat de functie hetzelfde aantal cellen doorloopt als kolom C lang is?

 

[LucHeyndrickx]

Verwijder alle lege rijen onder je tabel eens. Volgens mij zit jij met een veel te groot bestand te werken. Lege cellen worden bij jou door Excel als 'in gebruik' herkend.

Luc

 

[rzeeuw]

Dat klopte inderdaad. Ik heb nu bovenaan de macro verwerkt om alle data eerst te verwijderen, alvorens de rest aan te vangen. Voor de kolom met enkele lege velden gaat dat nu perfect. Ik heb alleen nog steeds het probleem met het toevoegen van de weekdagen. Ik heb het nu als volgt geprobeerd.

'Eerst de formule zo toevoegen in cel A2
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[1],""dddd"")"

Om deze vervolgens door te voeren omlaag. Hiervoor gebruik ik echter niet de juiste functie omdat hij nog steeds doorvoert naar de onderste cel van de sheet i.p.v. de onderste cel van de tabel.

Help

 

[Radagast]

De truuk is dat je het aantal cellen van de tabel gaat tellen, en dan een for-lus gaat doorlopen.

Hiervoor heb je een functie nodig die een aantal rijen telt en de uitkomst daarvan is je grootte van je tabel:

'we beginnen bij de eerste cel
Range("A1").Select
'nu springen we door naar de laatste cel
ActiveCell.SpecialCells(xlLastCell).Select

Dim rij As Integer
rij = ActiveCell.Row

'terug naar het begin
Range("A1").Select
For i = 0 To rij - 1
    If ActiveCell.Value = "" Then
        ActiveCell.Formula = ActiveCell.Offset(-1, 0).Value
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Next i

Natuurlijk is de kolom met A1 variabel en moet je deze zelf aanpassen of generaliseren.

 

[rzeeuw]

Ik blijf het probleem houden zoals Luc dit melde: Excel blijft lege cellen onder de tabel herkennen als zijnde in gebruik. De onderste cel selecteerd hij dan ook veel verder dan dat de eigenlijk laatste cel van de tabel is. Ook alle cellen onder de tabel verwijderen (al zijn ze leeg) verhelpt dit niet. Wie kan helpen.

Is er daarnaast een macro die de eerste lege cel onder een tabel kan selecteren. Hier moet dan een =MAX functie inkomen die als bereik alle cellen erboven heeft, behalve de eerste rij (zijn nl kolomkopjes). Als ik nu namelijk na '=MAX(', B2 invoer en dan met ....xlDown.. de laatste cel selecteer dan neemt de MAX functie ook zijn eigen cel mee in de functie en dus een fout door kringverwijzing.

BVD

 

[LucHeyndrickx]

Drastische middelen gebruiken:
Kopieer de tabel, en plak hem in een nieuw werkblad. (Selecteer niet meer dan de tabel). Dat werkt altijd.

Luc

 

[Radagast]

Het probleem is dat je niet van te voren kan weten wanneer de tabel afgelopen is. Mijn cod eheb ik geprobeerd op 15 rijen met her en der gaten en daar deed ie het.

Probeer het volgende. Ga onder ja tabel staan, en selecteer alle cellen onder je tabel, dus helemaal tot het einde van je werkblad en druk dan op delete. Dan zou je zeggen dat alles uit die cellen (misschien staat er wel een spatie in) verwijderd is.

 

[rzeeuw]

aan Luc Heyndrickx:

Aan zo'n drastische maatregel zat ik ook te denken. Probleem is dat ik dan de tabel moet kopieren voordat ik de subtotalen laat aanmaken in de macro en best wat waarden moet wijzigen in de macro dan. Had gehoopt op een andere oplossing, maar ben bang dat die er niet in zit dus.

via een omweg heb ik wel het resultaat wat ik wil. Ik laat nu gewoon eerst weekdagen toevoegen in kolom A waarbij hij dus doorloopt tot veel verder onder de tabel dan ik wil hebben. Daarna selecteer ik gewoon de laatste cel van de kolom ernaast (In die kolom krijg ik wel de juiste cel te pakken van de tabel) en selecteer ik alles links (xlToLeft) en onder (xlDown) en verwijder ik de teveel aan info weer... Een kleine omweg maar het resultaat werkt.

Bedankt wel voor het meedenken.

 

[rzeeuw]

aan Radagast:

Heb ik ook geprobeerd, zowel met gewoon delete als verwijderen en cellen naar boven verplaatsen. Beide geven echter nog steeds niet het juiste resultaat. Probleem zal waarschijnlijk zijn dat ik werk met een gefilterde tabel (met de optie kopieer gefilterde cellen) en de originele ongefilterde (dus langere) tabel in verborgen kolommen op hetzelfde werkblad staat. Ondanks dat ik die originele tabel compleet verwijder, blijft hij als laatste cel de laatste cel van die tabel pakken. (misschien omdat deze tabel eigenlijk weer een kopie is van een tabel op een andere sheet)

Vreemd is btw dat als ik de functie vul lege cellen met bovenste cel handmatig doe wel het juiste bereik wordt geselecteerd en alleen tot einde tabel de weekdagen worden toegevoegd. Maar als ik hetzelfde opneem met macro opnemen (waarbij hij tijdens opnemen wel de juiste cellen selecteerd) dan bij het opniew afspelen van die opgenomen macro weer niet het juiste bereik wordt geselecteerd.

Maar in ieder geval bedankt!