Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Do Loops Within Do Loops

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi - I've written code that has 1 loop within another loop that is within yet
another loop - looks something like this:

Do
Do
Do
Loop
Loop
Loop

It is not working though...just wanted to know if in theory a loop of this
sort should work (assuming all other coding is correct).

Thanks!



Similar Excel Video Tutorials

Helpful Excel Macros

Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum

Similar Topics







Is it possible to nest a Do Loop inside of a For Each Loop?

I have some code:

For Each mycell in Range(##)

Do
Code
Loop Until rowheader=rowheader2

Next mycell


as long as the code is correct in the loops, that should work, right? (in theory)


In a Do Loop, if I want to skip to the end of the loop somewhere inside the Loop (i.e. skip everything else in the loop and go back to loop over again, how do I do this?

I do not want to exit the loop, I mearly have a condition at the beginning of a loop that checks for shading and I want to repeat the loop if it is a specific color.

Thanks,
-cmeier7


Code:

Sub runner()
Dim a As Long

For a = 1 To 12 Step 1
  "Do something"
  Loop
Next a

End Sub


How do this task 100 times without repeating the whole "For..." code? Right now I have a sheet like this and the only way I can keep it running is if I manually run it over and over. I've tried to nest this loop in another loop but it acts like it can't redo the loop if it's already been performed.


How can I change the following code

If Worksheets("Players_20").Cells(2, 7).Value = "A" Then
Worksheets("Cards_20").Cells(4, 2).Value = 1
End If

so that it loops 15 times and during each loop the the code 'cell (4, 2)' points to a differant row as follows:

1st loop (4, 2)
2nd loop (5, 2)
3rd loop (6, 2)
.
.
15th loop (18, 2)

Thank you ,
Mike


I have a question regarding Do While loops. I'm trying to write a do while loop to search through
an array for a particular value and return the row number. This value is in the first column of the array and there are 211
of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times,
then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.
The loop I wrote is as follows:

-----------------

x = 1

'Condition at which to stop
Do Until Max_V13_elm_map_LC(n,2) = Arr_Max_V13_LC(x,1)

x = x+1 'row being returned

Loop

---------

'This places the value at (Row_Index,4) into (n,6) of the first array
Max_V13_elm_map_LC(n,6) = Arr_Max_V13_LC(Row_Index,4)


The value of n is counted in a for loop that this do loop is in.

I'm getting errors with this code. I'm not sure if I can specify the condition of the Do until loop as I did.

Thanks,


So I'm working on a workbook that has a two different Do While Loops that run for two specific columns which are columns A & B.

What happens currently if I started filling out the cells in column A, my loop works fine. But if I then go to column B that loop works fine, but if I go back to column A and enter data, that loop doesn't work.

But if I stay on column A until I'm done entering data. The loop works fine.
If I go to column B the loop works fine.

What I want to be able to do is go between column A & column B the loops to work.


I am trying to write a macro with a loop that counts the number of occurences of a specific piece of text within a range. I have tried both Do loops and ForEach loops. How do I end the loop? It just keeps wrapping back to the beginning. Excel does not seem to recognize EOF in the way Access or SQL does.


I have the following situation:

Code:

    j = 1
    i = 1
    Do While j <= 5
        Do While i <= 5            
            MsgBox CStr(i) & ":" & CStr(j)
            i = i + 1
        Loop
        j = j + 1
    Loop




and it appears to only run through the inner loop once and then exits both loops. Are nested loops not possible in VBA?

Thank you for your time,
Brandon


Hi,

I'm not sure if a loop is the right way to do this but stay with me!!

I have a file with the numbers 8 and 9 in row 2 (spanning the width of the spreadsheet) - what I want to do is delete every column that has a 9 in row 2.

I'm having trouble getting the actual loop to work as I'm not very experienced with loops - and I don't really understand what all the Dim stuff means!!

Can anyone help?


I have a For..Next loop that I wish to modify. In the code between these two statements, if I find a match, then I wish to move to the end of the loop, but not Exit the loop. Can anyone please advise how do this ? I have included some code showing the missing line needed.

Code:

 
x =7
for I = 1 to 15
    If I = x Then    
      skip to "Next I" statement and start loop with I =8
    End if
    y = 2 + I
    z = 3 + I^2
next I





Hi

This is probably a very easy question to solve but i need to create a loop which loops through two arrays. The loop should first loop through an array of path name variables to open the files then it should copy data from the file opened and paste it into another workbook using a second array for the sheet name.

I am sorry if i have not explained that very well.

Does anyone know how to do this as i am not sure??

Thank you

Jessicaseymour


I have a For..Next loop that I wish to modify. In the code between these tow lines, if I find a match, then I wish to move to the end of the loop, but not Exit the loop. Can anyone please advise how do this ? I have included some code showing the missing line needed. I do not wish to change the structure so that it involves ElseIF in this simple example

Code:

 
x =7
 for I = 1 to 15
 
    if I = x then
       go to "Next I" statement and start loop with I =8
    end if
   z = x +I
next I





I have been a member of this list for less than a month, but I have noticed in several posts that several seem to favor a For type loop over the Do type loop. Most of my programs are used to perform a variety of procedures on big lists of students so I am normally using Do While Len(student id) <> 0.

Would a For type loop be a better option in my given situation? Is there any particular advantage to using a For loop as opposed to a Do loop?

Thanks for the input!

Jeff


I have tried to search the internet for some explanations on For Loop, but I do not find anything remotely similar to the code that is provided on these message boards.

I'm mainly interested in how to loop through columns and why the loop works the way it does. I know right now I'm writing inefficient code when it comes to looping through columns. I've only mainly used Do While/Until Loops at this point.

If there is something already on the website that describes this information in depth I apologize for posting again and would appreciate the help locating it.

Thank you.


Hi

let see if i can explain what i am trying to do

I have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3

here is my main loop A1 = 5000
and second loop need to run inside the this loop


Sheet1.Activate
i = Range("A1")

For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b


I am currently nesting 15 for loop that all do the same thing. Is there a simpler way to do this?

I have tried playing with an outer loop that acts as an incrementer to the position of the 15 loops with no success.

Any help would be great.

The loops purpose is to create all combinations of a set of cells from 15 different worksheets.


hello.

i am looking at doing a loop inside my macro.
i have the loop set up, but am having trouble with the forumla that i want to loop in.

i want it to say:

if B4>B3, then copy B4:F4 and paste special values in C4(through G4).
then loop down to B5 and do the same.

if someone can help it would be much appreciated, also more information can be provided if needed.

thanks.
josh


Let say this is my loop:
Code:

For i=1 to 126

   If WorkSheet.Cells(4, i) = "searchWord" Then
     'Do sth
   End if

Next i



This 'For' loop keeping looping even after 'searchWord' matches with the cell value.
What I want to do is to stop the loop after spotting the two values are matched, so could I know is there a possible way to end the loop or replace 'for loop' with 'while loop'.

The value of 'i' wil be constant and there is only one cell that matches with the searchWord.

Please enlightens me with ideas and suggestions.


Hi, normally when I loop rows i use
Code:

 
for each bCell in Range
...
next bCell


It loops rows 1,2,3,4,5 etc, but i want to loop rows 5,4,3,2,1.

The problem is if i loop 1,2,3,4,5 and delete row 3 in the loop, row 4 will be row 3 after deletion but the loop is at number 4 so it will jump over old number 4 which is now number 3.


All,

Is there a way to have some code loop until I tell it to stop...............

So instead of the code below for the loop,

Loop Until IsEmpty(ActiveCell.Value)

Somthing like,

Loop Until the key esc is pressed

Obviously the orange is not the actual terminology that would be used for the code, but is there code that I could use to represent that data. Bottom line, is that I have a loop that opens a combo Box, and it goes until it gets to the bottom of the data, which can be several hundered lines. But, I would rather be able to manually stop the loop at any time instead. Any help would be greatly appreciated.

Thanks,
Dolphn22



I need help with some coding:

I want to make a loop that inerts the information under the last information that was enterd.

I have a colum with a value that is updated every day. I want this information that gets updated to every day to get just under the last one that was imported.

I have tryed with diffrent loops but i cant seem to get i working.

Here is the code that i have made:

Sub RedovisainformationiData()

For i = 5 To 10
If Sheets(2).Cells(i, 2) = "" Then
Sheets(2).Cells(i, 2) = Sheets(3).Cells(2, 3)
ElseIf Not Sheets(2).Cells(i, 2) = "" Then

End If
Sheets(2).Cells(i, 2).NumberFormat = "m/d/yyyy"
Next i
End Sub

the loop coninues every time and the information is writtin in every empty
row in the column thro the whole loop.

anyone with a good solution?


I'm trying to extend a loop according to data in a sheet, as follows :

For Loop = 1 to EndOfLoop
If Cells(Loop,1) = 1 Then
SelectString = (Loop+1) & ":" & (Loop+1)
Rows(Selectstring).Insert Shift:xldown
EndOfLoop = EndOfLoop + 1
End If
Next Loop

In the above, if column 1 contains anything but 1 we go to the next loop. If column 1 contains a 1 we insert a blank line and increase the loop variable by 1, to account for the fact that all the original data has been shifted down 1 line from the current point.

The problem is, it doesn't seem to be affecting the loop count (as if the loop lenght is set at the initial execution of the FOR statement and then cannot be altered. If I put

MsgBox(Loop)
MsgBox(EndOfLoop)

at the end of the code, I get Loop = original EndOfLoop and EndOfLoop = (original EndOfLoop+Number Of Blank Lines Inserted).

Is it not possible to dynamically alter a loop terminator variable from within a loop ? Do I have to use do...until ?


The following is the module I'm working on. The item which is not producing the desired results is the GrabMonitoring sub. What it is supposed to do is compare column 30 on the Bandwidth Monitoring tab to column 8 on the Monitoring tab and when a match exists to fill cells in columns 31-34 with items from columns 9-12. It repeats the run for a different component to fill columns 36-39. The code appears to run to completion but the values are not placed in the first sheet. Can someone let me know what it is that I'm doing wrong here?

Code:

Option Explicit
Option Base 1

Dim wsBMon, wsMonData As Worksheet
Dim BWRows, MonRows As Integer
Dim Loops, MonLoops As Integer

Sub MonitoringData()

    Set wsBMon = ActiveWorkbook.Worksheets("Bandwidth Monitoring")
    Set wsMonData = ActiveWorkbook.Worksheets("Monitoring")
    
' Get row count on Bandwidth Monitoring tab
    BWRows = wsBMon.UsedRange.rows.Count

' Get row count on Bandwidth Monitoring tab
    BWRows = wsBMon.UsedRange.rows.Count

    HeadersForBWMon
    LoopConcatenation
    GrabMonitoring

End Sub

Private Sub HeadersForBWMon()

    wsBMon.Cells(1, 30).Value = "CGID & Loop"
    wsBMon.Cells(1, 31).Value = "Loop A Interface IP"
    wsBMon.Cells(1, 32).Value = "Loop A Interface Name"
    wsBMon.Cells(1, 33).Value = "Loop A Router IP"
    wsBMon.Cells(1, 34).Value = "Loop A Router Name"

    wsBMon.Cells(1, 35).Value = "CGID & Loop"
    wsBMon.Cells(1, 36).Value = "Loop Z Interface IP"
    wsBMon.Cells(1, 37).Value = "Loop Z Interface Name"
    wsBMon.Cells(1, 38).Value = "Loop Z Router IP"
    wsBMon.Cells(1, 39).Value = "Loop Z Router Name"
    
End Sub

Private Sub LoopConcatenation()


For Loops = 2 To BWRows

    If wsBMon.Cells(Loops, 5)  "" Then
    
        wsBMon.Cells(Loops, 30).Value = CStr(wsBMon.Cells(Loops, 5)) & "LoopA"
        
        wsBMon.Cells(Loops, 35).Value = CStr(wsBMon.Cells(Loops, 5)) & "LoopZ"
    
    End If
        
Next

End Sub

Private Sub GrabMonitoring()

' Get monitoring data for A loops
For Loops = 2 To BWRows

    For MonLoops = 2 To MonRows
    
        If wsBMon.Cells(Loops, 30).Value = wsMonData.Cells(MonLoops, 8).Value Then
        
            wsBMon.Cells(Loops, 31).Value = wsMonData.Cells(MonLoops, 9).Value
            wsBMon.Cells(Loops, 32).Value = wsMonData.Cells(MonLoops, 10).Value
            wsBMon.Cells(Loops, 33).Value = wsMonData.Cells(MonLoops, 11).Value
            wsBMon.Cells(Loops, 34).Value = wsMonData.Cells(MonLoops, 12).Value
            
        End If
    
    Next MonLoops

Next Loops

' Get monitoring data for Z loops
For Loops = 2 To BWRows

    For MonLoops = 2 To MonRows
    
        If wsBMon.Cells(Loops, 35).Value = wsMonData.Cells(MonLoops, 8).Value Then
        
            wsBMon.Cells(Loops, 36).Value = wsMonData.Cells(MonLoops, 9).Value
            wsBMon.Cells(Loops, 37).Value = wsMonData.Cells(MonLoops, 10).Value
            wsBMon.Cells(Loops, 38).Value = wsMonData.Cells(MonLoops, 11).Value
            wsBMon.Cells(Loops, 39).Value = wsMonData.Cells(MonLoops, 12).Value
            
        End If
    
    Next MonLoops

Next Loops

End Sub





All,

I think this is an easy one, but am not sure as I am working from a macro that someone else created.........

Alright, here is the deal. In the macro, there is a loop, which I need, however, at certain times, I will need to be able to stop the macro without getting to the end of the loop, or deleting any data to make the loop end. The code for the loop is as follows:

Loop Until IsEmpty(ActiveCell.Offset(0, -2).Range("A1").Value)

Is there a way to say loop until the above OR until something is entered on the keyboard, so that if at anytime if I need to manually end the macro, I can hit like maybe CTRL-Shift-W or something to end the macro. Or is there already a universal key that will automatically end a macro? Any help or guidance would be greatly appreciated.

Thank You,
Dolphn22


Hi,

I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit.

So it looks something like this:

Do Until ActiveCell.Address = "a100"

Do whileActiveCell.value = ""
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select
Loop


bla bla bla

Loop

When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation!

Please help!

Thanks,
JillE