Next Whithout For ???

K

karltlbm

New Member
Joined
Jun 3, 2015
Messages
10
Hello, my code is :
Code: 
Dim j As Integer
        For j = 1 To 20
        
            If TypeName(List_SSP(j).nom_ssp) <> Empty Then
        
                If List_SSP(j).nom_ssp = List_Projet(i).ssp_P.nom_ssp Then
            
                    Sheets("originales").Select
                    Range("F2:G8").Select
                    Selection.Copy
                    Sheets("Sheet6").Select
                    ActiveSheet.Paste
                    
                    ActiveCell.Offset(0, 1).Value = List_SSP(j).nom_ssp
                    ActiveCell.Offset(1, 1).Value = List_SSP(j).statut_ssp
                    ActiveCell.Offset(2, 1).Value = List_SSP(j).deadline_ssp
                    ActiveCell.Offset(3, 1).Value = List_SSP(j).date_debut_ssp
                    ActiveCell.Offset(4, 1).Value = List_SSP(j).date_fin_ssp
                    ActiveCell.Offset(5, 1).Value = List_SSP(j).cout_previsionnel_ssp
                    ActiveCell.Offset(6, 1).Value = List_SSP(j).cout_reel_ssp
                    Range(ActiveCell, ActiveCell.Offset(6, 1)).Interior.ColorIndex = List_Projet(j).couleur_P
                    
                    Next
                
                ElseIf List_SSP(j).nom_ssp <> List_Projet(i).ssp_P.nom_ssp Then
                
                    Next
                
                End If

I get the error : next without for. I don't understand why, can someone help ?

Thank you
 
Last edited by a moderator:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
You have 2 IF's, but only 1 End If
You have 1 For, but 2 Next

You don't need to say Next in the IF.
It's going to do that once the IF is done.

and you don't need to do the ElseIf to test if the first IF is false.

Try
Code: 
Dim j As Integer
For j = 1 To 20
    
    If TypeName(List_SSP(j).nom_ssp) <> Empty Then
    
        If List_SSP(j).nom_ssp = List_Projet(i).ssp_P.nom_ssp Then
        
            Sheets("originales").Select
            Range("F2:G8").Select
            Selection.Copy
            Sheets("Sheet6").Select
            ActiveSheet.Paste
            
            ActiveCell.Offset(0, 1).Value = List_SSP(j).nom_ssp
            ActiveCell.Offset(1, 1).Value = List_SSP(j).statut_ssp
            ActiveCell.Offset(2, 1).Value = List_SSP(j).deadline_ssp
            ActiveCell.Offset(3, 1).Value = List_SSP(j).date_debut_ssp
            ActiveCell.Offset(4, 1).Value = List_SSP(j).date_fin_ssp
            ActiveCell.Offset(5, 1).Value = List_SSP(j).cout_previsionnel_ssp
            ActiveCell.Offset(6, 1).Value = List_SSP(j).cout_reel_ssp
            Range(ActiveCell, ActiveCell.Offset(6, 1)).Interior.ColorIndex = List_Projet(j).couleur_P
            
        End If
    End If
Next j
 
Upvote 0
You don't need the elseif for what I can see here. If you indent your code its easier to spot these errors. Try this:

Code: 
Dim j As Integer
For j = 1 To 20
    If TypeName(List_SSP(j).nom_ssp) <> Empty Then
        If List_SSP(j).nom_ssp = List_Projet(i).ssp_P.nom_ssp Then
            Sheets("originales").Select
            Range("F2:G8").Select
            Selection.Copy
            Sheets("Sheet6").Select
            ActiveSheet.Paste
            ActiveCell.Offset(0, 1).Value = List_SSP(j).nom_ssp
            ActiveCell.Offset(1, 1).Value = List_SSP(j).statut_ssp
            ActiveCell.Offset(2, 1).Value = List_SSP(j).deadline_ssp
            ActiveCell.Offset(3, 1).Value = List_SSP(j).date_debut_ssp
            ActiveCell.Offset(4, 1).Value = List_SSP(j).date_fin_ssp
            ActiveCell.Offset(5, 1).Value = List_SSP(j).cout_previsionnel_ssp
            ActiveCell.Offset(6, 1).Value = List_SSP(j).cout_reel_ssp
            Range(ActiveCell, ActiveCell.Offset(6, 1)).Interior.ColorIndex = List_Projet(j).couleur_P
        End If
    End If
Next
 
Upvote 0
Thank you guys, i didn't know that i could only use only one next. I do indent my code but it doesn't show it when i paste it on this forum. So now it is working but it doesn't actually do what i want. Actually, my entire code is :
Code: 
Dim i As Integer
For i = 1 To 20


    If TypeName(List_Projet(i).nom_P) <> Empty Then
    
        Sheets("originales").Select
        Range("A2:D8").Select
        Selection.Copy
        Sheets("Sheet6").Select


        'On colle une plage projet vide
        ActiveSheet.Paste


        'On remplit avec les données
        ActiveCell.Offset(0, 1).Value = List_Projet(i).id_P
        ActiveCell.Offset(0, 2).Value = List_Projet(i).nom_P
        ActiveCell.Offset(1, 2).Value = List_Projet(i).statut_P
        ActiveCell.Offset(2, 2).Value = List_Projet(i).deadline_P
        ActiveCell.Offset(3, 2).Value = List_Projet(i).date_debut_P
        ActiveCell.Offset(4, 2).Value = List_Projet(i).date_fin_P
        ActiveCell.Offset(5, 2).Value = List_Projet(i).budget_P
        
        Range(ActiveCell, ActiveCell.Offset(6, 3)).Interior.ColorIndex = List_Projet(i).couleur_P
        ActiveCell.Offset(0, 4).Activate
        
        Dim j As Integer
        For j = 1 To 20
    
            If TypeName(List_SSP(j).nom_ssp) <> Empty Then
             
                If List_SSP(j).nom_ssp = List_Projet(i).ssp_P.nom_ssp Then
                    
                    Sheets("originales").Select
                    Range("F2:G8").Select
                    Selection.Copy
                    Sheets("Sheet6").Select
                    ActiveSheet.Paste
                    
                    ActiveCell.Offset(0, 1).Value = List_SSP(j).nom_ssp
                    ActiveCell.Offset(1, 1).Value = List_SSP(j).statut_ssp
                    ActiveCell.Offset(2, 1).Value = List_SSP(j).deadline_ssp
                    ActiveCell.Offset(3, 1).Value = List_SSP(j).date_debut_ssp
                    ActiveCell.Offset(4, 1).Value = List_SSP(j).date_fin_ssp
                    ActiveCell.Offset(5, 1).Value = List_SSP(j).cout_previsionnel_ssp
                    ActiveCell.Offset(6, 1).Value = List_SSP(j).cout_reel_ssp
                    Range(ActiveCell, ActiveCell.Offset(6, 1)).Interior.ColorIndex = List_Projet(j).couleur_P
                    
                End If
                
            End If
        Next j
        
    ElseIf TypeName(List_Projet(i).nom_P) = Empty Then
        
        MsgBox ("il n'y a pas de projet")
        
    End If


Next i
I have many problems, one is : even if TypeName(List_Projet(i).nom_P) is Empty, the elseif doesn't work and the msgbox does not pop.
 
Last edited by a moderator:
Upvote 0
karltlbm said:
Thank you guys, i didn't know that i could only use only one next. I do indent my code but it doesn't show it when i paste it on this forum. So now it is working but it doesn't actually do what i want. Actually, my entire code is :



I have many problems, one is : even if TypeName(List_Projet(i).nom_P) is Empty, the elseif doesn't work and the msgbox does not pop.
Click to expand...


. I Know you are just here the once, but try to read up a bit on the Rules, FAX, Posting suggestions etc... some are dead easy .. like code tags.-- you simply highlight your code and then hit the# icon above, then your code looks like this

Code: 
Dim i As Integer
For i = 1 To 20


If TypeName(List_Projet(i).nom_P) <> Empty Then

Sheets("originales").Select
Range("A2:D8").Select
Selection.Copy
Sheets("Sheet6").Select


'On colle une plage projet vide
ActiveSheet.Paste


'On remplit avec les données
ActiveCell.Offset(0, 1).Value = List_Projet(i).id_P
ActiveCell.Offset(0, 2).Value = List_Projet(i).nom_P
ActiveCell.Offset(1, 2).Value = List_Projet(i).statut_P
ActiveCell.Offset(2, 2).Value = List_Projet(i).deadline_P
ActiveCell.Offset(3, 2).Value = List_Projet(i).date_debut_P
ActiveCell.Offset(4, 2).Value = List_Projet(i).date_fin_P
ActiveCell.Offset(5, 2).Value = List_Projet(i).budget_P

Range(ActiveCell, ActiveCell.Offset(6, 3)).Interior.ColorIndex = List_Projet(i).couleur_P
ActiveCell.Offset(0, 4).Activate

Dim j As Integer
For j = 1 To 20

If TypeName(List_SSP(j).nom_ssp) <> Empty Then

If List_SSP(j).nom_ssp = List_Projet(i).ssp_P.nom_ssp Then

Sheets("originales").Select
Range("F2:G8").Select
Selection.Copy
Sheets("Sheet6").Select
ActiveSheet.Paste

ActiveCell.Offset(0, 1).Value = List_SSP(j).nom_ssp
ActiveCell.Offset(1, 1).Value = List_SSP(j).statut_ssp
ActiveCell.Offset(2, 1).Value = List_SSP(j).deadline_ssp
ActiveCell.Offset(3, 1).Value = List_SSP(j).date_debut_ssp
ActiveCell.Offset(4, 1).Value = List_SSP(j).date_fin_ssp
ActiveCell.Offset(5, 1).Value = List_SSP(j).cout_previsionnel_ssp
ActiveCell.Offset(6, 1).Value = List_SSP(j).cout_reel_ssp
Range(ActiveCell, ActiveCell.Offset(6, 1)).Interior.ColorIndex = List_Projet(j).couleur_P

End If

End If
Next j

ElseIf TypeName(List_Projet(i).nom_P) = Empty Then

MsgBox ("il n'y a pas de projet")

End If


Next i



Alan

P.s.
. Check out my signature below for some of that info..
 
Last edited:
Upvote 0
Why do you think TypeName would be Empty? (it returns a String)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
369
ststern45
S
B
  • Question Question
Issue with Excel VBA using multiple For and Next
Replies
1
Views
737
Micron
M
C
  • Question Question
Help Converting excel formula to vba loop
Replies
5
Views
360
Natas
N
O
  • Question Question
MS Excel VBA Code to Filter on Date Equal to or Next Future Date
Replies
0
Views
907
OilEconomist
O
T
  • Question Question
Insert/Delete Columns issue via VBA
Replies
1
Views
835
Tdorman
T

Forum statistics

Threads
1,224,804
Messages
6,181,060
Members
453,016
Latest member
cherryfalling

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top