Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
It looks you are missing an End If
sourceRange.Copy destrange.PasteSpecial xlPasteAll End If ' this was missing Next ws RBS "Ray" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
The problem is that you have an End If outside the For...Next loop.
Next Fnum End If "Ray" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Ray" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
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, "Ray" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
That End If is fine. It goes with
If Fnum 0 Then But there was another End IF missing "JLGWhiz" wrote in message ... The problem is that you have an End If outside the For...Next loop. Next Fnum End If "Ray" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next without For? can't find the problem
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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Problem | Excel Programming | |||
Find and Find Next problem | Excel Programming | |||
Find Next problem | Excel Programming | |||
problem with FIND | Excel Worksheet Functions | |||
Find problem | Excel Programming |