+ Reply to Thread
Results 1 to 8 of 8

??? "For...Next" code only run 17/35 times

  1. #1
    Jaylin
    Guest

    ??? "For...Next" code only run 17/35 times


    I have a "for...Next" code that is supposed to loop 35 times getting value
    from cell A3-A38

    But it only runs from A3-A17 and stop.
    I have to run the code 3 times with the following modification:-
    (1) run For x=A3 to A17
    (2) run For x=A18 to A30
    (3) run For x =A31 to A38

    Hope I could have some advice to fix the problem so that I can run the code
    once.
    Thanks a million for your time and expert advice :-)
    Jaylin
    *****Jaylin Message ended*******

  2. #2
    Tim Williams
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    If you need suggestions then you might consider posting your actual code.

    Since we have to guess I might suggest something like:

    *********************************
    sub DoAll()
    with thisworkbook.sheets("Sheet1")
    DoSomething .Range("A3:A17")
    DoSomething .Range("A18:A30")
    DoSomething .Range("A31:A31")
    end with
    end sub

    sub DoSomething(rng as range)
    dim c as range
    for each c in rng
    'do something with c
    next c
    end sub
    **********************************


    Tim

    "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    >
    > I have a "for...Next" code that is supposed to loop 35 times getting value
    > from cell A3-A38
    >
    > But it only runs from A3-A17 and stop.
    > I have to run the code 3 times with the following modification:-
    > (1) run For x=A3 to A17
    > (2) run For x=A18 to A30
    > (3) run For x =A31 to A38
    >
    > Hope I could have some advice to fix the problem so that I can run the
    > code
    > once.
    > Thanks a million for your time and expert advice :-)
    > Jaylin
    > *****Jaylin Message ended*******




  3. #3
    Jaylin
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    Dear Mr Williams

    Thanks a lot for your immediate help. Pls find below an extract of the code:-


    Dim x As Variant *****x is A3 to A38 of a list of country
    names*******
    Dim y As String *****y is to name a worksheet as the country
    name***


    For x = 3 To 38
    Worksheets("DIY").Activate ***Sheet "DIY" is the mastersheet *********
    Range("E2").Select
    Cells(2, 5).Value = Worksheets("LIST").Cells(x, 3).Value


    Worksheets("A").Select
    Range("Mget").Select

    '**********Code Retrieve Current month from a database *******

    ****make a copy of sheet "DIY" and replace all formula to
    values***************

    y = Sheets("list").Cells(x, 3).Value
    Sheets("DIY").Select
    Sheets("DIY").Copy before:=Sheets("DIY")
    ActiveSheet.Name = y
    Sheets(y).Select
    Range("A1", "bz200").Select

    Application.CutCopyMode = True
    Selection.Copy
    Range("A1", "bz200").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    SkipBlanks _
    :=False, Transpose:=False
    Sheets("DIY").Select
    Next x


    --
    Thanks a million for your time and expert advice :-)
    Jaylin
    *****Jaylin Message ended*******


    "Tim Williams" wrote:

    > If you need suggestions then you might consider posting your actual code.
    >
    > Since we have to guess I might suggest something like:
    >
    > *********************************
    > sub DoAll()
    > with thisworkbook.sheets("Sheet1")
    > DoSomething .Range("A3:A17")
    > DoSomething .Range("A18:A30")
    > DoSomething .Range("A31:A31")
    > end with
    > end sub
    >
    > sub DoSomething(rng as range)
    > dim c as range
    > for each c in rng
    > 'do something with c
    > next c
    > end sub
    > **********************************
    >
    >
    > Tim
    >
    > "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    > news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    > >
    > > I have a "for...Next" code that is supposed to loop 35 times getting value
    > > from cell A3-A38
    > >
    > > But it only runs from A3-A17 and stop.
    > > I have to run the code 3 times with the following modification:-
    > > (1) run For x=A3 to A17
    > > (2) run For x=A18 to A30
    > > (3) run For x =A31 to A38
    > >
    > > Hope I could have some advice to fix the problem so that I can run the
    > > code
    > > once.
    > > Thanks a million for your time and expert advice :-)
    > > Jaylin
    > > *****Jaylin Message ended*******

    >
    >
    >


  4. #4
    Toppers
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    FYI, the extract of code provided ran OK for me i.e. generated 36 worksheets.
    I commented out the two lines below which didn't effect the loop.

    Worksheets("A").Select
    Range("Mget").Select


    (XL2003)

    "Jaylin" wrote:

    > Dear Mr Williams
    >
    > Thanks a lot for your immediate help. Pls find below an extract of the code:-
    >
    >
    > Dim x As Variant *****x is A3 to A38 of a list of country
    > names*******
    > Dim y As String *****y is to name a worksheet as the country
    > name***
    >
    >
    > For x = 3 To 38
    > Worksheets("DIY").Activate ***Sheet "DIY" is the mastersheet *********
    > Range("E2").Select
    > Cells(2, 5).Value = Worksheets("LIST").Cells(x, 3).Value
    >
    >
    > Worksheets("A").Select
    > Range("Mget").Select
    >
    > '**********Code Retrieve Current month from a database *******
    >
    > ****make a copy of sheet "DIY" and replace all formula to
    > values***************
    >
    > y = Sheets("list").Cells(x, 3).Value
    > Sheets("DIY").Select
    > Sheets("DIY").Copy before:=Sheets("DIY")
    > ActiveSheet.Name = y
    > Sheets(y).Select
    > Range("A1", "bz200").Select
    >
    > Application.CutCopyMode = True
    > Selection.Copy
    > Range("A1", "bz200").Select
    > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    > SkipBlanks _
    > :=False, Transpose:=False
    > Sheets("DIY").Select
    > Next x
    >
    >
    > --
    > Thanks a million for your time and expert advice :-)
    > Jaylin
    > *****Jaylin Message ended*******
    >
    >
    > "Tim Williams" wrote:
    >
    > > If you need suggestions then you might consider posting your actual code.
    > >
    > > Since we have to guess I might suggest something like:
    > >
    > > *********************************
    > > sub DoAll()
    > > with thisworkbook.sheets("Sheet1")
    > > DoSomething .Range("A3:A17")
    > > DoSomething .Range("A18:A30")
    > > DoSomething .Range("A31:A31")
    > > end with
    > > end sub
    > >
    > > sub DoSomething(rng as range)
    > > dim c as range
    > > for each c in rng
    > > 'do something with c
    > > next c
    > > end sub
    > > **********************************
    > >
    > >
    > > Tim
    > >
    > > "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    > > news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    > > >
    > > > I have a "for...Next" code that is supposed to loop 35 times getting value
    > > > from cell A3-A38
    > > >
    > > > But it only runs from A3-A17 and stop.
    > > > I have to run the code 3 times with the following modification:-
    > > > (1) run For x=A3 to A17
    > > > (2) run For x=A18 to A30
    > > > (3) run For x =A31 to A38
    > > >
    > > > Hope I could have some advice to fix the problem so that I can run the
    > > > code
    > > > once.
    > > > Thanks a million for your time and expert advice :-)
    > > > Jaylin
    > > > *****Jaylin Message ended*******

    > >
    > >
    > >


  5. #5
    Jaylin
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    Dear Mr Toppers

    Thank you very much for your help. I wonder if the problem would relate to
    computer memory / capacity?

    --
    Thanks a million for your time and expert advice :-)
    Jaylin
    *****Jaylin Message ended*******


    "Toppers" wrote:

    > FYI, the extract of code provided ran OK for me i.e. generated 36 worksheets.
    > I commented out the two lines below which didn't effect the loop.
    >
    > Worksheets("A").Select
    > Range("Mget").Select
    >
    >
    > (XL2003)
    >
    > "Jaylin" wrote:
    >
    > > Dear Mr Williams
    > >
    > > Thanks a lot for your immediate help. Pls find below an extract of the code:-
    > >
    > >
    > > Dim x As Variant *****x is A3 to A38 of a list of country
    > > names*******
    > > Dim y As String *****y is to name a worksheet as the country
    > > name***
    > >
    > >
    > > For x = 3 To 38
    > > Worksheets("DIY").Activate ***Sheet "DIY" is the mastersheet *********
    > > Range("E2").Select
    > > Cells(2, 5).Value = Worksheets("LIST").Cells(x, 3).Value
    > >
    > >
    > > Worksheets("A").Select
    > > Range("Mget").Select
    > >
    > > '**********Code Retrieve Current month from a database *******
    > >
    > > ****make a copy of sheet "DIY" and replace all formula to
    > > values***************
    > >
    > > y = Sheets("list").Cells(x, 3).Value
    > > Sheets("DIY").Select
    > > Sheets("DIY").Copy before:=Sheets("DIY")
    > > ActiveSheet.Name = y
    > > Sheets(y).Select
    > > Range("A1", "bz200").Select
    > >
    > > Application.CutCopyMode = True
    > > Selection.Copy
    > > Range("A1", "bz200").Select
    > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    > > SkipBlanks _
    > > :=False, Transpose:=False
    > > Sheets("DIY").Select
    > > Next x
    > >
    > >
    > > --
    > > Thanks a million for your time and expert advice :-)
    > > Jaylin
    > > *****Jaylin Message ended*******
    > >
    > >
    > > "Tim Williams" wrote:
    > >
    > > > If you need suggestions then you might consider posting your actual code.
    > > >
    > > > Since we have to guess I might suggest something like:
    > > >
    > > > *********************************
    > > > sub DoAll()
    > > > with thisworkbook.sheets("Sheet1")
    > > > DoSomething .Range("A3:A17")
    > > > DoSomething .Range("A18:A30")
    > > > DoSomething .Range("A31:A31")
    > > > end with
    > > > end sub
    > > >
    > > > sub DoSomething(rng as range)
    > > > dim c as range
    > > > for each c in rng
    > > > 'do something with c
    > > > next c
    > > > end sub
    > > > **********************************
    > > >
    > > >
    > > > Tim
    > > >
    > > > "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    > > > news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    > > > >
    > > > > I have a "for...Next" code that is supposed to loop 35 times getting value
    > > > > from cell A3-A38
    > > > >
    > > > > But it only runs from A3-A17 and stop.
    > > > > I have to run the code 3 times with the following modification:-
    > > > > (1) run For x=A3 to A17
    > > > > (2) run For x=A18 to A30
    > > > > (3) run For x =A31 to A38
    > > > >
    > > > > Hope I could have some advice to fix the problem so that I can run the
    > > > > code
    > > > > once.
    > > > > Thanks a million for your time and expert advice :-)
    > > > > Jaylin
    > > > > *****Jaylin Message ended*******
    > > >
    > > >
    > > >


  6. #6
    Toppers
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    Jaylin,
    It suggests (still) there is something else in the code, not
    capacity. If you wish you can send your workbook to me and I'll investigate
    further. (toppers@johntopley.fsnet.co.uk).

    "Jaylin" wrote:

    > Dear Mr Toppers
    >
    > Thank you very much for your help. I wonder if the problem would relate to
    > computer memory / capacity?
    >
    > --
    > Thanks a million for your time and expert advice :-)
    > Jaylin
    > *****Jaylin Message ended*******
    >
    >
    > "Toppers" wrote:
    >
    > > FYI, the extract of code provided ran OK for me i.e. generated 36 worksheets.
    > > I commented out the two lines below which didn't effect the loop.
    > >
    > > Worksheets("A").Select
    > > Range("Mget").Select
    > >
    > >
    > > (XL2003)
    > >
    > > "Jaylin" wrote:
    > >
    > > > Dear Mr Williams
    > > >
    > > > Thanks a lot for your immediate help. Pls find below an extract of the code:-
    > > >
    > > >
    > > > Dim x As Variant *****x is A3 to A38 of a list of country
    > > > names*******
    > > > Dim y As String *****y is to name a worksheet as the country
    > > > name***
    > > >
    > > >
    > > > For x = 3 To 38
    > > > Worksheets("DIY").Activate ***Sheet "DIY" is the mastersheet *********
    > > > Range("E2").Select
    > > > Cells(2, 5).Value = Worksheets("LIST").Cells(x, 3).Value
    > > >
    > > >
    > > > Worksheets("A").Select
    > > > Range("Mget").Select
    > > >
    > > > '**********Code Retrieve Current month from a database *******
    > > >
    > > > ****make a copy of sheet "DIY" and replace all formula to
    > > > values***************
    > > >
    > > > y = Sheets("list").Cells(x, 3).Value
    > > > Sheets("DIY").Select
    > > > Sheets("DIY").Copy before:=Sheets("DIY")
    > > > ActiveSheet.Name = y
    > > > Sheets(y).Select
    > > > Range("A1", "bz200").Select
    > > >
    > > > Application.CutCopyMode = True
    > > > Selection.Copy
    > > > Range("A1", "bz200").Select
    > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    > > > SkipBlanks _
    > > > :=False, Transpose:=False
    > > > Sheets("DIY").Select
    > > > Next x
    > > >
    > > >
    > > > --
    > > > Thanks a million for your time and expert advice :-)
    > > > Jaylin
    > > > *****Jaylin Message ended*******
    > > >
    > > >
    > > > "Tim Williams" wrote:
    > > >
    > > > > If you need suggestions then you might consider posting your actual code.
    > > > >
    > > > > Since we have to guess I might suggest something like:
    > > > >
    > > > > *********************************
    > > > > sub DoAll()
    > > > > with thisworkbook.sheets("Sheet1")
    > > > > DoSomething .Range("A3:A17")
    > > > > DoSomething .Range("A18:A30")
    > > > > DoSomething .Range("A31:A31")
    > > > > end with
    > > > > end sub
    > > > >
    > > > > sub DoSomething(rng as range)
    > > > > dim c as range
    > > > > for each c in rng
    > > > > 'do something with c
    > > > > next c
    > > > > end sub
    > > > > **********************************
    > > > >
    > > > >
    > > > > Tim
    > > > >
    > > > > "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    > > > > news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    > > > > >
    > > > > > I have a "for...Next" code that is supposed to loop 35 times getting value
    > > > > > from cell A3-A38
    > > > > >
    > > > > > But it only runs from A3-A17 and stop.
    > > > > > I have to run the code 3 times with the following modification:-
    > > > > > (1) run For x=A3 to A17
    > > > > > (2) run For x=A18 to A30
    > > > > > (3) run For x =A31 to A38
    > > > > >
    > > > > > Hope I could have some advice to fix the problem so that I can run the
    > > > > > code
    > > > > > once.
    > > > > > Thanks a million for your time and expert advice :-)
    > > > > > Jaylin
    > > > > > *****Jaylin Message ended*******
    > > > >
    > > > >
    > > > >


  7. #7
    Steve
    Guest

    Re: ??? "For...Next" code only run 17/35 times


    "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    >
    > I have a "for...Next" code that is supposed to loop 35 times getting value
    > from cell A3-A38
    >
    > But it only runs from A3-A17 and stop.


    I'm surprised it does that!

    > I have to run the code 3 times with the following modification:-
    > (1) run For x=A3 to A17
    > (2) run For x=A18 to A30
    > (3) run For x =A31 to A38
    >
    > Hope I could have some advice to fix the problem so that I can run the
    > code
    > once.
    > Thanks a million for your time and expert advice :-)
    > Jaylin
    > *****Jaylin Message ended*******


    Try some debug.print lines in your code. Example

    Sub test()
    Dim x
    For x = A3 To A17
    Debug.Print A3
    Debug.Print A17
    Debug.Print x
    Next x
    End Sub

    When I run the above, I see a single zero but it feed three lines. VBA is
    seeing A3 A17 as null and x as zero.

    Without knowing what you are trying to achieve, its difficult to give a good
    answer other than say if you are trying to have your code loop through your
    spreadsheet, that's not the way to do it. VBA does not recognise cells
    addresses in the format you use in you question.

    You might try looking at the following site under the heading 'Arrays'.
    http://www.puremis.net/excel/tips.shtml This will show you how to pull
    values from a spreadsheet into an array, how to do a simple modification and
    put it back.

    You might also look at "Range property" and "Cells Property" in VBA help and
    look at the examples.

    HTH
    Steve







  8. #8
    Jack
    Guest

    Re: ??? "For...Next" code only run 17/35 times

    try changing your index variables to x, y, and x instead of using x threee
    times.

    "Steve" <No Spam> wrote in message news:43e44dac@dnews.tpgi.com.au...
    >
    > "Jaylin" <Jaylin@discussions.microsoft.com> wrote in message
    > news:2BC733CA-7373-4AF5-8DDF-27A43E361C49@microsoft.com...
    >>
    >> I have a "for...Next" code that is supposed to loop 35 times getting
    >> value
    >> from cell A3-A38
    >>
    >> But it only runs from A3-A17 and stop.

    >
    > I'm surprised it does that!
    >
    >> I have to run the code 3 times with the following modification:-
    >> (1) run For x=A3 to A17
    >> (2) run For x=A18 to A30
    >> (3) run For x =A31 to A38
    >>
    >> Hope I could have some advice to fix the problem so that I can run the
    >> code
    >> once.
    >> Thanks a million for your time and expert advice :-)
    >> Jaylin
    >> *****Jaylin Message ended*******

    >
    > Try some debug.print lines in your code. Example
    >
    > Sub test()
    > Dim x
    > For x = A3 To A17
    > Debug.Print A3
    > Debug.Print A17
    > Debug.Print x
    > Next x
    > End Sub
    >
    > When I run the above, I see a single zero but it feed three lines. VBA is
    > seeing A3 A17 as null and x as zero.
    >
    > Without knowing what you are trying to achieve, its difficult to give a
    > good answer other than say if you are trying to have your code loop
    > through your spreadsheet, that's not the way to do it. VBA does not
    > recognise cells addresses in the format you use in you question.
    >
    > You might try looking at the following site under the heading 'Arrays'.
    > http://www.puremis.net/excel/tips.shtml This will show you how to pull
    > values from a spreadsheet into an array, how to do a simple modification
    > and put it back.
    >
    > You might also look at "Range property" and "Cells Property" in VBA help
    > and look at the examples.
    >
    > HTH
    > Steve
    >
    >
    >
    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1