vba code problem

shodan

Active Member
Joined
Jul 6, 2005
Messages
481
Hi there, this is the code I've written:

Code:
Sub mu_aan_cost_zero()
Dim mijnbereik As Range
Dim aantalrij, volgenderij As Integer

Worksheets(1).Activate

'copy first row
    Cells(1, 1).Copy
    Range(Cells(2, 1), Cells(12, 1)).Select
    Selection.Insert Shift:=xlDown

Set mijnbereik = Sheets(1).Range("A1").CurrentRegion
aantalrij = mijnbereik.Rows.Count

'copy next rows

For aantalrij = 1 To aantalrij 'moet hier komen
    
    volgenderij = (aantalrij * 12) + 1
    Cells(volgenderij, 1).Copy
    Range(Cells(volgenderij + 1, 1), Cells(volgenderij + 11, 1)).Select
    Selection.Insert Shift:=xlDown
 

Next aantalrij
End Sub
Now If I count the number of rows with "aantalrij" the result is 512 what is ok. And now I expected the macro to repeat 512 times the same procedure, but it only do 2 lines and than it leaves the procedure. What am I overlooking here?

Thanks
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
When you use
Code:
For aantalrij = 1 To aantalrij
What you are doing is resetting aantalrij to 1--you're losing your count of 512 entirely.

Just use a different variable, like

Code:
For i = 1 To aantalrij
     'code
Next i
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,937
You're changing the value of aantalrij. Try using two variables:

Code:
For new_variable= 1 To aantalrij 'moet hier komen 
    
    volgenderij = (new_variable* 12) + 1 
    Cells(volgenderij, 1).Copy 
    Range(Cells(volgenderij + 1, 1), Cells(volgenderij + 11, 1)).Select 
    Selection.Insert Shift:=xlDown 
  

Next new_variable
 

Forum statistics

Threads
1,078,013
Messages
5,337,725
Members
399,166
Latest member
jerome203

Some videos you may like

This Week's Hot Topics

Top