VBA Copy IF Value Equals

Question

I wonder whether someone may be able to help me please.

Firstly, I'm the first to admit, I've received help to get this stage, but I'm a little unsure about to get past a issue I have with the code below.

To give a little background:

What I'm trying to do, is to perform a check where the code looks at list of projects on the sheet "AllData" (Source) sheet, starting at cell E3, and copies the cell if it contains the text value "Enhancements" and pastes this to the "Enhancements" (Destination) sheet.

In addition, the code also takes the 'Actuals' manhour figure and date associated to each project and totals manhours by project and by period into the respective cells on the Destination Sheet (Enhancements Sheet). These are the "RVal" and "RDate" variables.

Revised Code - Full Working Script

    Sub Extract()
    Dim i As Long, j As Long, m As Long
    Dim strProject As String
    Dim RDate As Date
    Dim RVal As Single
    Dim BlnProjExists As Boolean
    With Sheets("Enhancements").Range("B3")
    For i = 1 To .CurrentRegion.Rows.Count - 1
        For j = 0 To 13
            .Offset(i, j) = ""
        Next j
    Next i
End With
With Sheets("AllData").Range("E3")
    For i = 1 To .CurrentRegion.Rows.Count - 1
    strProject = .Offset(i, 0)
    If InStr(strProject, "Enhancements") = 0 Then
        GoTo NextLoop
    End If
        RDate = .Offset(i, 3)
        RVal = .Offset(i, 4)
        With Sheets("Enhancements").Range("B3")
            If .CurrentRegion.Rows.Count = 1 Then
                .Offset(1, 0) = strProject
                j = 1
            Else
                BlnProjExists = False
                For j = 1 To .CurrentRegion.Rows.Count - 1
                    If .Offset(j, 0) = strProject Then
                        BlnProjExists = True
                        Exit For
                    End If
                Next j
                If BlnProjExists = False Then
 .Offset(j, 0) = strProject
                End If
            End If
            Select Case Format(RDate, "mmm yy")
                Case "Apr 13"
                    m = 1
                Case "May 13"
                    m = 2
                Case "Jun 13"
                    m = 3
                Case "Jul 13"
                    m = 4
                Case "Aug 13"
                    m = 5
                Case "Sep 13"
                    m = 6
                Case "Oct 13"
                    m = 7
                Case "Nov 13"
                    m = 8
                Case "Dec 13"
                    m = 9
                Case "Jan 14"
                    m = 10
                Case "Feb 14"
                    m = 11
                Case "Mar 14"
                    m = 12
            End Select
            .Offset(j, m) = .Offset(j, m) + RVal
        End With
NextLoop:
    Next i
End With
End Sub

Unfortunately when I try to run this I receive a 'Compile error: Label not defined' error, and the debug highlights the following line as the problem and I'm not sure why:

GoTo Nexti

I just wondered whether someone could possibly look at this please and let me know where I've gone wrong?

If it helps, I can provide a link to my test file.

Many thanks and kind regards

Answer 1

You're not doing anything differently when the value is "Enhancements". Change this:

    If InStr(.Offset(i, 0), "Enhancements") > 0 Then
        strProject = .Offset(i, 0)
    End If

to this:

    If InStr(strProject, "Enhancements") = 0 Then
        GoTo NextLoop
    End If

and add the NextLoop identifier before "Next i":

        End With
NextLoop:
    Next i
End With
End Sub