Perform iterative calcs over entire table

[kciz]

Hello,

I'm a bit stumped on this one, maybe somebody can help. I have a worksheet that contains a very complex iterative scheme. The user has to enter data values in cells A6:E6 and, after entering this data, the worksheet calculates terms that are reported in cells F6:I6. This works great if the user just has one or two data sets to analyze. However, I've now been given a huge list of data that needs analysis. Is there any way to write a marco that will query values form this list, enter them into this worksheet one-by-one, and compile another list of the calculated values? I cannot simply copy/paste the calculations down this entire list, it is a large iterative scheme that takes a long time to set up if a copy were to be made.

Any help in which direction to look would be greatly appreciated. Thanks in advance.

 

[shg]

Absent qualification, Cells refers to cells on the active worksheet. So

Range(sws.Cells(i, 1), sws.Cells(i, 5)).Copy Destination:=cws.Range("A6")

 

[kciz]

Now it doesn't error out, but it only fills in cells A2:D2 instead of compiling a list. The cells also don't contain numbers, it just copied fomulas from the "calculator" sheet, which of course won't work.

 

[MrKowz]

Public Sub kciz()
Dim i       As Long, _
    LR      As Long, _
    sws     As Worksheet, _
    cws     As Worksheet, _
    dws     As Worksheet, _
    rowx    As Long
    
    
Set sws = Sheets("Data")
Set cws = Sheets("Calculator")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Final List"
Set dws = ActiveSheet
LR = sws.Range("A" & rows.Count).End(xlUp).row
rowx = 2
Application.ScreenUpdating = False
For i = 2 To LR
    Application.StatusBar = "Currently on calculation " & i & " of " & LR
    Range(sws.Cells(i, 1), sws.Cells(i, 5)).Copy Destination:=cws.Range("A6")
    'Application.Calculate
    cws.Range("F6:I6").Copy
    dws.Range("A" & rowx).PasteSpecial Paste:=xlPasteValues
    rowx = rowx + 1
Next i
Application.ScreenUpdating = True
End Sub

 

[kciz]

That's it! Thanks a lot, this will really come in handy.