Batch process a large number of values

[jim]

I hope I can get this right:

Sheet 1 has two cells for entering an x and a y value.

Sheets 2 through 6 have lots of big "complicated" formulas that use
the two values entered on sheet 1.

Back on sheet one is an answer cell that is compiled from various
answers on sheets 2 - 6:

=0&sheet2!H2&sheet2!H3&sheet3!F1&sheet4!F1&sheet5!F1&sheet6!F1

I've got another sheet (sheet 7) with many x and y values (columns A and B)
that I want to plug into the formulas on sheets 2 through 6 and return an
answer back on sheet 7 (column C).

Is there a way of batch processing all of this? Have I given enough info?
The
whole thing was written by someone else and I'm not as adept with Excel as
he is.

Thanks,

Jim

 

[Bob Phillips]

Jim,

I think I understand, so I'll give it a shot.

I am making the following assumptions, so if they are wrong, you will need
to adjust the code to suit
- the x and y values in Sheet1 are in A1 and B1
- the answer cell on Sheet1 is in C1
- the results will go in D1n

Here we go

Sub BatchValues()
Dim cRows As Long
Dim i As Long, j As Long
Dim oWSValues As Worksheet

Set oWSValues = Worksheets("Sheet7")
cRows = oWSValues.Cells(Rows.Count, "A").End(xlUp).row
With Worksheets("Sheet1")
For i = 1 To cRows
.Range("A1").Value = oWSValues.Cells(i, 1)
.Range("B1").Value = oWSValues.Cells(i, 2)
.Cells(i, "D").Value = .Range("C1")
Next i
End With
End Sub