general way to run multiple values through another spreadsheet and record results

[TestableEmu263]

Hi everyone, I've been copy and pasting by hand a dataset into some spreadsheet, it making calculations, then copy and pasting the results back out into another spreadsheet. This is extremely tedious and I need a way to streamline this. If you could help, that would be greatly appreciated. Ill give a simple example and work off of that for my larger spreadsheet

A) say I have 3 datasets in column vectors in spreadsheet X:
1. (2,3)
2. (2,5)
3. (4,6)

B) on a different spreadsheet, spreadsheet Y, I have some equations to put these vectors though. for simplicity lets assume this spreadsheet adds 2 to the first value in the column vector, and then subtracts 2 to the second value in the column vector and returns some result. Lets also assume these equations are not cell-adjacent to each other in spreadsheet Y. For example, equation 1 could be in cell A2 and equation 2 could be in cell D5.

C) read these results from spreadsheet Y and return them in a 3rd spreadsheet, spreadsheet Z in a list. With the above examples this list should then report in row vectors:
1. (4,1)
2. (4,3)
3. (6,4)

If you have any questions please reach out. And again, I would greatly appreciate any help

 

[offthelip]

Try this, hopefully it will give you the way to do your worksheet:

Sub test()
Dim outarr()
With Worksheets("X")
lastsource = .Cells(Rows.Count, "A").End(xlUp).Row
src = .Range(.Cells(1, 1), .Cells(lastsource, 2))  ' pick up alll the input data
End With
ReDim outarr(1 To lastsource, 1 To 4)   ' dimension output array for each input row with two inputs  and two outputs
With Worksheets("Y")
For i = 2 To lastsource
  .Range(.Cells(2, 1), .Cells(2, 1)) = src(i, 1) ' put first variable into A2 on worksheet Y
  .Range(.Cells(4, 4), .Cells(4, 4)) = src(i, 2) ' put second variable into D4 on worksheet Y
 ' pick up the outputs ( you haven't said where they are so I have assume A3 and D5
  outarr(i, 1) = src(i, 1)  ' output the Input variables
  outarr(i, 2) = src(i, 2)
  outarr(i, 3) = .Range(.Cells(3, 1), .Cells(3, 1))  ' output the output variables
  outarr(i, 4) = .Range(.Cells(5, 4), .Cells(5, 4))
Next i
End With
outarr(1, 1) = " input 1"
outarr(1, 2) = " input 2"
outarr(1, 3) = " output 1"
outarr(1, 4) = " output 2"

With Worksheets("Z")
.Range(.Cells(1, 1), .Cells(lastsource, 4)) = outarr
End With

End Sub