Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

'Next without for' Compile error Excel 2010

Status
Not open for further replies.

Walter349

Technical User
Aug 16, 2002
250
BE
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.

Code:
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'
 
Hiya,

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

Regards

Jason
 
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
 
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'
 

Walter349 said:
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.
Code:
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...
Code:
    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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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'
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top