Running a For function in a For function
 

Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

Sub for_in_for()
For i = 1 To 3
For j = 1 To 3
Cells(i, j).Value = i + j
Next
Next
End Sub

or something similar
--
Gary''s Student - gsnu200798


"C. Corodan" wrote:

Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

I think you stand a greater chance of getting help if you post your code or
at least explain what conditions you are looping for and what the exit
conditon is

Mike

"C. Corodan" wrote:

Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

I'm not clear what you are trying to do (example code would be useful), but
perhaps the following code will get you started.

Dim XEQ2 As Boolean
Dim N As Long
Dim ExitFirst As Boolean

For N = 1 To 10
Debug.Print N
' set the value of ExitFirst to True to exit out of the
' first For loop.
If ExitFirst Then
' set XEQ2 to True to run the second loop.
XEQ2 = True
Exit For
End If
Next N

If XEQ2 = True Then
For N = 11 To 20
Debug.Print N
Next N
End If


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"C. Corodan" wrote in message
...
Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

First at all, thank you all for your ideas, I will have time to try them only
next week.

As you requested more details, here they are. I am trying to recreate an
SAP-based sales planning tool in Excel, due to the unavailability of some SAP
functionalities for a country. From an SAP download, a set of macros is
extracting all data related to a Sales Representative and creates a new
workbook for each Sales Rep. The new workbook is not a simple workbook, but
it has a lot of functionalities and validations, e.g. for adding a new
Customer - Article combination or distributing yearly sales volumes to each
month of the interval covered.

Some of the code I already have is:

'Sub copy_to_workbooks
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer
Dim FileExtStr As String
Dim FileFormatNum As Long

'Name of the sheet with your data
Set ws1 = Sheets("Sales Planning Aug 08") '<<< Change

'Determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
If ws1.Parent.FileFormat = 56 Then
FileExtStr = ".xls": FileFormatNum = 56
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If

'Set filter range : A1 is the top left cell of your filter range and the
header of the first column, AA is the last column in the filter range
Set rng = ws1.Range("A1:AA" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if
needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B,
.......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
MyPath = "J:\02 Projects\_Sales Planning\02 Integration\02 OpCos\...\"

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

'Create folder for the new files
foldername = MyPath & Format(Now, "mmm yy") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
....

Here is where I want to add a new for function

....

'Save the file in the new folder and close it
WSNew.Parent.SaveAs foldername & cell.Value & Format(Now, " mmm yy") &
FileExtStr, FileFormatNum
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

....


"C. Corodan" wrote:

Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

OK, posted some of the code under my initial message. Thanks

"Mike H" wrote:

I think you stand a greater chance of getting help if you post your code or
at least explain what conditions you are looping for and what the exit
conditon is

Mike

"C. Corodan" wrote:

Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?

Running a For function in a For function
 

Thanks, I have posted some of the code under my initial message.

Regars,

"Chip Pearson" wrote:

I'm not clear what you are trying to do (example code would be useful), but
perhaps the following code will get you started.

Dim XEQ2 As Boolean
Dim N As Long
Dim ExitFirst As Boolean

For N = 1 To 10
Debug.Print N
' set the value of ExitFirst to True to exit out of the
' first For loop.
If ExitFirst Then
' set XEQ2 to True to run the second loop.
XEQ2 = True
Exit For
End If
Next N

If XEQ2 = True Then
For N = 11 To 20
Debug.Print N
Next N
End If


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"C. Corodan" wrote in message
...
Hello,

I have a For function executing a loop. Inside this function, before it is
completed, I want to launch a new For function for a different loop.

Any ideas?