Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Stumped - New to a "Loop"

    I know this has to be simple to most, but Loops are new to me.

    I have some lines of code that need to run a number of times. The number of times it needs to run is the total of how many territories, regions and districts we have. I have that number calculated and populating cell AA1, currently as 117 total. So this code needs to run 117 times. I have tried:

    counter = 0
    MyNum = 1
    Do
    MyNum = MyNum + 1
    counter = counter + 1

    (my code inserted here)

    Loop Until MyNum = AA1

    It runs, but it won't stop, so it is not seeing the total of 117 in cell AA1. What am I missing?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    MyNum is an integer, and you're looping until it equals AA1 (not an integer!)...

    Do you think this condition will ever be met?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Probably not. I have since changed it to the below and I can get it to work fine, except I still cannot get the line to see I want to use the value in cell AA1.

    counter = 0
    MyNum = 0
    Do
    MyNum = MyNum + 1
    counter = counter + 1
    ActiveCell.Range("A1:A1").Select
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Selection.delete Shift:=xlToLeft
    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
    Selection.Copy
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Loop Until MyNum = 117

    When I try to change the Loop Until MyNum = from 117 to AA1. I don't understand how to make MyNum reflect the value in cell AA1 I guess. Or should I be using a different method. I found this in the help file as using a Loop until condition becomes true method.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Add a conditional line to check the value (MyNum = value of AA1), before it continues on the loop, something along this line assuming that cell AA1 is the reference: (not tested)

    If MyNum > Cells(1,27).Value Then Exit
    Last edited by shades; 10-21-08 at 20:09.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •