• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Variabele in een Macro

Status
Niet open voor verdere reacties.
H

Humadgen

Gebruiker
Lid geworden
16 aug 2006
Berichten
251
Hallo wie weet een oplossing?

Ik heb een "Sheet1" met data. De kolommen zijn altijd hetzelfde.De hoeveelheid regels varieert iedere dag.

In "Sheet2" wil ik met formules dingen uitrekenen.

Eenvoudig voorbeeld:
Sheet1
-------Kolom A ---- Kolom B
1-----Aantal-------- Prijs
2-----2--------------- 4
3-----3--------------- 6
4-----8--------------- 5

[De ---- staan er alleen maar in om het beeld van een spreadsheet te geven.]

Sheet2
De formule die in "Sheet2" in cel A2 : =Sheet1!A2*Sheet1!B2

Aangezien de formule in Sheet2 naar beneden moet worden gekopieerd om alles uit te rekenen van Sheet1, moet ik dat nu handmatig doen met copy - paste. ( tot het gelijke aantal regels als er data is in Sheet1)
Maar ik wil dit in een Macro verwerken omgebruikers niet de handmatige actie te laten verrichten in Sheet2. (Sheet2 wil ik uiteindelijk verbergen voor de gebruikers waarvoor ik het programma maak.

Ik kan met een foefje de formules circa 20000 regels omlaag kopieren en daarna met een filter alle 0 waarden weer verwijderen, maar ik wil het liefste dat de gebruiker kan aangeven in een soort "communicatie met de macro" hoeveel regels er nodig zijn.

Het andere foefje heeft namelijk als nadeel dat het spreadsheet heel groot wordt bij opslaan omdat het herkent dat er 20000 regels "in kunnen staan".
Daarnaast lopik het gevaar dat indien er een keer meer dan 20000 regels met data in Sheet1 voorkomen dat niet alle getallen worden berekent.

Vraag: weet iemand hoe ik de copy functie naar het aantal regels interactief kan maken voor de gebruiker zonder dat ik (middels een werkbeschrijving) in Visual Basic de macro handmatig moet laten aanpassen.

Alvast bedankt
Humadgen
 
Laatst bewerkt:
Wellicht is dit wat?

Code: 
Sub kopieren()
With Sheets("Sheet2")
    .Range("A2").Copy .Range("A3").Resize(WorksheetFunction.Count(Sheets("Sheet1").Columns(1)))
End With
End Sub

Wigi
 
Wigi

Alles deed het totdat ik deze oplossing in het werkelijke spreadsheet geen plakken

in het werkelijke spreadsheet is de datasheet = Sheet1 45 kolommen breed en heet:
'Transaction Detail 2'

Sheet2 waarin de formules naar beneden moeten lopen heet:
'Transaction Detail'


de formules in A2 t/m E2 zijn
A2:
=IF('Transaction Detail 2'!O2="H014",MID('Transaction Detail 2'!U2,3,4)," ")

B2
=VLOOKUP(A2,Blad1!$A$2:$C$42,3,0)

C2
='Transaction Detail 2'!A2&"_"&B2&"_"&'Transaction Detail 2'!P2&"_"&'Transaction Detail 2'!Y2

D2
='Transaction Detail 2'!A2&"_"&'Transaction Detail 2'!O2&"_"&'Transaction Detail 2'!P2&"_"&'Transaction Detail 2'!Y2

E2
='Transaction Detail 2'!A2&"_"&'Transaction Detail 2'!P2

Kan het zijn dat er nog ergens een aanpassing moet komen in jou oplossing?
Toen ik langzaam je oplossing aanpaste in een test werkboek deed hij het wel, maar dan blijf ik in Sheet1 (='Transaction Detail 2' ) in de kolommen A t/m D met data werken.

Zie hieronder wat ik ervan gemaakt heb:

'
With Sheets("Investment Detail")
.Range("A2:E2").Copy .Range("A3").Resize(WorksheetFunction.Count(Sheets("SD Investment Detail 2").Columns(1)))
End With

'

Dit werkt wel in het test werkboek, maar niet in het uiteindelijke werkboek.

(Ik hoop dat je er een beetje uitkomt)
Alvast bedankt
Humadgen
 
Laatst bewerkt:
Ik denk dat de bladnamen niet kloppen.

Code: 
With Sheets("Transaction Detail")
.Range("A2:E2").Copy .Range("A3").Resize(WorksheetFunction.Count(Sheets("Transaction Detail 2").Columns(1)))
End With

Klopt het nu?

Wigi
 
Je hebt gelijk met de bladnamen, (Ik moet om 1:00 uur 's nachts ook gaan slapen in plaats van zulke dingen willen typen) maar ook de namen Investment Detail en Investment Detail 2 komen in het bestandje voor en daar zou het ook voor moeten lukken.

Het gekke is dat als ik in een testbestandje de query gebruik, dan werkt het wel.
Is er misschien nog een storende factor van meerdere werkbladen. (Kan het me haast niet voorstellen)


Kun je me de verschillende componenten uitleggen in de query.
With Sheets("Investment Detail")
.Range("A2:E2").Copy .Range("A3").Resize(WorksheetFunction.Count(Sheets("SD Investment Detail 2").Columns(1)))
End With

With Sheets("Investment Detail") = begin van de Query die zich gaat afspelen in sheet "Investment Detail"
Range("A2:E2").Copy = wat er gekopieerd wordt
Range("A3") = waar er geplakt moet worden

Maar het is me niet helemaal duidelijk wat je hier doet:
Resize(WorksheetFunction.Counts(Sheets("SD Investment Detail 2").

en wat de functie is van
Columns(1)))

Wat me namelijk opviel is: als ik in het Test bestandje de data range in "Investment Detail 2" naar rechts opschuif, dan moet ik de (1))) in Columns (1))) ook verhogen met het aantal kolommen dat ik invoeg.

Dus ik verwachtte dat er misschien een verband is tussen die (1))) en waar de eerste Celwaarde van een formule staat.

Bedankt
Humadgen
 
Laatst bewerkt:
Voeg het goede bestandje eens bij, enkele rijen is al genoeg. Ook geen kritieke info op het forum gooien aub.
 
Ik heb even wat data moeten aanpassen om te zorgen dat client gegevens niet meer herkenbaar waren, maar zie hieronder.
De query moet voor 5 sheets worden werken

Alvast bedankt
Humadgen
 

Bijlagen

  • Exposure Template 2007 (2).zip
    77,7 KB · Weergaven: 23
Voilà

Code: 
Sub kopieren()
Dim i As Integer
For i = 2 To 6
    Sheets(i).Range("A2:E2").Copy Sheets(i).Range("A3").Resize(WorksheetFunction.CountA(Sheets(i + 5).Columns(1)) - 2)
Next
End Sub

Ik ga er wel vanuit dat de volgorde van de tabbladen hetzelfde blijft.

Wigi
 
Kijk ook eens naar je borders code, deze kan een stukje korter:
Voorbeeld:
Code: 
With Selection.Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With Selection
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
        End With

Groet,
Ferenc
 
Wigi,

Heb niet de gehele vraag gevolgt maar mbv:
Code: 
Sheets(i).Range("A2:E2").Copy Sheets(i).Range("A3").Resize(WorksheetFunction.CountA(Sheets(i + 5).Columns(1)) - 2)

Kopieer je data van bv. sheet 1 en plaats je deze meerder keren in het zelfde sheet achter elkaar.
Of ben ik nu geheel abuis? Zou je mij dit uit kunnen leggen?

Bedankt,
Ferenc
 
Demeter zei:
Wigi,

Heb niet de gehele vraag gevolgt maar mbv:
Code: 
Sheets(i).Range("A2:E2").Copy Sheets(i).Range("A3").Resize(WorksheetFunction.CountA(Sheets(i + 5).Columns(1)) - 2)

Kopieer je data van bv. sheet 1 en plaats je deze meerder keren in het zelfde sheet achter elkaar.
Klik om te vergroten...

Ja, het kopiëren klopt, maar het begint wel op het 2 de tabblad van de file. (zie de variabele i)

Wigi
 
Wigi:

Deze 11 tab bladen kunnen op dezelfde plek ten opzichte van elkaar blijven staan.
In het uiteindelijke spreadsheet zitten nog 15 spreadsheets.

Afsluitende vraagjes over dit probleem:
1. Mag ik van de bovengenoemde 11 spreadsheets wel gebruik maken van de:
"Format-Sheet-Hide" functie? en
2. Moeten de andere 15 spreadsheets er voor of erna worden geplaatst.
(Ik vermoed erna)

Twee aanvullende vraagjes over de Query:
3. Wat is de functie van de A bij CountA?
en
4. Wat doet Columns(1))-2)?



Ferenc

Bedankt voor je aanvullende opmerking ik ga dat nog veranderen.
 
Demeter zei:
Kijk ook eens naar je borders code, deze kan een stukje korter:
Voorbeeld:
Code: 
With Selection.Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With Selection
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
        End With

Groet,
Ferenc
Klik om te vergroten...

En als ik mij niet vergis zette je dit ook neer voor vele tabbladen. Dat hoeft niet, gebruik een loop om door de tabbladen te gaan en dan eenmalig de code neer te zetten.
 
Humadgen zei:
Wigi:

Deze 11 tab bladen kunnen op dezelfde plek ten opzichte van elkaar blijven staan.
In het uiteindelijke spreadsheet zitten nog 15 spreadsheets.

Afsluitende vraagjes over dit probleem:
1. Mag ik van de bovengenoemde 11 spreadsheets wel gebruik maken van de:
"Format-Sheet-Hide" functie? en
2. Moeten de andere 15 spreadsheets er voor of erna worden geplaatst.
(Ik vermoed erna)

Twee aanvullende vraagjes over de Query:
3. Wat is de functie van de A bij CountA?
en
4. Wat doet Columns(1))-2)?
Klik om te vergroten...

1. Ja dat mag.
2. Erna. Ervoor kan ook, maar laat i dan gaan van 17 tot 21.
3. Het telt alle niet-lege cellen in een bereik. Count (zoals ik voorheen gebruikte) telt enkel cellen met getallen erin. Tekst kan nu dus ook.
4. Columns(1): daaruit worden de niet-lege cellen geteld. (Kolom A dus)
De -2 heeft te maken met hoofding (die ook meegeteld wordt in CountA), en ook omdat je begint op A3.

Nu ok?

Wigi
 
Wigi,

Hartstikke duidelijk.
Bedankt.

Je opmerking over de loop die ik kan gebruiken voor de Borders en de invulling van de kleuren werkt niet via een loop. (althans ik weet niet hoe dat zou moeten)

Ik heb namelijk in 1 tabblad alle macro's buttons staan (= noem het een soort menu of stuur pagina) en verschillende Macro's moeten na elkaar worden gebruikt.

Voor de macro die gebruikt moet worden laat ik de cellen erachter Geel oplichten en van de Macro die al gebruikt is wordt de achtergrond weer neutraal.

Op die manier "dwing" ik een gebruiker de juiste stappen achter elkaarte nemen.
"Monkey Proof"

Dus het verkleuren van de cellen verspringt steeds op het zelfde blad.
Grtnx
Humadgen

Ps. mocht dat wel met een loop kunnen..... Ik sta overal voor open.
 
Post eens de versie welke je tot nu toe hebt gemaakt? natuurlijk zonder prive gegevens.
Kijken we met zijn allen (?) naar de loop.

Groet,
Ferenc
 
Ferenc / Wigi

Ik zal als ik e.e.a. in elkaar heb gezet de laatste versie plaatsen. Zal waarschijnljik in de loop van de week worden.

Ik worstel nog wel met een "loop" die volgens mij eenvoudig te maken is, maar waar ik de kennis nog niet van heb hoe dat precies werkt.

Ik schoon nu de overbodige data heel simplistisch op deze manier, maar dat moet volgens mij ook met een loop kunnen.
' Delete formulas from 5 spreadsheets
Sheets("Investment Detail").Select
Range("A3:E60000").Select
Selection.ClearContents
Range("A2").Select

Sheets("Foreign Exchange Contracts - Pe").Select
Range("A3:E60000").Select
Selection.ClearContents
Range("A2").Select

Sheets("Transaction Detail").Select
Range("A3:E60000").Select
Selection.ClearContents
Range("A2").Select

Sheets("Transaction YTD").Select
Range("A3:E60000").Select
Selection.ClearContents
Range("A2").Select

Sheets("Foreign Exchange Contracts").Select
Range("A3:E60000").Select
Selection.ClearContents
Range("A2").Select


Tot zover jullie beiden al heel erg bedankt.
Grtnx
Humadgen
 
Ten eerste gebruik de code tags aub.

Maak je code overzichtelijker, zodat je beter zicht hebt op wat er nu werkelijk moet gaan gebeuren!!!!
Code: 
Sheets("Investment Detail").Range("A3:E60000").ClearContents

Sheets("Foreign Exchange Contracts - Pe").Range("A3:E60000").ClearContents

Sheets("Transaction Detail").Range("A3:E60000").ClearContents

Sheets("Transaction YTD").Range("A3:E60000")..ClearContents

Sheets("Foreign Exchange Contracts").Range("A3:E60000").ClearContents

Range("A2").Select

Zo te zien gaan we in ieder van de vijf tabbladen het bereik A3:E60000 leegmaken.
denk dat E60000 wel beter kan met een dynamisch bereik (iets van laatste gevulde regel).

Probeer deze gegevens eens in een loop te zetten zoals Wigi heeft aangegeven een eerder post.
Sub schoon_tabbladen_op()
Dim i As Integer
For i = 2 To 6
Sheets(i).Range("A3:E60000").ClearContents
Next
End Sub
Klik om te vergroten...
Dan moeten natuurlijk wel deze tabbladen overeenkomen met de gegeven nummers in de For loop.

Succes ermee.


Groet,
Ferenc
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan