help with vba loop

[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

 

[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

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

 

[bobh]

Are you trying to print WORKSHEET 3,8 Etc or
pages on a certain worksheet?- Hide quoted text -

- Show quoted text -

pages on a worksheet........

 

[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

http://tinyurl.com/8cj663v