Print many instances of a single chart in a single worksheet 3

[IJR]

I have a table whose y-values are determined from a small set of user input. I have created a graph for this table in the same Excel worksheet.

Now

I have at least 20 similar sets of input. Obviously I can enter them one after the other and PRINT

I dont want to do this

I want to store my input set in another worksheet. Call this a data sheet.

And put a button in my main worksheet to calculate the table and store them somewhere.

Finally add a PRINT button which will simply get the tables in memory one by one, create graph as if on the same worksheet, print the worksheet(graph) one after the other


I will appreciate basic hints and even a few basic lines of code.

Thanks,IJR

 

[Imagineer]

Just off the cuff, assuming this is a one-off activity, I'd suggest a macro. Setup your spread sheets. Turn the macro recorder on go through the steps you want it to do. Turn off the macro. Edit the macro to go to the next line of input by using a FOR-NEXT loop,

i.e.

Dim Found, MyObject, MyCollection
Found = False ' Initialize variable.
For Each MyObject In MyCollection 'Iterate through each
If MyObject.Text = "Hello" Then
Found = True ' Set Found to True.
Exit For ' Exit loop.
End If

'CHANGE VALUES
'DO CALCULATIONS
'PRINT GRAPH
Next


Hope this helps,



Imagineer

 

[Mala]

Imagineer has presented the correct mechanism, but I think IJR must be wanting something more specific. I suggest the following:
1. I presume the Input data sets are contained in a sheet named 'InputDat', arranged in rows (e.g. 20 rows of 5 parameters each) and the top left cell of this range (containing the first i.e. leftmost parameter in the first row) has been named 'InputCell1'.
2. The cells which calculate the y-values for the chart are named 'ChartInput'.
3. The idea is to refer to the values from the InputDat sheet to the ChartInput range one by one and print the chart for each input set.
4. Since I find this easier to do than to describe, I've spent a half-hour preparing a workbook to do your thing – this has been done in a generalized form. U can ask for this from mala_rs_singh@rediffmail.com.
Mala

 

[IJR]

Coool stuff Mala. Dont keep away from the forum

IJR

 

[yakpol]

Hi, IJR
I am used to be 4928...

In a fact you don't need to use VBA to accomplish this task. Instead you can use dynamic ranges and array formulas. It's easier to accomplish than to explain, it's very short and straight forward if you know how to name ranges using formulas and how to use the 'offset' function. See the solution at

http://yakpol.net/downloads

File name: DynamicChart.xls

Bye

 

[yakpol]

The previos post link to the site does not work, try it here

http://yakpol.net/downloads/