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.

 

[Ropes4u]

How long does it take to calculate once the new value is input? Can you paste the values from one list into the worksheet?

 

[kciz]

It takes 5-10 seconds for it to calculate for every value thats input. The values can be pasted from the list onto the worksheet, just so they're on the same sheet, but all the values cannot be analyzed simultaneously. There is only one field for inputting data and one field for the response.

 

[shg]

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.

There is only one field for inputting data and one field for the response.

Are there six inputs associated with each calculation, or one?

If the latter, data tables were invented for you.

 

[MrKowz]

kciz,

Can you please provide an example of how your list of data is laid out? We can accomplish this through a macro that will pull in each group of data, paste into those input cells, copy the output cells, and put them into a new worksheet.

Also, if you can provide the sheet name of the input page and the sheet name of the list you need to enter in, that would be most beneficial.

Cheers

 

[kciz]

shg, there are 5 numbers that are input for every calculation. When I said there was 1 field for inputting data I meant the group of 5 cells. In my first post, I was calling each group of 5 numbers a "data set", and the table to which I'm referring to contains hundreds of these data sets stacked vertically.

MrKowz, The list of data is laid out vertically, such that cells A2:E2 contain the first data set, A3:E3 the second, and so on. The order that the numbers are arranged column-wise is the same in list as they are input into the calculator (values in column A go to cell A6 in the calculator, column B data into in B6 of the calculator, etc.) Unfortunately, I don't have the sheet names in front of me, but you can use "data" for the data list sheet name and "calculator" for the input page.

Thank you all for all the help.

 

[MrKowz]

Try out this macro - it will create another sheet called "Final List" at the end of your worksheets to put all the results into. If by chance it doesn't properly calculate, remove the comment indicator on the Application.Calculate line, delete the sheet "Final List", and run it again.

Public Sub kciz()
Dim i       As Long, _
    LR      As Long, _
    sws     As Worksheet, _
    cws     As worskheet, _
    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
    sws.Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=cws.Range("A6")
    'Application.Calculate
    cws.Range("F6:I6").Copy Destination:=dws.Range("A" & rowx)
Next i
Application.ScreenUpdating = True

End Sub

 

[kciz]

I get the following error when I try to run it:

Compile error:
User-defined type not defined

Then it highlights the "Public Sub kciz()" line

 

[MrKowz]

There was a typo in there (Since I couldn't test it, I wasn't able to catch that).

Try:

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
    sws.Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=cws.Range("A6")
    'Application.Calculate
    cws.Range("F6:I6").Copy Destination:=dws.Range("A" & rowx)
Next i
Application.ScreenUpdating = True
End Sub

 

[kciz]

Well, now it says: Run-time error '1004': Method 'Range' of object '_Worksheet' failed. Then it highlights:

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

Any thoughts? Thanks again for your time.