macro rij invoegen formule kopiëren

[jvbaal]

Ik heb een macro die er voor zorgt dat er een rij ingevoegd wordt en dat de formules op de nieuwe rij gekopieerd worden.
Maar als ik meerdere tabbladen selecteer om een rij in te voegen, dan gaat er iets niet goed. De gegevens van andere rijen veranderen en dat kan natuurlijk niet.

Hieronder staat de macro, ik selecteer altijd eerst een rij voordat ik de macro start. Dus de optie van het selecteren van een cel kan eruit, maar ik weet niet goed wat ik dan kan weglaten. ook zou het geen probleen zijn als er altijd maar een rij ingevoegd kan worden. Hierdoor kan er misschien nog meer verwijderd worden uit de macro. Ik denk maar altijd hoe minder regels hoe minder fouten er kunnen ontstaan.

Dit is de code:

Sub InsertRowsAndFillFormulas(Optional vRows As Long)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub


groetjes
Juul

 

[jvbaal]

Na enkele uren bezig geweest te zijn ben ik erachter gekomen, dat de macro fout loopt als er een formule is op een tabblad die verwijst naar een ander tabblad.

De rij die ingevoegd wordt krijgt de juiste formules, maar de volgende rij krijgt dezelfde formule met de zelfde celverwijzingen (dus loopt niet mee) en daardoor lopen al de formules in de volgende rijen een rij achter.

Als je verwijzingen hebt op het tabblad naar hetzelfde blad dan gaat alles goed.

Weet iemand raad of weet iemand hoe je van een gekopieerde rij de gegevens kunt verwijderen en de formules kunt laten staan.

groetjes
Juul

 

[Withaar]

Ik zie net dat je een reactie geplaats hebt, was ondertussen bezig je marco uit te pluizen.

Dit heb ik over gehouden;

Sub Macro4()
ActiveCell.EntireRow.Select
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook.Windows(1).SelectedSheets.Count)
i = 0
For Each sht In Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=1).Insert Shift:=xlUp
Selection.AutoFill Selection.Resize(rowsize:=2), xlFillDefault
On Error Resume Next
Selection.Offset(1).Resize(1).EntireRow.SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

N.b. je hoeft niet eens een rij te selecteren.
De macro start met het kijken naar de actieve cel en selecteerd die rij.
Sta je in rij 1 wordt deze gebruikt ect.

 

[Withaar]

Zie het probleem, gaat ook fout bij de uitgeklede versie.

Ik weet echter niet zo 1,2,3 een oplossing.

 

[jvbaal]

Withaar,
Hartelijk dank voor het herschrijven van de macro. Dit ziet er veel duidelijker uit.

Ik heb hem natuurlijk meteen getest. Maar helaas hetzelfde probleem. Alles gaat goed totdat een tabblad in de formule verwijst naar een ander tabblad.

De regel die toegevoegd wordt is goed maar de daaronderstaande lopen allemaal een regel achter in de formule. Er staat b.v. =beginvoorraad!A13 terwijl er moet staan =beginvoorraad!A14 enz.
Zogauw er een verwijzing komt naar hetzelfde blad, dan loopt de formule wel door.

groet
Juul

 

[jvbaal]

Ik heb het internet nog eens afgezocht en kwam op de volgende pagina

http://www.mvps.org/dmcritchie/excel/offset.htm

Nu denk ik dat misschien hier de oplossing te vinden is, maar daar ik met de nederlandse versie van Excel werk en omdat ik geen ster ben in het beheersen van de engelse taal, loop ik toch weer vast.

Weet iemand raad?

groetjes
Juul

 

[jzeedijk]

Hoi Juul,

Ik heb mij tot dus verre een beetje buiten de discussie gehouden. Maar als de formule gekopieerd wordt, kun je dan in de formule niet gewoon werken met $-tekens?

Jeroen

 

[jvbaal]

Jeroen,

bedankt voor je reactie,
Maar het is juist niet de bedoeling dat dat de formule absoluut wordt. Hij moet relatief blijven.

groetjes
Juul

 

[Withaar]

Nederlandse formule naam is ;=VERSCHUIVING(bron cel;rij;kolom)

Handigheidje; voeg bij tabladen het internationaal macroblad toe.
Zet hier je engelse formule in en copieër hem dan naar een Nederlands tabblad, de formule wordt automatisch vertaald door excel.

Mogelijk zou je een soort na bewerking kunnen doen na het invoegen waarbij de hele kolom (vanaf de actieve cel/rij) naar beneden 'recht' getrokken wordt bv door een functie als de vul greep, die zou je dan in de macro moeten opnemen en deze actie laten uitvoeren voor elk geselecteerde tabblad.

 

[jzeedijk]

Hoi Juul,

Ik denk dat ik hem heb:
A14: =indirect("beginvoorraad!A"&Rij()) ipv =beginvoorraad!A14

als formule gebruiken. Wordt er voor rij 14 een nieuwe regel ingevoerd verschuift de formule automatisch mee.

Jeroen

 

[jvbaal]

jzeedijk,

bedankt voor je reactie.
ik heb het getest, maar het gevolg is dat op iedere rij hetzelfde komt te staan.

ik heb ingevoerd in Z2 op het tabblad gekocht:
=indirect("beginvoorraad!H"&2))
als ik nu deze formule kopieer naar beneden dan krijg ik overal een verwijzing naar H2, terwijl juist de verwijzing relatief moet zijn dus H3, H4 enz

Weet je nog raad?

groet
Juul

 

[Sdekreek]

Hoi Juul,

In Jeroen zijn formule staat
=indirect("beginvoorraad!A"&Rij())

In je formule heb je 2 staan.

Succes!

Groet,
Stefan

 

[jvbaal]

sdekreek,
jzeedijk,

Je hebt gelijk, ik had het verkeerd gedaan.
Even getest en dat ging goed.
Nu zal ik al mijn formules eens aan gaan passen en kijken of het goed verloopt.

Ik laat weten vandaag of morgen hoe het verloopt.
In ieder geval HEEL HARTELIJK BEDANKT.

groet,
Juul

 

[jvbaal]

Nu loop ik tegen het volgende probleem

ik heb staan =som(piet:jan!Z3)

ook deze formule past zich niet goed aan met de macro, ook hier een verloop in de volgende regels.
Is hier ook een combinatie mogelijk met Indirect?


groet
Juul

 

[jzeedijk]

Hoi Juul,

Voor zover ik weet kan indirect niet werken met meerdere werkbladen. Wat natuurlijk wel kan is dat de somformule aangepast wordt tot:
=indirect("Piet!Z"&Rij())+....+indirect("Jan!Z"&Rij())

Jeroen

 

[jvbaal]

jeroen,

Is het zo dat je maar in 1 kolom een Indirect functie kunt gebruiken op een tabblad?

groet
Juul

 

[jvbaal]

Nog even terug komend op mijn vorige bericht.

Als ik in kolom B en rij 4 een functie heb met indirect en ik voer er ook weer een in in kolom E in rij 4, dan krijg ik in al de kolommen waar formules instaan #waarde! in rij 4. Ook waar formules staan zonder Indirect.

Dus, ik denk dat het niet mogelijk is om op 1 blad in dezelfde rij meer als 1 keer de functie Indirect te gebruiken.

Weet nog iemand raad of moet ik me erbij neerleggen dat Excel niet in staat is mijn probleem op te lossen.

Alsnog iedereen bedankt voor de medewerking.

Groet
Juul

 

[Withaar]

Voeg je altijd een rij toe op alle tabbladen tegelijk of ook 'willekeurig' op 'losse' tabbladen?

N.b. als het eerste het geval is kan de macro ook nog wel iets aangepast worden (dat dit automatisch gaat) en zou ik ook nog wel een poging willen wagen op te zoeken naar en oplossing.
Voeg je ook toe op 'losse' tabbladen dan wordt het allemaal wel erg lastig.
Mogelijk moet echter gewoon de werkwijze van de macro aangepast worden.

 

[jzeedijk]

Hoi Juul,

Indirect kun je zo vaak als je wilt toepassen in een rij of kolom. Voor Excel is het niet meer dan het omzetten van een tekst naar een celverwijzing. Dat mag het probleem niet zijn.

Kun je de formule die je wil kopieren even posten dan wordt het misschien duidelijk.

Jeroen

 

[jvbaal]

Withaar,

Bedankt voor je reactie.

Ja, ik voeg altijd een rij toe op alle bladen.

Ik heb zelf al zitten prutsen met de macro en er een stuk aan vast geplakt, waar ik de macro op al de bladen een voor een de formules gaat kopiëren. Maar het lastige is dat ook de eindtellingen in orde moeten blijven. Het aantal rijen kan nog al eens veranderen en daardoor weet ik nooit hoeveel cellen er van een kolom gekopieerd moeten worden.

Groet
Juul