Next without For? can't find the problem

[Ray]

Hope someone with 'eagle eyes' can help on this one ...

Here's my code:
Sub UpdStoData_Click()
Dim Path As String, stonum As String, FilesInPath As String
Dim MyFiles() As String, Trange As String, Tcol As Integer
Dim SourceRcount As Long, x As Long, Fnum As Long, total As Long
Dim mybook As Workbook, basebook As Workbook, ws As Worksheet, Sh
As String
Dim sourceRange As Range, destrange As Range, myC As Range

Path = "\\Retus100-nt0009\common\US OPS Projects\from Stores\"

'Add a slash at the end if the user forget it
If Right(Path, 1) <> "\" Then
Path = Path & "\"
End If

' 'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(Path & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' On Error GoTo CleanUp

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set basebook = ThisWorkbook

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
total = Fnum

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(Path & MyFiles(Fnum), 0, True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
stonum = mybook.Sheets("Home").Range("c3").Value
stonum = Format(stonum, "000")

For Each ws In mybook.Worksheets
ws.Activate
If ws.Name <> "Home" Then

Sh = ws.Name

Set sourceRange = ws.Range("m7:m100")

Set myC = basebook.Sh.Range("m5:ba5").Find(stonum,
LookIn:=xlValues, LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox stonum & " wasn't found"
GoTo CleanUp
End If

Trange = Cells(8, Tcol).Resize(93, 1).Address

Set destrange = basebook.ws.Range(Trange)

sourceRange.Copy
destrange.PasteSpecial xlPasteAll
Next ws

mybook.Close savechanges:=False

Next Fnum
End If

Application.StatusBar = False

MsgBox "Store FCs are Updated!"

CleanUp:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = False

End Sub

'Basebook' is a summary workbook, designed to collect data from approx
30 external workbooks -- these external WBs are identical in setup,
including sheet names.

The code opens up each of the external workbooks and copies the
specified range from SheetA in mybook to SheetA (remember, sheet names
are identical) in Basebook .... using StoNum to identify the proper
column to paste the data into.

The problem is that I keep getting 'Next without For' errors and I
don't see where the problem is -- I have them all matched up, I
think. can you help?

TIA,
Ray

 

[RB Smissaert]

It looks you are missing an End If

sourceRange.Copy
destrange.PasteSpecial xlPasteAll
End If '>> this was missing
Next ws


RBS

 

[Guest]

The problem is that you have an End If outside the For...Next loop.

Next Fnum
End If

 

[Guest]

You will typically get that with a missing end if in your For/Next I count
one missing but don't know where you need it in your code because i dont have
the data, you have and If before the For Next with an end if after then you
have
If ws.Name <> "Home" Then
but the only other end if is related to
If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox stonum & " wasn't found"
GoTo CleanUp
End If

 

[George Nicholson]

The error message is a bit misleading in this case. All it really means is
that you've left some unfinished business hanging somewhere and the compiler
is very confused.

For Each ws In mybook.Worksheets ..............
If ws.Name <> "Home" Then

...............
End If '*This is missing*
...............

Next ws

HTH,

 

[George Nicholson]

That End If is fine. It goes with

If Fnum > 0 Then

But there was another End IF missing

 

[Ray]

Thanks all ...... RBS, that was it ....

BTW, can anyone tell me how to use the 'show quoted text' feature?
I'm accessing via Google.Groups, if that makes any difference ...