Excel Vba routine

Beste alle,

ik heb onderstaande routine in vba en dat werkt goed maar nu wil ik dat een deel van het bereik variabel wordt.
Range("C45").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-40]C:R[-2]C,dienst1)" Het rode moet variabel worden . het variable wordt door een andere macro bepaald.

ik dacht dit Range("C45").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-40]C:R[-2-Aantal_Zonder]C,dienst1)"

aantal_zonder is steeds anders, wordt door een macro bepaald.
de macro loopt vast en ik zie niet wat ik verkeerd doe.

graag jullie advies......
thanks

Ronald

Plaats even het document of de volledige code.

Select is overbodig.

Private Sub hoeveel_mensen_buiten_telling(control As IRibbonControl)
Jaar1 = Sheets("Januari").Range("C4").Value


Aantal_zonder = InputBox("Hoeveel werknemers wilt u niet mee laten tellen?, werknemers vult u in in de het rooster in de licht grijze balk", "Hoeveel buiten telling")
If Aantal_zonder = "" Then End
If Aantal_zonder = 0 Then End

'If ActiveSheet.Name = "Januari" Then Application.Run "onderste_Rij_KLeuren_31"
'If ActiveSheet.Name = "Februari" Then GoTo test_februari
'If ActiveSheet.Name = "Maart" Then Application.Run "onderste_Rij_KLeuren_31"
'enz voor alle maanden, de routine Application.Run "onderste_Rij_KLeuren_31" loopt goed.

Application.Run "Diensten_invullen_31"

end sub

Private Sub Diensten_invullen_31()

Columns("B:B").Select
Selection.ColumnWidth = 19
Columns("C:AH").Select
Selection.ColumnWidth = 4
Rows("35:50").Select
Selection.RowHeight = 15
Columns("AH:AU").Select
Selection.ColumnWidth = 8

range("B45").Select
ActiveCell.FormulaR1C1 = "=dienst1"
Range("C45").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-40]C:R[-2+Aantal_zonder]C,dienst1)"
end sub


hoop dat dit duidelijker is. het eerste gedeelte van de routine ( eigenlijk een aparte routine zorgt ervoor dat een aantal regels in het rooster een lichtgrijze kleur krijgen door de macro "onderste_Rij_KLeuren_31" op te roepen. dit is afhankelijk van de maand, 28, 29, 30 of 31 dagen )
Het tweede deel (groen), zorgt ervoor dat die diensten die in het rooster worden gezet niet meetellen in de dag telling van de bezetting.

De diensten die je ziet. "dienst1 bv zijn een verwijzing naar een ander tabblad waarin de codering van de dienst wordt ingevuld. bv B voor dagdienst. die diensten moeten als er een aantal regels worden gekozen buiten te telling niet meetellen.
alvast bedankt.

Aantal_zonder wordt bepaald in "hoeveel_mensen_buiten_telling" en is dus niet gevuld in "Diensten_invullen_31". Als je de waarde wil laten overgaan van de ene routine naar de andere dan zul je gebruik moeten maken van een parameter waarin je de waarde meegeeft.

Je doet dan de volgende aanroep:
Application.Run "Diensten_invullen_31(Aantal_zonder)"

En je wijzigt de routine zelf als volgt:
Private Sub Diensten_invullen_31(ByVal Aantal_zonder_overnemen As Integer)
En de formule:
"=COUNTIF(R[-40]C:R[-2+Aantal_zonder_overnemen]C,dienst1)"

Aandachtspunten:
Ik ken de aanroep "Application.Run" niet. Ik gebruik altijd "Call". Ik weet niet of hier verschil tussen zit. Als et niet werkt zou ik eerst de aanroep wijzigen
Je gebruikt iedere keer select. Dit is niet nodig en werkt vertragend. Gebruik bv: Columns("B:B").ColumnWidth = 19

Gebruik svp code tags als je in dit forum VBA code plaatst.

Peter,

dank voor je reactie en opmerkingen.

de Aantal_zonder had ik. maar dat heb ik niet vermeld eerder onder public beschikbaar gemaakt.
Als ik binnen de routine msgbox Aantal_zonder gebruik geeft hij keurig aan wat de waarde van Aantal_zonder is.

bij mijn versie van excel vba snapt hij Call niet. Office 2010 is dat en Nederlands. Mogelijk dat dat ermee te maken heeft.
Als ik mijn versie aanpas zoals je zegt loopt hij ook vast.


hij loopt vast op "=COUNTIF(R[-40]C:R[-2+Aantal_zonder_overnemen]C,dienst1)"

Ronald,

Plaats je bestand eens, dat werkt over het algemeen beter.
Ik ga het in ieder geval niet nabouwen; ook geen gissen.

bij deze het bestand.
als je onder menu Roosterprogramma - rooster kijkt en nieuw jaar maken kiest dan loopt hij vast en zie je in de vba wat er mis is

alvast bedankt.

ronald

Bij een soortgelijk probleem werkt dit bij mij:

@Peter: Gebruik geen Application.run, beter is:

Ik heb 2 macros samengevoegd
Private Sub Jaar_Maken(control As IRibbonControl)
en
Private Sub Nieuw_Jaar_Maken()



- vermijd 'select'
- vermijd 'activate'
- vermijd goto statement in VBA- code
- vermijd application.run
- vermijd overbodige messageboxen
- besteed liever eerst tijd aan effektieve VBA code dan aan gadgets in het UserInterface.

. waarom niet automatisch een kopie opslaan ? daar kan een gebruiker weinig op tegen hebben.
. waarom geen dropdown in sheet januari om het jaar te kiezen ?

dank Haije, probleem is opgelost met deze oplossing.

dank SNB en jkpieterse
een copy opslaan is zeker wel handig en wilde ik ook al doen. maar vond ik nu nog niet zo belangrijk.
dank voor de tips om de routine sneller te maken.
Veel van de routines zijn gemaakt door record en daar is nog zeker wat aan te verbeteren.
De application.run gebruik ik meer om duidelijker te vinden waar een nieuwe routine wordt gestart maar dat ga ik ook als het klaar is weghalen.

als ik activate verwijder wordt dan nog wel je juiste sheet en cel gebruikt?

alvast bedankt.

groet,

Ronald

als ik activate verwijder wordt dan nog wel je juiste sheet en cel gebruikt?

Klik om te vergroten...

In VBA wel

snb: Waarom schrijf je 276 in het tweede argument van die messagebox? Dit is toch duidelijker:
Overigens zou ik gaan voor het vbQuestion icoontje in plaats vbCritical. Het gaat om een vraag, niet om een ernstige foutmelding toch?