Newbie with problem (duh!)

Hi Guys & Gals,

Why doesn't this work?

ActiveSheet.PageSetup.PrintArea = "$a$1:$z$"&CEILING(COUNTA(D3:D42)/8;1)*46

This is only one line of a macro and the problem lies in the range-defining part. I'm totally new to VB, so the problem's gotta be in the syntax...

Thanks for any feedback

Yes, it's a formula. It checks how many fields have values in them on one worksheet, and for every 8 values it should increase the printarea on another worksheet by 46 rows.
So, the formula looks for the number of values in (d3:d42) and divides by 8 and rounds up to the nearest integer to get the multiplier, so that the printarea would be :

for 8 values : $a$1:$z$46
for 16 values : $a$1:$z$92
for 24 values : $a$1:$z$138

Since the printarea in the VB line is between "", I thought it regarded it as text, so I substituted the rownumber by my formula, as you would in an INDIRECT-formula...

I also realise now I should have "pointed" to the other worksheet, because now it doesn't know it has to look there for the values...

This is the original macro, that I "connected" to a button :

Sub Print_Click()

Sheets("Page1").Select
Range("A1:Z46").Select
ActiveSheet.PageSetup.PrintArea = "$a$1:$z$46"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

End Sub

Ok, tried something different :

Private Sub CommandButton1_Click()
Dim MyValue As Integer
Sheets("Blad1").Select
MyValue = Range("ab1").Value
Range("$A$1:$Z$" & MyValue).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$Z$" & MyValue
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Registers").Select
End Sub

So I let the spreadsheet take care of the formula in cell AB1, which returns an integer, i.e. multiples of 46. The button is placed on sheet "Register", what has to be printed is located on sheet "Blad1". As I see it, all this is pretty straightforward... So why does it give me an error in line "Range("$A$1:$Z$" & MyValue).Select"? For a value of 46 in AB1, this should return "Range("$A$1:$Z$46"), no?

Thanks for any feedback