How to reduce use of "Select &/or Activate" to

[ortizSr]

To: Richie(UK)

Here is a sample of the many macros I use in a project.

Reduction of the "Select &/or Activate" coding would be appreciated, and act as a worthy lesson for improving other codes that I have in the Project..

It purpose is to provide the next suffix to an Invoice number consisting of the (Month-Year-Next Record No).

May I further add that I had to resort to a Mickey-mouse technique in setting the first record no in the Database. I did not know how to avoid going to the bottom of the Worksheet when the database is empty.

How can I modify this, so that I could start with any number, and not '0001??

Sub NextRecordNo()

'Summary: To Set a Record No for each Invoice in the
' Invoice Database, and to Set the Suffix for the
' Next Invoice Number

'Step 1 is to position all sheets to A1

Call ToFirstCellAllSheets

'Step 2 To insure that the first Record No is in place.

Sheets("Invoice Database").Select
Selection.End(xlToRight).Select
Range("HP2").Select
ActiveCell.FormulaR1C1 = "'0001"
Range("A1").Select

'Step 3 To create the Next Record No in
' the Invoice Database,

Sheets("Invoice Database").Select
Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
ActiveCell.Range("A1:A2").Select
Selection.DataSeries Rowcol:=xlColumns, _
Type:=xlAutoFill, Date:=xlDay, _
Trend:=False
Selection.End(xlDown).Select
Selection.Copy

'Step 4 Post new Record No to the Invoice Master
' Cell N4

Sheets("Invoice Master").Select
ActiveCell.Offset(2, 13).Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Invoice Database").Select
Application.CutCopyMode = False
Range("A1").Select

End Sub

 

[jdavis9]

Re: How to reduce use of "Select &/or Activate&quot

Well, you have a lot of jumping around going on in there.....lol

Heres a couple of sugestions.

If you know the cell value just use
range("N4").value = yourvalue

If you want to do something if a cell value is blank, then you can always use

if activecell.value = vbnullstring then
your code.

or

if range("a1").value = vbnullstring then
your code.

It also appears you are trying to number the invoices/database from the last number.

If you have a master list of used invoice numbers then
at the top of the number list of invoices put the formula =max(yourrange)
you can always refer to that location as your last number.

then when you need the next available number
nextnum = (range ref you used above) + 1
use that value for the next invoice.

if your invoice number is located in range("hp2") then

your code is then
range("HP2").value = nextnum
you will also need to update your masterlist at the end with nextnum

 

[ortizSr]

Re: How to reduce use of "Select &/or Activate&quot

Dear jdavis9:

My question was in response to Richie(UK) comment, and I quote it below:

Consider the following two simplistic examples:

code:
--------------------------------------------------------------------------------
Sub test1()
Worksheets("Sheet3").Select
Range("A10").Activate
ActiveCell.Value = 123
End Sub

Sub test2()
Worksheets("Sheet3").Range("A10").Value = 123
End Sub

--------------------------------------------------------------------------------

Both routines achieve the same objective, however the second routine achieves it without needing to Select or Activate the sheet or range objects. Generally this approach will make your code shorter and easier to read and, more importantly, faster to execute

.


I presented a sample of my code for getting the next record No. (which is part a part of the Invoice No -the suffix), so that he or anyone else could show me how to reduce the coding whereever Select &/or Activate is employed.

I also wanted to know if there is a way to enter the first Record No (whatever Number is selected- I used '0001) other than the way I show. If I do not enter this Record No on the first entry of Invoices in the Database, then the macro will end up in Row 65,536 of the Column HP.

The Invoice No. assigned consists of:
The Month the invoice is generated
The Year the Invoice is issued, and
The Record number The Macro determines by going to the last Record No. and increasing by one.
In other words The Invoice No. appears as follows: (Month+Year+Record No)

Your comments are well received but I really wanted 2 things. 1. To save coding, and 2. to Find a better method for the issuance of the first Record No.

Thanks for your help.