For Each ... Next

Organic synthesis, the powerful chemistry developed by humankind, still often uses a simple step-by-step approach to convert a starting material A into a final product D, in which intermediate products B and C are isolated and purified for each next conversion step (Fig. 13.1). Catalytic steps are mostly combined with stoichiometric steps in the preparation of precursors or in the further downstream processing. Obvious disadvantages are low space-time yields (kg L-1 h-1), laborious recycle loops and large amounts of waste. 

For Each...Next Delineates a block of statements to be repeated. ... 

The For Each...Next loop structure is similar to the For...Next loop structure, except that it executes the statements within the loop for each object within a group of objects. Figure 14-12 illustrates the syntax of the statement. 

An important point the For..Each...Next loop returns an object variable in each pass through the loop. You can access or use all of the properties or methods that apply to Element. For example, in a loop such as... 

Often you use a loop structure to search through an array or collection of objects, looking for a certain value or property. Once you find a match, you don t need to cycle through the rest of the loops. You can exit from the loop using the Exit For (from a For...Next loop or For Each...Next loop) or Exit Do (from a Do While... loop). The Exit statement will normally be located within an If statement. For example. 

The simple macro in Figure 16-1 formats the text in a single cell. We d like our macro to be able to format text in a single cell or in a range of cells. To do this, we simply need to add an outer For..Each...Next loop. We ll add the lines of code... 

To specify the range of cells to use as data labels, we ll use the InputBox method. We ll define the range as an object variable, so that we can use a For...Each...Next loop. For a code example, see "You Can Define Your Own Objects" in Chapter 14. To convert SeriesCollection(l) into the general case, remember that a specific item in a collection can be referred to by its index number or by its name. We ll use the code... 

See also Do...Loop, Exit, For Each...Next, While...Wend... 

Group must be a collection or array. Element is the name assigned to the variable used to step through the collection or array. Group must be a collection or array. Example See examples of For Each...Next procedures in Chapter 14. 

This example illustrates how easy it is to use a For Each. .. Next loop to address ah cells in a given, highlighted block, even without ever specifying it as an array. The name cell specifies the individual cells in the highlighted block, and the loop addresses each of them in turn. (Any other name instead of cell would have worked just as well. You can see that cell is not recognized by the VBA compiler because it is not capitalized. (But Cells in... 

On the other hand, the macro Power disp enses with much of this, and simply condenses steps (b) through (e) into a simple, single For Each... Next loop. 

B2+ 1= 8 + 1 =9, hence we find the result 93 = 729 instead. In B4 the macro then computes 7303 = 3.87E + 08, and from there the size of the results quickly escalates to exceed the numerical capacity of the spreadsheet (approximately 9.9 X 10307) in B8, which is why we see the overflow sign. Therefore one should use For Each... Next loops only on cells containing values that are independent of all other data in the selected block. 

The cause of the problem lies in the numbers 3 and 113 used to raise the individual terms of the array to their cube power, and to take their cube root. When we raised the individual cells in the block (or the individual elements in Array) to their cube power, we reduced the results to single precision. To demonstrate this and, at the same time, fix the problem, add a dimension statement to both macros, reading Dim p As Double , and add a line specifying the value of p (e.g., either p = 3 or p =1/3 ). Finally, in the For Each... Next (or in the nested For... Next loop) refer to p rather than to 3 (or 1/3). The macros now should read like the example shown below, where we have also added a title and some comment lines and, as before, boldfaced the most recent changes. 

The For Each...Next loop is similar to a For... Next statement except that it will automatically apply the statements to each element of the specified group. The syntax is... 

This chapter will focus on the use of zeolites in cascade reactions, i.e. combined catalytic reactions without intermediate recovery steps [17]. Here nature serves as the shining example numerous multistep cascade syntheses are executed in the cells of living organisms without separation of intermediates. By contrast, in fine chemicals syntheses generally a step-by-step approach is applied in which intermediate products are isolated and purified for each next conversion step. 

For Each...Next loop 268 For...Next loop 268 form, custom 159 Format Cells dialog box 33 Format Painter toolbutton 34 formats, number... 

LOOKUP (worksheet function) 82 Lookup Reference functions 81 lookup in table, with linear interpolation 171 Loop, Do While... (VBA) 268 loop, exiting from (VBA) 269 Loop, For Each...Next (VBA) 268 Loop, For...Next (VBA) 268 looping (VBA) 267 LOWER (worksheet function) 79... 

There are four looping statements in VBA (For...Next, Do While...Loop, Do...Loop While, For Each...Next). Only the first two of these are presented since the other two are repetitious (or can be confusing). 

A total of six sets of samples were molded using PBT and ABS. The PBT was run first and the same three injection velocities were used as in the two-cavity study. Again shot size was adjusted for each velocity to get a 70% filled part. Samples were collected and set aside for each. Next the ABS material was tested following the same procedme. 

---