Newbie VBA Questions - Print Loop, Incrementing Serial Nos.

[Alriemer]

Hello all,

I appologize for the basic nature of this question but didn't turn up much that I understoop in search of the board or VBA Help.

Task at hand: automatically print set of cash receipts with serial numbers, given number of copies to print; save and close worksheet.

Sheet1 consists of 4 cash receipts (2 printable pages) with serial numbers - say 001 through 004. Macro should get next serial number (005 - calulated in a cell named New_Serial_No) and put it in a cell named First_Serial_No; calculate and print; repeat until completing a given number of copies (value entered in cell named Number_To_Print); save and close.

Here is code - crashes when executed with error "Object doesn't support this property or method".
===================

Dim Counter As Integer
Dim Number_To_Print As Integer
Dim New_Serial_No As Integer
Dim MyCell As Range

Private Sub PrintLoop()
MyCell = ActiveSheet.ActiveCell
Number_To_Print = Range("Sheet1!Number_To_Print").Value
Counter = 0
Do
With Worksheets("Sheet1")
New_Serial_No = .Range("New_Serial_No").Value
.Range("First_Serial_No") = New_Serial_No
.Calculate
.Print
Counter = Counter + 1
End With
Loop While Counter < Number_To_Print

Range(MyCell).Select
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
===============

Any suggestions/corrections much appreciated.
Regards,
alriemer

 

[Andrew Poulsom]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

If you dimension MyCell as a Range object, you need to use the Set keyword in the assignment expression:

Set MyCell = ActiveSheet.ActiveCell

Then you can use:

MyCell.Select

 

[Alriemer]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Andrew,

Thanks for the tip - what dimension was VBA expecting, given my prior syntax?

Still crashing,
alriemer

 

[Andrew Poulsom]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

The default property of the Range object (ActiveCell) is Value, so MyCell could be a String.

On which line is your code failing?

 

[Alriemer]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Apparently fails on every line... I can't Run to Cursor at all. No matter where I try to run, from VBA Editor or Excel, I get runtime error 438. Sorry I can't be more specific. I did make some changes this morning:

Set MyCell = ActiveSheet.ActiveCell
Number_To_Print = Range("Sheet1!Number_To_Print").Value
Counter = 0
Do
Do While Counter < Number_To_Print
With Worksheets("Sheet1")
New_Serial_No = .Range("New_Serial_No").Value
.Range("First_Serial_No") = New_Serial_No
.Calculate
.Print
Counter = Counter + 1
End With
Loop
Loop Until Counter = Number_To_Print

 

[Andrew Poulsom]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Sorry, I didn't notice - ActiveCell is a property of the Application and Window objects, but not of a Sheet object. So try just:

Set MyCell = ActiveCell

 

[Alriemer]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Andrew - Thanks for the help. I'm really new at this, so can't tell what's broken at all. Made the change you suggested but still get RTE 438.
regards,
alriemer

 

[Juan Pablo González]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

In what line is the error happening ?

 

[Andrew Poulsom]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Andrew - Thanks for the help. I'm really new at this, so can't tell what's broken at all. Made the change you suggested but still get RTE 438.
regards,
alriemer

I got error 438 on this line:

Set MyCell = ActiveSheet.ActiveCell

which was highlighted yellow in the VBE. So I changed it to:

Set MyCell = ActiveCell

and everything was fine.

 

[Alriemer]

Re: Newbie VBA Questions - Print Loop, Incrementing Serial N

Hmm. Maybe I'm using debugging tools wrong.

The problem seems to come at

.Print

Steps up to that point appear to execute. When I set Number_To_Print > 1, to make it loop, it fails at the line

With Worksheets("Sheet1")

thanks,
alriemer