activecell.offset does not work

[hans]

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

 

[Frank Kabel]

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

 

[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

 

[David McRitchie]

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

 

[hans]

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

this is /was the problem

greetings hans

 

[Guest]

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.

 

[Myrna Larson]

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.

 

[Guest]

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