copying & pasting multiple values from one sheet to another

[phdog]

Hi All

If anybody could help me with the following problem i would really appreciate it, ive have no idea how to go about it...

I have one fairly complex sheet which calculates a cost based on 2 input values.

What i want to do is change the input values that the sheet recieves to cover all the possible combinations of the inputs, while at the same time recording the cost that is calculated for each one of the combination's. (The 2 input values range from 1-100 and 1-40 respectively)

I want to use VB to do this all at the click of a button if possible...

I would imagine that it would be some sort of loop function going through all possible combination's and dumping the calculated value in another sheet.

please could somebody help me, as ive been trying to achieve it through recording a macro, and other methods but nothing seems to work, or i just have not a clue how to do it....

Phil

[karan]

Can you attach a sample sheet?

--Karan--

[phdog]

Hi Karan Thanks for the reply..

I have now attached the sheet... On the sheet 'C-H-HU' the inputs are in cells B5 & B6 and when they are changed a cost is reflected in B14. For each combination this cost will change. So for each cost i want it pasted to the matrix in 'sheet 1'.. is there a way to do this?

i want this so that i can show how the costs change over a range..ie:represent it graphically...

thanks phil

[EseKuent]

Sorry, can't open your file...

What about this? As I understand that's mainly what you are looking for (taking into account I don't know what are you trying to calculate from this 2 inputs).

Sub test()
Offset = 2
Set ws = ThisWorkbook.Worksheets(1)
For n = 1 To 40
    For p = 1 To 100
        ws.Cells((n - 1) * 100 + p - 1 + Offset, 2).Value = n
        ws.Cells((n - 1) * 100 + p - 1 + Offset, 3).Value = p
        ws.Cells((n - 1) * 100 + p - 1 + Offset, 4).Formula = "=RC[-1]*RC[-2]"
    Next p
Next n
End Sub