Issue with : "Next without For"

[Nyanko]

Hi !

I've tried taking the following code apart several times and building in stages, however I'm still getting foxed by "Next without For" errors and the Next -For loop not working the way I want !!

I have this working using lots of select cases and gotos, but I wanted to try and do it 'properly'. I think I'm being hampered by my own inexperience !!!

The premise is that if a cell on the Absence Report sheet is missing a value then the macro should note the employee number and the date, then look to the Sick_AltDuty sheet to match the information and find the missing value.

For o = 1 To LastRow
    Sheets("Absence Report").Select
    If Cells(rowcount, 4) = "" Then 'if this cell is empty then
        If Cells(rowcount, 3) = "" Then 'if date cell is empty then end of this section
        MsgBox ("end")
        Exit For
        Else
        EmpNo = Cells(rowcount, 1) 'if date cell is not empty then declare employee number ...
        AbDate = Cells(rowcount, 3) '...and date of absence
        rowcount = rowcount + 1 'add one to the row counter
        End If
        Sheets("Sick_AltDuty").Select
        Range("B3").Select
            For p = 1 To LastRow2 'we need to find the employee number and date that matches the earlier value
            OldEmpNo = Cells(rowcount2, 2)
            If OldEmpNo = EmpNo Then 'if the current cell = the emp no ...
            OldAbDate = Cells(rowcount2, 1)
                If OldAbDate = AbDate Then AbReason = Cells(rowcount2, 9) 'if the current cell = the absence date ...
                GoTo AddReason 'goto addreason
                If OldAbDate = 0 Then AbReason = "Date Not Found" 'if no date then enter text
                GoTo AddReason 'goto addreason
                Else
                rowcount2 = rowcount2 + 1 'otherwise this date doesn't match, add one to the counter...
                End If
                Next p '...next p to find the right emp and date
            If OldEmpNo = "" Then 'but if the emp can't be found then enter text
            AbReason = "Employee Not Found"
            GoTo AddReason 'goto addreason
            Else
            rowcount2 = rowcount2 + 1 'otherwise this employee doesn't match
            Next p '... next p to find the right emp and date
            End If
    Else
    If Cells(rowcount, 4) <> "" Then rowcount = rowcount + 1 'if the cell isn't blank then add to row count
    End If
Next o

If needed I have a test file that I can upload tonight ...

Any suggestions would be welcome, I'm guessing it's to do with the many IFs, however I can't get the phrasing correct.

 

[Lewiy]

I have indented your code to make it easier to read and debug and have added comments where I think you may still need some work. This is just done on a first glance on the basis that I don’t have the rest of your code or the worksheet data.

For o = 1 To LastRow
    Sheets("Absence Report").Select
    If Cells(RowCount, 4) = "" Then
        If Cells(RowCount, 3) = "" Then
            MsgBox ("end")
            Exit For
            Else
                EmpNo = Cells(RowCount, 1)
                AbDate = Cells(RowCount, 3)
                RowCount = RowCount + 1
        End If
        Sheets("Sick_AltDuty").Select
        Range("B3").Select
        For p = 1 To LastRow2
            OldEmpNo = Cells(rowcount2, 2)
            If OldEmpNo = EmpNo Then
                OldAbDate = Cells(rowcount2, 1)
                If OldAbDate = AbDate Then AbReason = Cells(rowcount2, 9)
                GoTo AddReason 'You still have this GoTo here, this may cause problems
                If OldAbDate = 0 Then AbReason = "Date Not Found"
                GoTo AddReason 'You still have this GoTo here, this may cause problems
                Else
                    rowcount2 = rowcount2 + 1
            End If
        Next p
        If OldEmpNo = "" Then
            AbReason = "Employee Not Found"
            GoTo AddReason 'You still have this GoTo here, this may cause problems
            Else
                rowcount2 = rowcount2 + 1
                Next p 'You have already closed the "For p" loop 6 lines above, this is going to be your main problem
                        'because you can't close it twice
        End If
        Else
            If Cells(RowCount, 4) <> "" Then RowCount = RowCount + 1
    End If
Next o

Hope that helps!

 

[Nyanko]

Hi thanks for the speedy response !!!

The goto references (for the moment) still point to a valid reference, so I know that's not the issue.

However I see what you state about the two "Next p" statements !! But I need to have the p loop re-commence at these points depending on the outcome of the IFs... how would I be able to do this without closing the loop ?

Many thanks again

 

[Lewiy]

It’s tricky without being able to tell exactly what you are doing (although I’m starting to get the picture). One thing I’m not understanding is what is the purpose of the “For p” loop? You don’t actually appear to be referencing “p” at any point during the code. Is it referenced in the sections of code where the GoTo takes you? If so, can you post the GoTo code so I can see what it’s doing?

 

[Nyanko]

Hi,

I'm using the "For p" loop to search for matching data on sheet Sick_AltDuty within the "For o" loop. The GoTo simply instructs to add the AbReason data into the missing cell searched at the begining of the "For o" loop.

My data looks like :
Absence Report sheet
EmpNo....EmpName....Abdate.......AbReason
1234...... Mr B Test.....01/01/07...
1234.......Mr B Test.....02/01/07...
408.........Mr C Test.....01/01/07...Long Term Sick

The "For o" loop is to look through column D (AbReason) and where it is empty to look in another tab for a reason :

Sick_AltDuty sheet
AbDate......EmpNo....EmpName....AltDuty....Hrs....AbReason
01/01/07....999 ......Mr A Test .......Y...........12.......Migrane
01/01/07....1234.....Mr B Test.........N..........10.......Fever
01/02/07....1234.....Mr B Test.........N..........10.......Fever
05/01/07....444.......Mr D Test........Y...........5........Infection

So the missing value for both days should be Fever. The Goto therefore is just a tweak on :

                Sheets("Absence Report").Select
                Cells(roe, 4) = AbReason
                roe = roe + 1
                Sheets("Sick_Altduty").Select

at the appropriate places ...

I really hope that makes sense !!!!!

 

[Norie]

I'm sorry to say but that code's a bit of a mess.

This will compile.

For o = 1 To LastRow
    Sheets("Absence Report").Select
    If Cells(RowCount, 4) = "" Then
        If Cells(RowCount, 3) = "" Then
            MsgBox ("end")
            Exit For
        Else
            EmpNo = Cells(RowCount, 1)
            AbDate = Cells(RowCount, 3)
            RowCount = RowCount + 1
        End If
        Sheets("Sick_AltDuty").Select
        Range("B3").Select
        For p = 1 To LastRow2
            OldEmpNo = Cells(rowcount2, 2)
            If OldEmpNo = EmpNo Then
                OldAbDate = Cells(rowcount2, 1)
                If OldAbDate = AbDate Then AbReason = Cells(rowcount2, 9)
                    GoTo AddReason 'You still have this GoTo here, this may cause problems
                    If OldAbDate = 0 Then
                        AbReason = "Date Not Found"
                        GoTo AddReason 'You still have this GoTo here, this may cause problems
                    Else
                        rowcount2 = rowcount2 + 1
                    End If
                End If
        Next p
        If OldEmpNo = "" Then
            AbReason = "Employee Not Found"
            GoTo AddReason 'You still have this GoTo here, this may cause problems
        Else
            rowcount2 = rowcount2 + 1
        End If
    Else
        If Cells(RowCount, 4) <> "" Then RowCount = RowCount + 1
    End If
Next o

But I don't know if it will do what you want, because the code is difficult to follow.

PS You really should try and avoid using Goto.

 

[Nyanko]

I'm sorry to say but that code's a bit of a mess.

But I don't know if it will do what you want, because the code is difficult to follow.

PS You really should try and avoid using Goto.

Hi, unfortunately I've inherited a massive spreadsheet that was created via recorder and my knowledge is self taught !! A bit of the blind leading the blind !!

I'm trying to remove the goto's, however it's making sure the FORs and IFs are correct that giving me a headache !!!

 

[Lewiy]

Ok, if all this is supposed to do is fill in the missing absence reasons, then hopefully this will do the job (based on the sample data you provided).

Sub FindMissing()
Dim Absence As Worksheet
Dim Sick As Worksheet
Dim Limit As Long
Dim c As Long
Dim SickLimit As Long
Dim sc As Long

Set Absence = Worksheets("Absence Report")
Set Sick = Worksheets("Sick_AltDuty")
Limit = Absence.Cells(Rows.Count, 1).End(xlUp).Row
SickLimit = Sick.Cells(Rows.Count, 1).End(xlUp).Row

For c = 2 To Limit
    If Absence.Cells(c, 4) = "" And Absence.Cells(c, 3) <> "" Then
        For sc = 2 To SickLimit
            If Sick.Cells(sc, 2) = Absence.Cells(c, 1) And Sick.Cells(sc, 1) = Absence.Cells(c, 3) Then
                Absence.Cells(c, 4) = Sick.Cells(sc, 6)
            End If
        Next sc
    End If
Next c
End Sub

 

[Nyanko]

Holy moly !!

That's amazing.

Now I can see the way that you've done it !! Using the FOR letter for the rowcount and setting the worksheet (I didn't know you could do that !) I also didnot know that I could do the IF statements over 2 sheets. Well that makes things SO much easier !

Thank you. It's speeded up my code a million-fold and given me a lot more to learn !!!