Processing/Calculating Range Of Cells

I created a spreadsheet that contains a lot of formulas which use SQL to retrieve data. The spreadsheet is pulling data for manufacturing operations, each row represents an operation. Here are examples of the cell formula:
=IF(B5>0,SQL.REQUEST("DSN=...,"select top 1 customer.csname from orders... where job_number = " & C5),"")
=fnGetOpData("M",B2,B4,B5) <-- Operation Make Ready Time
=fnGetOpData("Q",B2,B4,B5) <-- Quantity to output
=fnGetOpData("R",B2,B4,B5) <-- Calculates Run Time

The 3 columns above require number crunching using VBA via a function - which performs a couple SQL selects. You can see the last 3 are the same function call with a change in the arguments.

Therefore, for each row the same function is executed 3 times - time consuming. (Of course, we have turned off auto calculate and the user performs a manual calculation.)

I would like to revise this to speed up the performance. I realize there's a technique to process each row of a range and store values into cells, but, have not found a resource that explains how to do this in terms that I can understand - there always seems to be an assumption about some aspect already being understood.

My gut feeling for the gist of this is: (1) define the range of cells, (2) create some iteration process, i.e. For i = {row i} thru {row n} ... Next, (3) perform a function (or other routine) that stores the results/calculations into the appropriate cells of the row.

Can anyone direct me to a resource, or, if it's short enough, provide the instructions?

Hello EdAROC,

Welcome to the Forum!

When asking questions you need to provide accurate details and examples about problem and the results you want. You have done neither. You clipped your code and we have no idea what the following code is...

Please Login or Register  to view this content.

Aside from your comments is this a SQL function or VBA user defined function? What version of SQL are you using? How is the data arranged on the worksheet? It would be best if you post the workbook with your next post. Reference the workbook cells, tables, etc. when referring to the VBA code or SQL code.

Sincerely,
Leith Ross

My apologies for what appears to have been a vague request.

My request is for guidance on the Excel VBA functionality to process a range of cells in order to improve performance - the spreadsheet works, it's just terribly slow because cells in columns B, F, G, M and N have a formulas that are either a SQL.REQUEST or a user defined function that performs a SQL request - hence many SQL connections created, then closed.

I think there is functionality so that the number of times the SQL connections can be reduced (to 1?), the calculations and SQL Selects reduced, and one final closing of the SQL connection. That is, I can define a range, rows 5 thru 85 and iterate thru each one. If there are values in columns C, D, and E then perform the SQL selects to retrieve data and perform calculations to drop into columns B, F, G, M and N.

A stripped down version of the file is uploaded. (Worksheets for other departments/operations have been removed as they are basically the same as the one on the spreadsheet).

In each row the user will enter a Job# (column C), Op (D), and Form (E). When calculations are performed there is:
1. A SQL.REQUEST in column B to retrieve the Customer Name.
2. A user defined function that performs a SQL Select to retrieve the Die# (column G).
3. A user defined function that executes 3 times to calculate and/or retrieve either the Quantity (column F), Make Ready time (column M), and RUN time (column N).

I hope I have provided enough information.
(Keith thanks for letting me know more was needed).

I'm getting closer, I was browsing online and found the key to my solution - the Range functionality.

What I gathered from what I read is the basics of the solution is a For ... Next loop, for each row on the worksheet and using the Range function.

My problem now is that I gleaned bits and pieces of the gist of what I need to do, but, can't get my arms around it - probably because I was absorbing a variety of things.

Any direction to a resource to read about using the Range function would help.

Found a resource that used a For...Next Loop and the Cells method of the Range object.

The solution that I'm putting together is to use the Cells() method to store the values in the appropriate cells.

What I still don't know is how to execute the VBA code, since the cells - Customer, Quantity, Die#, MR and RUN - won't contain formulas.

Here's the solution that comes to mind. First there are three "selects", one for the Customer name, one for the Die#, and one for the operation - Quantities, MR, and Run times.

To retrieve the Customer name:
1. Perform a For...Next loop of the Job# column and capture the minimum (min_job_no) and maximum Job# values, (i.e. min_job_number and max_job_number) - for any worksheet this will be a small range of the currently open Jobs.
2. Perform a select to retrieve the Job#, Customer name WHERE Job_Number between min_job_number and max_job_number.
3. For...Next loop for each row of the spreadsheet.
> Retrieve the Job# in column C, i.e. lngJobNumber = Cells(iRow,3)
> "Lookup" the selected record , i.e. rs.findfirst job_number = lngJobNumber
> Cells(iRow,2) = rs.fields(customername)

With this technique the number of connections and selects goes from many to 1 (plus moving between the selected records in the recordset).

To retrieve the Die#...
This is similar to the above, retrieving the Die# from two tables, then, the "lookup" checks if there's a Combo#, if there is use the Die# from that table, otherwise, use the Die# from the Specs table.

To retrieve the Operation Quantity, MR and RUN times...
Will still need to perform selects for each row, however, the number of goes from 3 for each row to only 1.

I think I got it.