help with vba loop

B

bobh

Hi All,

I've been trying to write a macro in Excel 2003 that will ask for 'up
to' 5 page numbers to print with the following vba but I'm not have
any success with the 'Do Until' loop section. The first time into the
loop the correct value is in PgPrt which is 3 if I were to enter the 5
numbers in the comment below. But then after the
ActiveWindow.SelectedSheets.etc.... statement the values for the next
3 line are not correct. The value for PgPrt the first time thru would
be 8 then the next loop it would be 19 then the next loop it would be
21 etc..... I've tried several different combination but I just don't
get it. Any help re-writing the vba to get this working is very
appreciated!
bobh.

Sub PrintPages()
'
Dim PRange As String
Dim PgCount As Integer, PosStart As Integer, PosAcum As Integer,
PgPrt As Integer

'input looks like this --> 3,8,19,21,36
PRange = InputBox("Enter up to 5 page nbr's to print, seperated by
a comma( , )", "Enter Nbrs")

'determine number of pages entered by counting how many commas there
are and add 1 to it
PgCount = intStringCount(PRange, ",") + 1
If PgCount = 0 Then Exit Sub

'print each page
PosStart = 1
PosAcum = InStr(PosStart, PRange, ",")
PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))
Do Until PgCount = 1
ActiveWindow.SelectedSheets.PrintOut From:=PgPrt, To:=PgPrt
PosAcum = InStr(PosStart, PRange, ",")
PosStart = PosStart + PosAcum
PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))
PgCount = PgCount - 1
Loop

End Sub
 
D

Don Guillett

Hi All,



I've been trying to write a macro in Excel 2003 that will ask for 'up

to' 5 page numbers to print with the following vba but I'm not have

any success with the 'Do Until' loop section. The first time into the

loop the correct value is in PgPrt which is 3 if I were to enter the 5

numbers in the comment below. But then after the

ActiveWindow.SelectedSheets.etc.... statement the values for the next

3 line are not correct. The value for PgPrt the first time thru would

be 8 then the next loop it would be 19 then the next loop it would be

21 etc..... I've tried several different combination but I just don't

get it. Any help re-writing the vba to get this working is very

appreciated!

bobh.



Sub PrintPages()

'

Dim PRange As String

Dim PgCount As Integer, PosStart As Integer, PosAcum As Integer,

PgPrt As Integer



'input looks like this --> 3,8,19,21,36

PRange = InputBox("Enter up to 5 page nbr's to print, seperated by

a comma( , )", "Enter Nbrs")



'determine number of pages entered by counting how many commas there

are and add 1 to it

PgCount = intStringCount(PRange, ",") + 1

If PgCount = 0 Then Exit Sub



'print each page

PosStart = 1

PosAcum = InStr(PosStart, PRange, ",")

PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))

Do Until PgCount = 1

ActiveWindow.SelectedSheets.PrintOut From:=PgPrt, To:=PgPrt

PosAcum = InStr(PosStart, PRange, ",")

PosStart = PosStart + PosAcum

PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))

PgCount = PgCount - 1

Loop



End Sub
Click to expand...

Are you trying to print WORKSHEET 3,8 Etc or
pages on a certain worksheet?
 
D

Don Guillett

Hi All,



I've been trying to write a macro in Excel 2003 that will ask for 'up

to' 5 page numbers to print with the following vba but I'm not have

any success with the 'Do Until' loop section. The first time into the

loop the correct value is in PgPrt which is 3 if I were to enter the 5
Click to expand...
numbers in the comment below. But then after the
ActiveWindow.SelectedSheets.etc.... statement the values for the next

3 line are not correct. The value for PgPrt the first time thru would

be 8 then the next loop it would be 19 then the next loop it would be

21 etc..... I've tried several different combination but I just don't

get it. Any help re-writing the vba to get this working is very

appreciated!

bobh.



Sub PrintPages()

'

Dim PRange As String

Dim PgCount As Integer, PosStart As Integer, PosAcum As Integer,

PgPrt As Integer



'input looks like this --> 3,8,19,21,36

PRange = InputBox("Enter up to 5 page nbr's to print, seperated by

a comma( , )", "Enter Nbrs")



'determine number of pages entered by counting how many commas there

are and add 1 to it

PgCount = intStringCount(PRange, ",") + 1

If PgCount = 0 Then Exit Sub



'print each page

PosStart = 1

PosAcum = InStr(PosStart, PRange, ",")

PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))

Do Until PgCount = 1

ActiveWindow.SelectedSheets.PrintOut From:=PgPrt, To:=PgPrt

PosAcum = InStr(PosStart, PRange, ",")

PosStart = PosStart + PosAcum

PgPrt = Mid(PRange, PosStart, InStr(PosStart, PRange, ","))

PgCount = PgCount - 1

Loop



End Sub
Click to expand...

http://tinyurl.com/8cj663v
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with array 5
VBA Coding Help for Beginner 0
VBA Export to PDF 0
Problem with macro running from rule 3
error in code: capitalize first letter of a word 6
Memo Fields, Quotes and ReadLine 1
Pivot table consolidation with variable number of worksheets 0
VBA for Dependents shortcut menu 0

Top