Loop through a range of cells, inputting data into other cells (drop-down selections) and return the result of calculations

0
votes

I have a calculator built in Excel 2003 in which you select options from a drop-down menu, data is then gathered and calculated returning your total cost to produce that package of items. What I'd like is to cycle through all the combinations of this drop-down and automatically return the total unit cost to another cell.

I have already created a massive grid with all possible drop-down selections entered in a separate worksheet. Here is an example of one iteration of this, but obviously this needs to loop and the cell references need to offset with the move.

  • Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for G1 of the worksheet entitled "Costs"
  • Cell value for E9 of the worksheet entitled "Cost Calculator" needs to = the cell value for A2 of the worksheet entitled "Costs"
  • Cell value for E10 of the worksheet entitled "Cost Calculator" needs to = the cell value for B2 of the worksheet entitled "Costs"
  • Cell value for E11 of the worksheet entitled "Cost Calculator" needs to = the cell value for C2 of the worksheet entitled "Costs"
  • Cell value for E12 of the worksheet entitled "Cost Calculator" needs to = the cell value for D2 of the worksheet entitled "Costs"
  • Cell value for E13 of the worksheet entitled "Cost calculator" needs to = the cell value for E2 of the worksheet entitled "Costs"
  • Then the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into G2 of the worksheet entitled "Costs".

Then the exact same thing needs to occur with 2 changes.

  • Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for H1 of the worksheet entitled "Costs" (offset by one column)

and the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into H2 of the worksheet entitled "Costs" again offset by one column.

This needs to occur 21 times (including the initial time) with the same two offsets.

I'd be happy if that were the case and I can just run the macro for each row, but if it could also cycle down a row and continue running until it hit a blank cell that would be ideal. If that were to occur, after the 21 times across it needs to move down a row and would be the following:

  • Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for G1 of the worksheet entitled "Costs" (will always be in row 1 as this is the quantity)
  • Cell value for E9 of the worksheet entitled "Cost Calculator" needs to = the cell value for A3 of the worksheet entitled "Costs"
  • Cell value for E10 of the worksheet entitled "Cost Calculator" needs to = the cell value for B3 of the worksheet entitled "Costs"
  • Cell value for E11 of the worksheet entitled "Cost Calculator" needs to = the cell value for C3 of the worksheet entitled "Costs"
  • Cell value for E12 of the worksheet entitled "Cost Calculator" needs to = the cell value for D3 of the worksheet entitled "Costs"
  • Cell value for E13 of the worksheet entitled "Cost calculator" needs to = the cell value for E3 of the worksheet entitled "Costs"
  • Then the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into G3 of the worksheet entitled "Costs".

Then the same offset as before 21 times and loop.

I'm not even sure if this is possible with Excel, but I figure if it is someone out here will be able to assist. Please let me know if you have any questions for what I am asking, I tried to be as descriptive as possible. Hopefully the idea is conveyed and if I need to reformat the layout of my sheets to accommodate the code that can be done as well.

1
vbaloopsexcelexcel-2003

1 Answers

0
votes

I'm not certain this is exactly what you are looking for, I am being thrown by the

Then the same offset as before 21 times. And loop.

I created some code that will do the following

Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for G1 of the worksheet entitled "Costs" Cell value for E9 of the worksheet entitled "Cost Calculator" needs to = the cell value for A2 of the worksheet entitled "Costs" Cell value for E10 of the worksheet entitled "Cost Calculator" needs to = the cell value for B2 of the worksheet entitled "Costs" Cell value for E11 of the worksheet entitled "Cost Calculator" needs to = the cell value for C2 of the worksheet entitled "Costs" Cell value for E12 of the worksheet entitled "Cost Calculator" needs to = the cell value for D2 of the worksheet entitled "Costs" Cell value for E13 of the worksheet entitled "Cost calculator" needs to = the cell value for E2 of the worksheet entitled "Costs" Then the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into G2 of the worksheet entitled "Costs".

Then the exact same thing needs to occur with 2 changes. Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for H1 of the worksheet entitled "Costs" (offset by one column) and the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into H2 of the worksheet entitled "Costs" again offset by one column.

Sub Calc_Loop()
Dim lngRow As Long
Dim intOS As Integer
Dim intCol As Integer

  Sheets("Cost Calculator").Select
  lngRow = 2
  Do
    For intOS = 1 To 5
      'Set Cells E9 - E13 = to Cells A-E on row lngRow on Sheet Costs
      Cells(8 + intOS, 5) = Sheets("Costs").Cells(lngRow, intOS)
    Next intOS
    'Set E8 to G1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 7)
    'Set G on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 7) = Cells(22, 5)

    'Set E8 to H1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 8)
    'Set H on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 8) = Cells(22, 5)

    lngRow = lngRow + 1
  Loop Until IsEmpty(Sheets("Costs").Cells(lngRow, 1))
End Sub

So this will loop through the Sheet "Costs" and:
* insert G2 for the calculated output from A2 - E2 with G1 and
* insert H2 for the calculated output from A2 - E2 with H1
* insert G3 for the calculated output from A3 - E3 with G1 and
* insert H3 for the calculated output from A3 - E3 with H1 ...

Is this what you need?