How Do you send results of calc to seperate rows of answers to then later create chart

[greilly]

I have a simple spreadsheet that I enter a single value, (input). It
then performs calculations and displays three results, (x), (y), and
(z). I would like to copy these results into an area of cells of
results as Row1. Then enter next input value and obtain three new
results which I want to send over to the table as Row2.

For Next Value->Calculate Results x1,y1 & z1->Record x, y and z to
table Row1, then Row2(x2, y2 and z2), Row3(x3, y3 and z3), etc...-each
row has results of the for next (input1), (input2) and (input3).

Any help would be appreciated!

~G

 

[Dave Peterson]

How about this.

Create two worksheets--one the calc sheet and one the input sheet.

On the input sheet, put headers in A11
And then put the values you want in A2:A999 (or whatever).

Then after you enter all the values, you can run a macro that copies the input
value in column A and retrieves the output values and puts them in B.

You'll have to change this to use the right cells on the calculation sheet:

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
.Range("a1").Value = myCell.Value
Application.Calculate
myCell.Offset(0, 1).Value = .Range("b1").Value
myCell.Offset(0, 2).Value = .Range("c1").Value
myCell.Offset(0, 3).Value = .Range("d1").Value
Next myCell
End With

End Sub

I plopped the input cell into A1 and picked up from B1, C1 and D1. That can't
be a good guess!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[greilly]

How about this.

Create two worksheets--one the calc sheet and one the input sheet.

Does this mean one .xls file with Sheet1(calc) and Sheet2(input)? or
two seperate .xls files?

 

[greilly]

Thanks Dave,

I figured it out and it works like a charm.

Thanks Again!!

~George Reilly

 

[Dave Peterson]

Just in case...

I'd use two worksheets in the same workbook--just because I like it that way.

But you could modify these lines:

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

to point at any worksheet in any open workbook...

Set InputWks = workbooks("book99.xls").Worksheets("sheet1")
Set CalcWks = workbooks("book888.xls").Worksheets("sheet2")