Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
Could anyone please tell why the funnel is kept running without stopping even
after the expected task is completed. Sub Macro() Do Until blanks For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next Loop End Sub Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
On 3 Aug, 14:04, AlanW wrote:
Could anyone please tell why the funnel is kept running without stopping even after the expected task is completed. Sub Macro() Do Until blanks For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next Loop End Sub Thank you You have told it to run until an undeclared variable called Blanks is true, which is never referred to again! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
Alan,
You have a For Loop inside a Do Loop. Your For Loop will go through all cells in A1:A10 and terminate when all cells have been processed. Your Do Loop however will not terminate. It will terminate when the variable blanks evaluates to true. You don't seem to really need the Do Loop in your code. If your intent is to not go through the For loop if there are blanks in A1:A10, then, you can do it with an If statement like this: If Application.WorksheetFunction.CountA(Range("A1:A10 ")) = 10 Then For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next End If -- Hope that helps. Vergel Adriano "AlanW" wrote: Could anyone please tell why the funnel is kept running without stopping even after the expected task is completed. Sub Macro() Do Until blanks For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next Loop End Sub Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
If you only need the code to look at A1:A10, you should take out the
'Do Until blanks' and 'Loop' code ... worked just fine for me without those lines... ray |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
Because you have not defined blanks
You don't even need it if you want to do it just for A1 to A10 Use this: Sub Macro() For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next End Sub "AlanW" wrote: Could anyone please tell why the funnel is kept running without stopping even after the expected task is completed. Sub Macro() Do Until blanks For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next Loop End Sub Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop
The do until "blanks" does nothing and the do/loop is doing nothing. All you
need is the for/next loop Sub DocolorMacro() For Each c In Range("A1:A10") Select Case cell.Value Case "W":x=1 Case "Q":x=7 Case Else:x=9 End Select c.Offset(,1).Interior.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "AlanW" wrote in message ... Could anyone please tell why the funnel is kept running without stopping even after the expected task is completed. Sub Macro() Do Until blanks For Each cell In Range("A1:A10") Select Case cell.Value Case "W" cell.Offset(0, 1).Interior.ColorIndex = 1 Case "Q" cell.Offset(0, 1).Interior.ColorIndex = 7 Case Else cell.Offset(0, 1).Interior.ColorIndex = 9 End Select Next Loop End Sub Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |