I am using this function

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range
Sheets("Factuur").Select
Range("a21").Select
rijnummer = ActiveCell.Row
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select

If IsNumeric(ActiveCell.Value) Then
rijnummer = ActiveCell.Row + 1
End If
Loop

I have no clue but nor rage("a21") nor activecell.offset seems to work.

Has anyone a idear what i am doin wrong?

Thanks Hans

Hi
first: no need for using select statements in such a case.
Second: Could you explain what you want to achieve with this?

Maybe try:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range

with Sheets("Factuur")
rijnummer = 21
Do While Not IsEmpty(.cells(rijnummer,1).value)
If IsNumeric(.cells(rijnummer,1).value) Then
rijnummer = rijnummer + 1
End If
Loop
end with

--
Regards
Frank Kabel
Frankfurt, Germany

"hans" schrieb im Newsbeitrag
...
I am using this function

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range
Sheets("Factuur").Select
Range("a21").Select
rijnummer = ActiveCell.Row
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select

If IsNumeric(ActiveCell.Value) Then
rijnummer = ActiveCell.Row + 1
End If
Loop

I have no clue but nor rage("a21") nor activecell.offset seems to
work.

Has anyone a idear what i am doin wrong?

Thanks Hans

I need to make invoices.
The touble is that the invoices wil be longer as one a4.
So i neetd to spli the invoice in several pages.
The last line of page one of the bill has to show the subtotal from that
page.
on the next page this subtotal has to apair on the first line (this is row
20)
On the last page i have to put the total off the bill.

What i am trying is to split the bill in sections of 40 rows ad the last row
of the page (this shows the subtotal).

ps the first 20 lines are the top lines of the bill.

Greetings Hans
"Frank Kabel" schreef in bericht
...
Hi
first: no need for using select statements in such a case.
Second: Could you explain what you want to achieve with this?

Maybe try:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range

with Sheets("Factuur")
rijnummer = 21
Do While Not IsEmpty(.cells(rijnummer,1).value)
If IsNumeric(.cells(rijnummer,1).value) Then
rijnummer = rijnummer + 1
End If
Loop
end with

--
Regards
Frank Kabel
Frankfurt, Germany

"hans" schrieb im Newsbeitrag
...
I am using this function

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range
Sheets("Factuur").Select
Range("a21").Select
rijnummer = ActiveCell.Row
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select

If IsNumeric(ActiveCell.Value) Then
rijnummer = ActiveCell.Row + 1
End If
Loop

I have no clue but nor rage("a21") nor activecell.offset seems to
work.

Has anyone a idear what i am doin wrong?

Thanks Hans

Hi Hans,
As Frank indicated you do not need to change the selection
and stating a purpose might help getting the answer you want.

But I think what you were doing wrong was switching
row and column in the OFFSET(row,column)
ActiveCell.Offset(0, 1).Select
I think you wanted to select a cell on the next row, not the
next column, in which case:
ActiveCell.Offset(1,0).Select
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"hans" wrotebl...
Has anyone an idea of what I am doing wrong?

yes this was wrong but my selected cell dit not move.

this is /was the problem

greetings hans

"David McRitchie" schreef in bericht
...
Hi Hans,
As Frank indicated you do not need to change the selection
and stating a purpose might help getting the answer you want.

But I think what you were doing wrong was switching
row and column in the OFFSET(row,column)
ActiveCell.Offset(0, 1).Select
I think you wanted to select a cell on the next row, not the
next column, in which case:
ActiveCell.Offset(1,0).Select
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"hans" wrotebl...
Has anyone an idea of what I am doing wrong?

Hans,

I think you should close you while loop with 'Wend' and not 'Loop'. I'm not
sure though. It has been a while since I used a while loop in VBA.

"hans" wrote:

yes this was wrong but my selected cell dit not move.

this is /was the problem

greetings hans

"David McRitchie" schreef in bericht
...
Hi Hans,
As Frank indicated you do not need to change the selection
and stating a purpose might help getting the answer you want.

But I think what you were doing wrong was switching
row and column in the OFFSET(row,column)
ActiveCell.Offset(0, 1).Select
I think you wanted to select a cell on the next row, not the
next column, in which case:
ActiveCell.Offset(1,0).Select
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"hans" wrotebl...
Has anyone an idea of what I am doing wrong?

I haven't seen the code you refer to, but Wend is used with While. Loop is
used when the top line is Do. i.e. it's While/Wend or Do/Loop. If the code
compiled, this was not the problem.


On Fri, 19 Nov 2004 13:21:02 -0800, "Jared"
wrote:

Hans,

I think you should close you while loop with 'Wend' and not 'Loop'. I'm not
sure though. It has been a while since I used a while loop in VBA.

"hans" wrote:

yes this was wrong but my selected cell dit not move.

this is /was the problem

greetings hans

"David McRitchie" schreef in bericht
...
Hi Hans,
As Frank indicated you do not need to change the selection
and stating a purpose might help getting the answer you want.

But I think what you were doing wrong was switching
row and column in the OFFSET(row,column)
ActiveCell.Offset(0, 1).Select
I think you wanted to select a cell on the next row, not the
next column, in which case:
ActiveCell.Offset(1,0).Select
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"hans" wrotebl...
Has anyone an idea of what I am doing wrong?

use activecell.offset(1,0).activate as opposed to .select

"hans" wrote:

I am using this function

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AllCells As Range
Dim rng As Range
Sheets("Factuur").Select
Range("a21").Select
rijnummer = ActiveCell.Row
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select

If IsNumeric(ActiveCell.Value) Then
rijnummer = ActiveCell.Row + 1
End If
Loop

I have no clue but nor rage("a21") nor activecell.offset seems to work.

Has anyone a idear what i am doin wrong?

Thanks Hans