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

Jaylin · 02-02-2006, 02:00 AM

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*******

Tim Williams · 02-02-2006, 02:25 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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*******

Jaylin · 02-02-2006, 02:40 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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*******
>
>
>

Toppers · 02-02-2006, 04:00 AM

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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*******
> >
> >
> >

Jaylin · 02-02-2006, 04:10 AM

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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*******
> > >
> > >
> > >

Toppers · 02-02-2006, 05:30 AM

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. ([email protected]).

"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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > 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*******
> > > >
> > > >
> > > >

Steve · 02-04-2006, 02:50 AM

"Jaylin" <[email protected]> wrote in message
news:[email protected]...
>
> 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

Jack · 02-04-2006, 08:35 AM

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

"Steve" <No Spam> wrote in message news:[email protected]...
>
> "Jaylin" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>
>
>
>
>
>