verwijzen kopieren tot een bepaald, variabel bereik

[arjoderoon]

ik heb een erg uitgebreid bestand met data vanuit een aantal query's. Ik heb hier voor een vba script dat de data van 6 verschillende tabbladen op 1 tabblad plaats zodat ik met draaitabellen kan werken.

nu heb ik 1 schaduwblad met verwijzingen naar het blad met de samengevoegde data.
probleem waar ik tegen aanloop is dat ik nu de verwijzingen tot een x aantal regels heb ingezet, maar dat het ook voorkomt dat op het blad met samengevoegde data veel meer regels staan.

Ik zou eigenlijk graag willen dat ik met een script de verwijzingen in de regels door kopieer tot de laatste regel met data in het bestand waarin de data samen gevoegd is.

Zo uit mijn hoofd denk ik aan iets als het volgende:
-het script kijkt tot welk regel nummer het blad met samengevoegde data gevuld is.
-vervolgens kopieert het de formules over alle regels tot en met het laatste regel nummer door.

op deze manier wil ik ervoor zorgen dat ik nooit data mis.

Ik heb nu handmatig de regels met de daarin de verwijzingen tot en met regel 100.000 gekopieerd. Soms is dit ruim voldoende, maar ik zal al dat dit soms ook lang niet voldoende is, waardoor ik dus data mis en waardoor er dus foute conclusies getrokken worden.

Ik heb al een script dat op de 6 bladen met de data queries kijkt tot waar de data gevuld is, deze selecteert en kopieert naar het blad "samenvoeging". Dat gaat goed.

Ik zou het zelfde script nogmaals kunnen gebruiken om het laatste regel nummer op te halen tot waar op het blad "schaduwblad" de verwijzingen moeten komen. Alleen hoe ga ik er dat dan gebruiken in een script dat die verwijzingen ook daadwerkelijk kopieert?

Een aangezien het gaat om 200.000 regels over 27 kolommen betekent dat nogal wat verwijzingen, die het bestand er niet sneller op maken. Daarom zou ik de verwijzingen willen plakken, de corresponderende waarde die ermee opgehaald wordt, vervolgens slechts als waarde willen plakken en als ik het bestand de volgende keer weer refresh, dat dan de verwijzingen weer worden geplakt, en vervolgens weer als waarde geplakt worden.

(ik ontkom helaas niet aan een schaduwblad omdat ik een aantal voorwaardelijke parameters gecre�erd heb die nodig zijn voor de draaitabellen)

onderstaande is het script dat nu de data van de 6 bladen samenvoegd op 1 werkblad:

Sub dataplaatsen()

Application.ScreenUpdating = False
Sheetnames = Array("food-drug", "food-drug (2)", "aswatson", "aswatson (2)", "food", "food (2)")

For i = LBound(Sheetnames) To UBound(Sheetnames)
    With Sheets(Sheetnames(i))
        .Range(.Range("U2"), .Cells(.Rows.Count, 1).End(xlUp)).Copy Sheets("Samenvoeging").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
Next i

With Sheets("samenvoeging")
    .Range("a1", "u1").ClearContents
    Sheets("food-drug").Range("a1", "u1").Copy Sheets("samenvoeging").Range("A1", "u1")
       

End With

ThisWorkbook.RefreshAll

Application.ScreenUpdating = True

End Sub

Hoe kan ik een dergelijk iets doen?

 

[wampier]

Waarom de berekening niet gewoon in VBA doen en de juiste waarde direct plaatsen? Volgens mij kun je dan zelfs het schaduwblad overslaan.

 

[arjoderoon]

als het kan, graag maar ik zie zelf niet hoe ik dat zou kunnen doen.

de data komt op het blad samenvoeging in de kolommen A t/m U.
In de kolommen V t/m AA komen dan de formules voor het bepalen van de afhankelijke waardes (zo bepaalt 1 formule bijvoorbeeld of de waarde in een kolom positief is (>0) en geeft afhankelijk daarvan als uitkomst ja of nee.

De formules (of de uitkomsten van deze formules) moeten iedere keer weer in de kolommen V t/m AA geplaatst worden als het bestand bijgewerkt wordt.

Ik heb dat geprobeerd, maar kwam niet uit met het refreshen van de data. bij het refreshen wordt alle data op het blad vanaf regel 2 gewist en daarna wordt de data opnieuw geplaatst.

Daarna moeten de berekeningen in de kolommen V t/m AA gedaan/geplaatst worden.


ik had net zelf de formules om de range te bepalen die moet worden geplaatst of gewist al gebruikt en geprobeerd deze aan te passen, maar dan wordt volgens mij vervolgens niet het juiste bereik gebruikt om de formules te plakken (alleen regel 3 kreeg waarden)

laatsteregel = Sheets("samenvoeging").Cells.SpecialCells(xlCellTypeLastCell).Row
laatstekolom = Sheets("samenvoeging").Cells.SpecialCells(xlCellTypeLastCell).Column

ik zou de formules voor kolom v t/m AA op een apart blad kunnen zetten die daar vandaan gehaald worden?

ik had dit:

Sub copyverwijzingen()


laatsteregel = Sheets("samenvoeging").Cells.SpecialCells(xlCellTypeLastCell).Row
laatstekolom = Sheets("samenvoeging").Cells.SpecialCells(xlCellTypeLastCell).Column
laatsteregel2 = Sheets("schaduwblad").Cells.SpecialCells(xlCellTypeLastCell).Row
laatstekolom2 = Sheets("schaduwblad").Cells.SpecialCells(xlCellTypeLastCell).Column

adres = Sheets("samenvoeging").Cells(laatsteregel, laatstekolom).Address
adres2 = Sheets("schaduwblad").Cells(laatsteregel2, laatstekolom2).Address


Sheets("schaduwblad").Range("A2", adres2).ClearContents
Sheets("blad1").Range("A2", "AA2").Copy
Sheets("schaduwblad").Range("A2", adres).PasteSpecial (xlPasteAll)
Sheets("schaduwblad").Range("A2", adres2).PasteSpecial (xlPasteValues)



End Sub

maar dat werkt helaas niet.

Hoe zou ik het wel kunnen doen?

 

[wampier]

Ik denk dat je nog steeds vooral handmatige zaken automatiseert. Overigens kan dit wel, maar ik kan aan deze stukjes niet goed zien wat er fout gaat. Heb je eventueel een voorbeeldbestand zonder gevoelige informatie waar we wat op kunnen loslaten (hoeft uiteraard maar een paar regels te zijn)

Het direct toevoegen in kolom "V" van een waarde gebaseerd op een andere kolom is redelijk eenvoudig met VBA te doen overigens. Daarvoor hoeft niet eerst een formule gemaakt te worden maar kan direct in VBA:

if cel >0 then
   cel.offset(0,20).value = true
else
   cel.offset(0,20).value = false

Zolang de extra kolommen goed gedefinieerd zijn kan het zeer waarschijnlijk direct met VBA. Persoonlijk prefereer ik die methode, omdat je geen data gaat dupliceren over twee tabbladen die je eigenlijk al hebt.

 

[arjoderoon]

bedankt voor je reactie. het zou me niks verbazen dat ik handmatige zaken automatiseer. Dat komt mede doordat ik door te zoeken en te proberen wat vba leer, maar ik dus niet gedegen kennis heb van vba om het goed op te zetten (en dus ook zo efficient mogelijk te maken).
Jouw voorstel heeft ook mijn voorkeur zijn maar tot op heden heb ik dit zoals je ziet nog niet weten te maken. Daarom was het tussenblad een enigszins werkende optie.

Wat er fout gaat is dat ik met het bepalen van de laatste kolom, kolom AL terug krijg, terwijl dit AA zou moeten zijn.
Ik had al wat extra tussen stappen toegevoegd waarbij ik wel over het volledige bereik nu de verwijzingen gekopieerd krijg. Vervolgens dit bereik nogmaals selecteren en dan de betreffende verwijzingen omzetten naar waarden lukt ook, alleen heb ik dan nog steeds een dubbelblad.

de formule in kolom v bepaald of er de waarde ja of nee komt te staan en is gerelateerd aan dezelfde rij maar dan in kolom H

edit: hierbij een link naar een voorbeeld bestand in mijn dropbox:
https://www.dropbox.com/s/1ndxqnqsjkzuxv3/voorbeeld helpmij2.xlsm

in dit bestand zie je in blad 1 in de kolommmen V t/m AA de formules die dan op het blad samenvoeging moeten komen in dezelfde kolommen en in alle regels waar data staat..

 

[wampier]

Als je een voorbeeldje kan posten met wat random data en een duidelijke omschrijving wat de formules in de kolom moeten doen, kan ik wel eens kijken of ik iets in vba kan bouwen.

 

[arjoderoon]

ik heb net in mijn vorige post een link geplaatst.
de formule die in kolom AA komt kijkt of er een waarde in de cel in kolom E staat. Deze was eerst gerelateerd aan het schaduwblad, vandaar dat hij test op waarde = 0, maar nu zou hij moeten testen op waarde = leeg of niet

Bedankt voor je hulp alvast!

 

[wampier]

Zo iets werkt voor mij goed:

Sub toevoegen()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For Each cel In Range([a2], [a1000000].End(xlUp))
        For i = 0 To 3
            If cel.Offset(0, 7 + i * 4) > 0 Then
                cel.Offset(0, 21 + i) = "ja"
            Else
                cel.Offset(0, 21 + i) = "nee"
            End If
        Next i
        
        If cel.Offset(0, 3) = "" Then
            cel.Offset(0, 25) = "nee"
        Else
            cel.Offset(0, 25) = "ja"
        End If
        
        If cel.Offset(0, 4) = "" Then
            cel.Offset(0, 26) = "nee"
        Else
            cel.Offset(0, 26) = "ja"
        End If
    Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

uitgevoerd op tabblad "samenvoeging"

 

[arjoderoon]

super....

ik probeer te snappen wat je doet maar daar kom ik niet helemaal uit...

Kan je me dat ook uitleggen, dan leer ik er ook weer wat van...


in ieder geval super bedankt!

 

[wampier]

De macro loopt van [a2] tot [axxx] waarbij xxx de laatst gebruikte cel is (zolang er minder dan 1 miljoen lijnen zijn)

vervolgens wordt op offset 7(H),11(L),15(P) en 19(T) gekeken of de waarde groter is dan 0 en op offset 21(V),22(W),23(X) en 24(Y) weggeschreven

Dan volgen er nog twee checks op offset 3(D) en 4(E) of die tekst bevatten.

De offsets zijn dus tov van de cel in kolom A die op dat moment bekeken wordt (A5 + 3 kolommen offset = D5)

 

[arjoderoon]

thanks, ik snap hem nu.
ik zag bij range [a2] staan. ik vroeg me af of dat iets speciaals was omdat ik overal range("a2") zie.

nu is het bereik binnen een grens van 1 miljoen regels. in het hypothetische geval dat je er meer zou hebben pakt ie die niet meer mee..?

kan je dan ipv die a1000000 deze ook variabel maken dat je de a vast zet en de regelwaarde bepaalt middels een formule?

ik probeer vanalles uit om te kijken wat er gebeurt, maar bij de dingen die ik doe krijg ik een foutmelding.

kan het trouwens dat de code niet werkt met excel2003? ik krijg hier (op mijn eigen laptop) als ik de code uitvoer de melding:
compileerfout: kan het project of de bibliotheek niet vinden


ander punt: Als ik nu deze methode gebruik en het schaduwblad de deur uit doe moet ik nog 2 dingen doen:
1. ervoor zorgen dat alle kolomtitels goed op het blad samenvoeging gezet worden. De titels van de kolommen A t/m F zijn afkomstig uit de data queries, de titels van de kolommen G t/m AA moeten nog van een andere plek gehaald worden. Is dat ook in dit script te verwerken?
(de data moet ook iedere keer gewist worden als het vernieuwd wordt zodat er geen oude data meer tussen staat, kan dat met activeworksheet.clearcontents?)

2. in de werkelijke bestanden die gebruikt worden op kantoor zijn er talloze draaitabellen aan het blad "schaduwblad" gekoppeld. Die koppeling moet nu aan het blad "samenvoeging" komen. Is dat met een script te doen? Ik heb 24 draaitabellen per bestand, en iets van 20 bestanden waarvan de koppelingen aangepast moeten worden en om dat allemaal handmatig te doen zie ik niet zitten...

 

[wampier]

er kunnen maximaal 1.040.000 regels in een 32-bit versie, dus dat zal wel meevallen Overigens kan 2003 maar iets van 60000 regels dus dat gaat mijn vba ook niet leuk vinden.

je hebt in je code een plugin staan waarnaar je verwijst. Die heb ik zelf ook even moeten verwijderen om de code te runnen (xxx_range plugin oid). (extra -> verwijzingen ; dan uitvinken)

@1 Ja, je kunt eenvoudig alles wissen en de headers toevoegen met een script [g1].value = "header" etc.

@2 Je zou het sheet eenvoudigweg kunnen hernoemen . Maar ja, de koppelingen zijn met een script aan te passen. Of gewoon de hele sheet even kopieeren.

 

[arjoderoon]

mooi! ik ga alles eens proberen.

over eenvoudigweg hernoemen van een sheet: als ik vanuit excel een blad verwijder en dan het andere sheet die naam geef dan heeft excel geconstateerd dat de bron verwijderd is en lopen de draaitabellen in de soep omdat de verwijzingen niet blijven staan en daarmee automatisch naar het "nieuwe" blad overgenomen worden.

Als je dit middels vba doet, gebeurt dan niet hetzelfde?

(als ik in een cel in excel de verwijzing heb =blad2!A1 en ik verwijder blad2 veranderd mijn verwijzing in #verw!)

 

[wampier]

Bij interne referenties klopt dat, maar bij externe referenties worden die pas weer opgebouwd als die geopend worden (zolang de bron niet ook geopend is!).

Automatisch updaten van links uitzetten, externe files openen, bijwerken met VBA (of gewoon CTRL-H ->vervangen in dit geval)

 

[arjoderoon]

hoi... het betreft hier volgens mij ook interne referentie (binnen het bestand)...

1 ander ding. ik probeer het hier nu in te passen in mijn bestanden. De macro wordt niet uitgevoerd vanaf het blad "samenvoeging" maar vanaf een ander werkblad.
blijkbaar beinvloed dat het script want ik hebt het nu uitgevoerd vanaf het blad "schaduwblad" (het blad dat straks overbodig is geworden).

Maar de waarden in de kolommen v t/m aa komen op het blad schaduwblad terecht ipv op het blad "samenvoeging".

Ik had het script al aangepast naar:

Sub toevoegen()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With worksheets("samenvoeging")
    For Each cel In Range([a2], [a1000000].End(xlUp))
        For i = 0 To 3
            If cel.Offset(0, 7 + i * 4) > 0 Then
                cel.Offset(0, 21 + i) = "ja"
            Else
                cel.Offset(0, 21 + i) = "nee"
            End If
        Next i
        
        If cel.Offset(0, 3) = "" Then
            cel.Offset(0, 25) = "nee"
        Else
            cel.Offset(0, 25) = "ja"
        End If
        
        If cel.Offset(0, 4) = "" Then
            cel.Offset(0, 26) = "nee"
        Else
            cel.Offset(0, 26) = "ja"
        End If
    Next cel



End With

maar dat maakt geen verschil.

Moet ik hier nu eerst worksheet.activate doen?

+

hoe kan ik middels een script voor alle aanwezige draaitabellen in het bestand de bron aanpassen van "schaduwblad" naar blad "samenvoeging"?

 

[wampier]

Dat kan, of je moet je "with" gebruiken.

For Each cel In .Range([a2], [a1000000].End(xlUp))

Echter werkt dat verwarrend als je er weinig ervaring mee hebt en kun je inderdaad beter gewoon een sheet forceren

 

[arjoderoon]

ik had al "with" toegevoegd. Mogelijk niet goed...


With worksheets("samenvoeging")
For Each cel In Range([a2], [a1000000].End(xlUp))

daarom heb ik maar naar de "activate" noodgreep gegrepen...

ik ontdekte dat als ik mijn schaduwblad verwijder, dat het bestand dan op meerdere manieren in de soep loopt.
op de een of andere manier worden mijn draaitabellen toch vernaggeld, plus mijn sheet "samenvoeging" is dan verdwenen.
Nu is dat makkelijk aan te passen door in het script een extra worksheet met de naam samenvoeging in te voegen, maar toch, dan hou je het deleten van overbodige werkbladen.

daarom dacht ik: ipv het schaduwblad te deleten kan ik ook alles op dit blad laten uitvoeren en dan het blad samenvoeging deleten. daarmee blijven ook de verwijzingen voor de draaitabellen intact. dat heb ik nu gedaan en dat werkt goed.
Alleen zit ik nu nog met een ander punt en dat is dat in een ander topic pixcel mij adviseerde om het bronblad voor de draaitabellen om te zetten naar een tabel en de bron van de draaitabellen aan te passen naar deze tabel ipv het bereik A:AA

dit zou ik nog in het script willen inbouwen als het inderdaad nodig is.

in heb hiervoor nu het volgende gevonden / aangepast:

Sub maaktabel()
  
laatsteregel = Sheets("schaduwblad").Cells.SpecialCells(xlCellTypeLastCell).Row
laatstekolom = Sheets("schaduwblad").Cells.SpecialCells(xlCellTypeLastCell).Column


adres = Sheets("schaduwblad").Cells(laatsteregel, laatstekolom).Address

With Sheets("schaduwblad")
     'Maak tabel gewoon bereik
    .ListObjects("Tabel1").Unlist

    .ListObjects.Add(xlSrcRange, .Range("A1", adres), _
                                 , xlYes).Name = "Tabel1"
                                 
End With

End Sub

dit lijkt te werken. Moet ik nu nog de bron van mijn draaitabellen aan laten passen? Of heeft excel automatisch door dat het bereik waar naar verwezen wordt nu een tabel is?

 

[wampier]

Wat betreft de laatste vraag: dat is voor mij natuurlijk niet goed te beoordelen (bron aanpassen). Dat is een beetje afhankelijk van hoe de draaitabellen zijn opgezet (vast bereik, dynamisch bereik, etc.). Ik vermoed dat je de bron aan moet passen.

Als je goed kijkt naar mijn vorige post zie je dat er een punt (.) staat voor de "range". Een with statement doet an sich niets, totdat je die verkort aanroept door een punt voor het betreffende commando te zetten. In jouw code maak je een with omgeving aan, maar roept die niet aan (heel klein verschil in syntax, heel groot verschil in uitwerking)

 

[arjoderoon]

als ik nu dit heb als begin in het script:

With worksheets("schaduwblad")
    For Each cel In .Range([a2], [a1000000].End(xlUp))
        For i = 0 To 3

krijg ik een foutmelding.

hoe zou je het moeten opzetten als je 'with' wilt gebruiken?





krijg ik een foutmelding.

 

[wampier]

omdat de andere ranges ook in het bereik moeten

For Each cel In .Range(.[a2], .[a1000000].End(xlUp))

Daarom kun je beter gewoon switchen, dan is het een stuk minder ingewikkeld