Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to make a running total | Excel Discussion (Misc queries) | |||
Running a Macro from a Spreadsheet Function | Excel Worksheet Functions | |||
VBA Function running order | Excel Programming | |||
Running Ontime's procedure while another function is running? | Excel Programming | |||
Running worksheet function in VBA | Excel Programming |