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

  • Thread starter Thread starter greilly
  • Start date Start date
G

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
 
How about this.

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

On the input sheet, put headers in A1:D1
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:D.

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
 
How about this.

Create two worksheets--one the calc sheet and one the input sheet.
Click to expand...

Does this mean one .xls file with Sheet1(calc) and Sheet2(input)? or
two seperate .xls files?
 
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")
 
Back
Top