'Next without for' Compile error Excel 2010

[Walter349]

I cannot for the life of me, see why I am getting a 'Next without for' compile error for this.
Can anyone throw some light on what I am not seeing here? There is clearly something I have stuffed up.

For X = 1 To UBound(Z)

    Set Bk = Workbooks.Open(Z(X))

    On Error Resume Next
        Set Sh1 = Bk.Worksheets(Sheet2) ' The data source sheet in the source report

    On Error GoTo 0

    If Not Sh1 Is Nothing Then

        Set rng = Sh1.Range("I5") 'Name
        Set rng1 = Sh.Cells(Rows.Count, 1).End(xlUp)(2)
        If rng = "" Then
        rng = "N/K"
        rng1.Copy
        rng1.PasteSpecial xlValues
        Else
        rng.Copy
        rng1.PasteSpecial xlValues
        End If

        Set rng = Sh1.Range("I6") 'DOB
        Set rng1 = Sh.Cells(Rows.Count, 2).End(xlUp)(2)
        If rng = "" Then
        rng = "N/K"
        rng1.Copy
        rng1.PasteSpecial xlValues
        Else
        rng.Copy
        rng1.PasteSpecial xlValues
        End If

        Set rng = Sh1.Range("I8") 'Nationality
        Set rng1 = Sh.Cells(Rows.Count, 3).End(xlUp)(2)
        If rng = "" Then
        rng = "N/K"
        rng1.Copy
        rng1.PasteSpecial xlValues
        Else
        rng.Copy
        rng1.PasteSpecial xlValues
        End If
    

    Bk.Close

Next X

'If at first you don't succeed, then your hammer is below specifications'

 

[JasonEnsor]

Hiya,

Looks like you might be missing and End If before you Next X

Regards

Jason

 

[PHV]

I'd put the missing End If before the Bk.Close
Furthermore I'd replace this:
Set Sh1 = Bk.Worksheets(Sheet2)
with this:
Set Sh1 = Bk.Worksheets("Sheet2")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Walter349]

Thanks for the responses,

Jason,

The endif is at the end if each section for copying the data to the destination cell/workbook. I would not have thought that another would have been required as it appeared to me it would be orphaned. But I will give it a shot.

PHV,
Unfortunately each worksheet, is individually and uniquely named for all the workbooks(45,000+ of them), so I am forced to use the property name (sheet2) for the source worksheet rather than the tab code name.

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

The endif is at the end if each section for copying the data to the destination cell/workbook. I would not have thought that another would have been required as it appeared to me it would be orphaned.

If Not Sh1 Is Nothing Then

...has no End If

I like that way you indent MOST of your code. I indent ALL. However, I also recommend entering entire blocks before codeing the guts. So when you would code If Not Sh1 Is Nothing Then, here's what I would recommend entering the entire block and filling in the guts after...

    If Not Sh1 Is Nothing Then

    Else

    End If

Goes for...
[tt]
For...Next
Do...Loop
With...End With
Select Case...End Select
[/tt]
And guess what: it's what the vb editor does for you, when you start a Sub or Function -- it creates a stub block for you to fill in the guts.

Eliminates these problems.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Have a look here:

http://www.oaltd.co.uk/indenter/indentpage.asp

Note: this utility works fine for recent versions of VBA ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nodrog77]

I'm not sure if it still works in excel 2007/2010 but in 2003 I used Pretty Code print to isolate these sorts of problems

http://submain.com/products/prettycode.print.aspx

 

[Walter349]

Nodrog77,

Good news, it does work under windows 8, but you have to copy it from the clipboard, it will not open an xlsm document.

Skip,

Thanks for that, good advice. I started out programming COBOL many years ago, but haven't done anything serious for years.

Thanks all.

'If at first you don't succeed, then your hammer is below specifications'