VB in excel macros

[Dave C]

I have a macro that prints reports 1 to 48.

Sub ADCHART()
For X = 1 To 48
Range("keyval").Value = X
Calculate
Calculate
Calculate
ActiveSheet.PrintOut copies:=1
Next

End Sub

I would like to use this type of macro to print beyond 48, however, the reports
are not in a continuous range. The numbers are between 2002 and 999999,
but only 550 reports exist and I do not want to print all those blank pages.


I recorded a macro using excel but it is so large, i had to split it in two.
Here is a sample:

Range("B63").Select
ActiveCell.FormulaR1C1 = "2002"
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("B63").Select
ActiveCell.FormulaR1C1 = "2034"
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("B63").Select
ActiveCell.FormulaR1C1 = "2061"
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("B63").Select
ActiveCell.FormulaR1C1 = "2062"
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("B63").Select
ActiveCell.FormulaR1C1 = "22092"
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1


As you can see, there is a lot of code for such a simple job. is there any
way to shrink this code into something like the original macro?

[Arthur Wood]

Dave,

If you know before hand what the 550 vlaues are, then create an array
to hold the values

Dim myValues(550) as Integer

then you can set each of the array elements to the correct value, and then
loop through the array just as you are already looping through the value
1 to 48:

For X = 1 To 550
Range("B63").Select
ActiveCell.FormulaR1C1 = myArray(X)
Range("B64").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next


that should simplify things greatly.

Arthur Wood



"Dave C" <djcourtright@yahoo.com> wrote:
>
>I have a macro that prints reports 1 to 48.
>
>Sub ADCHART()
> For X = 1 To 48
> Range("keyval").Value = X
> Calculate
> Calculate
> Calculate
> ActiveSheet.PrintOut copies:=1
> Next
>
>End Sub
>
>I would like to use this type of macro to print beyond 48, however, the
reports
>are not in a continuous range. The numbers are between 2002 and 999999,
>but only 550 reports exist and I do not want to print all those blank pages.
>
>
>I recorded a macro using excel but it is so large, i had to split it in
two.
> Here is a sample:
>
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2002"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2034"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2061"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2062"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "22092"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>
>
>As you can see, there is a lot of code for such a simple job. is there
any
>way to shrink this code into something like the original macro?

[Craig Brown]

Dave,

First, I would use Arthur's suggestion, below. It's simple, and, more importantly,
easy to follow.

If your list of 550 values is at all dynamic, i.e. maybe 550 today but 549
tomorrow, maybe a different set of values in the 550, etc. I would put the
values in a .txt file and read it in at start-up. That way you can modify
the list without modifying the program.

For X = 1 To UBound(TheListArray)
....See Arthur's Code below
Next X

Now your program/macro is "stupid", it doesn't know or care how many or what
the values are, it just processes them.

Craig Brown



"Arthur Wood" <wooda@saic-trsc.com> wrote:
>
>Dave,
>
> If you know before hand what the 550 vlaues are, then create an array
>to hold the values
>
> Dim myValues(550) as Integer
>
>then you can set each of the array elements to the correct value, and then
>loop through the array just as you are already looping through the value
>1 to 48:
>
> For X = 1 To 550
> Range("B63").Select
> ActiveCell.FormulaR1C1 = myArray(X)
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Next
>
>
>that should simplify things greatly.
>
>Arthur Wood
>
>
>
>"Dave C" <djcourtright@yahoo.com> wrote:
>>
>>I have a macro that prints reports 1 to 48.
>>
>>Sub ADCHART()
>> For X = 1 To 48
>> Range("keyval").Value = X
>> Calculate
>> Calculate
>> Calculate
>> ActiveSheet.PrintOut copies:=1
>> Next
>>
>>End Sub
>>
>>I would like to use this type of macro to print beyond 48, however, the
>reports
>>are not in a continuous range. The numbers are between 2002 and 999999,
>>but only 550 reports exist and I do not want to print all those blank pages.
>>
>>
>>I recorded a macro using excel but it is so large, i had to split it in
>two.
>> Here is a sample:
>>
>> Range("B63").Select
>> ActiveCell.FormulaR1C1 = "2002"
>> Range("B64").Select
>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>> Range("B63").Select
>> ActiveCell.FormulaR1C1 = "2034"
>> Range("B64").Select
>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>> Range("B63").Select
>> ActiveCell.FormulaR1C1 = "2061"
>> Range("B64").Select
>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>> Range("B63").Select
>> ActiveCell.FormulaR1C1 = "2062"
>> Range("B64").Select
>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>> Range("B63").Select
>> ActiveCell.FormulaR1C1 = "22092"
>> Range("B64").Select
>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>>
>>
>>As you can see, there is a lot of code for such a simple job. is there
>any
>>way to shrink this code into something like the original macro?
>

[Peter Higgns]

I find it unclear from your explanation just what you are printing. I presume
it is the same sheet using different sets of starting data. You could place
the numbers in another sheet and determine the end using cells.currentregion.specialcells(xlcelltypelastcell)
or use the function below,

Public Function GetLastRow() As Long
GetLastRow = ActiveSheet.Cells.CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count
End Function


or you could build a collection, or variant array(shown) and use for next
AnArray = array(2002,2034, 2061,... )
for each VItem in AnArray

Hope this helps

"Dave C" <djcourtright@yahoo.com> wrote:
>
>I have a macro that prints reports 1 to 48.
>
>Sub ADCHART()
> For X = 1 To 48
> Range("keyval").Value = X
> Calculate
> Calculate
> Calculate
> ActiveSheet.PrintOut copies:=1
> Next
>
>End Sub
>
>I would like to use this type of macro to print beyond 48, however, the
reports
>are not in a continuous range. The numbers are between 2002 and 999999,
>but only 550 reports exist and I do not want to print all those blank pages.
>
>
>I recorded a macro using excel but it is so large, i had to split it in
two.
> Here is a sample:
>
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2002"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2034"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2061"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "2062"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
> Range("B63").Select
> ActiveCell.FormulaR1C1 = "22092"
> Range("B64").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>
>
>As you can see, there is a lot of code for such a simple job. is there
any
>way to shrink this code into something like the original macro?