ansaurus

Question

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

Answer 1

A:

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?

Craig 2010-01-25 23:21:32

For what it's worth, you should be using .Value on all your cell references: `Cells(8,5).Value = Sheets("Costs").Cells(1,7).Value` and any time you refer to the same sheet over and over again, you should declare a variable and use that: `Dim Costs as Excel.Worksheet | Set Costs = ActiveWorkbook.Sheets("Costs")`. Last, please read about Hungarian Notation at http://www.joelonsoftware.com/articles/Wrong.html.

Emtucifor 2010-01-26 00:31:44

So I can understand the value of stating what about the cell you are changing, that is valid. I can also see the value of setting a variable for the Worksheet, however, I do have two questions: 1) Can you show him, and me, how to use it, is it Costs.Cells(1,7).Value? 2) Should the variable be called wsCosts in proper Apps Hungarian? Finally, I understand that in posting an answer I should be trying to propagate better coding, however, it will take some time to change 14 years of coding style, so some gentle reminders going forward would be fine.

Craig 2010-01-26 15:03:36

Yes, Costs.Cells ... is correct. What I find in my own coding (and this is *entirely* personal preference), is that eliminating prefixes most of the time makes my code more readable. Basically, what I got from reading about the "original use of Hungarian notation" was that it was a practice of *abbreviation*, not *prefixing* : for example, RelativeWindowTop became rwTop. Name your variables as descriptively as they need to be (and not one bit more), then abbreviate if there are overlong or repeated elements. In this case, I think Costs is just fine, because in context it won't be confusing.

Emtucifor 2010-01-27 18:52:25

Also, I should have used `ThisWorkbook.Sheets("Costs")` instead of `ActiveWorkbook` because that can run into problems sometimes.

Emtucifor 2010-01-27 18:53:32

ansaurus

tags:

views:

answers:

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

related questions