Macro - Loop/automatization problem
 

Hello,

This problem may occur very "basic", which I apologize for.
I try to make a macro to automatize a task (move data from one sheet to
another, return av value, and loop this process a number of times (several
hundred lines). The macro is listed below.

What is my problem is : Every time the macro does a new "loop", the row
number in "Datasheet.Range" should increase by one. I.e., for the second
loop, the first line should do :
Modelsheet.Range("D6").Value = Datasheet.Range("A3").Value
etc.
I.e. the row number at the "right" in the equation should be the row number
of "cell".

Is it possible to do this in an elegant and functioning way ?

All help will be greatly appreciated.

Best regards,
Eirik Saevareid


Private Sub cmdKalkuler_Click()
Dim Modelsheet As Worksheet
Dim cell As Range

Set Modelsheet = Worksheets("Input")
Set Datasheet = Worksheets("Productmix2006")

For Each cell In Worksheets("Productmix2006").Range("A2:A183")
'Set the input values
Modelsheet.Range("D6").Value = Datasheet.Range("A2").Value
Modelsheet.Range("D7").Value = Datasheet.Range("B2").Value
Modelsheet.Range("D8").Value = Datasheet.Range("C2").Value
Modelsheet.Range("D9").Value = Datasheet.Range("D2").Value
Modelsheet.Range("D10").Value = Datasheet.Range("E2").Value
Modelsheet.Range("D11").Value = Datasheet.Range("F2").Value
'Ensure calculation, if not automatic
Modelsheet.Calculate
'Read the result value back, into the next column
cell.Offset(0, 12).Value = Modelsheet.Range("F46").Value
Next

End Sub

Macro - Loop/automatization problem
 

Private Sub cmdKalkuler_Click()
Dim Modelsheet As Worksheet
Dim cell As Range

Set Modelsheet = Worksheets("Input")
Set Datasheet = Worksheets("Productmix2006")

For Each cell In Datasheet.Range("A2:A183")
'Set the input values
Modelsheet.Range("D6").Value = Datasheet.Range("A" & cell.Row).Value
Modelsheet.Range("D7").Value = Datasheet.Range("B" & cell.Row).Value
Modelsheet.Range("D8").Value = Datasheet.Range("C" & cell.Row).Value
Modelsheet.Range("D9").Value = Datasheet.Range("D" & cell.Row).Value
Modelsheet.Range("D10").Value = Datasheet.Range("E" & cell.Row).Value
Modelsheet.Range("D11").Value = Datasheet.Range("F" & cell.Row).Value
'Ensure calculation, if not automatic
Modelsheet.Calculate
'Read the result value back, into the next column
cell.Offset(0, 12).Value = Modelsheet.Range("F46").Value
Next



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eirik Sævareid" wrote in message
...
Hello,

This problem may occur very "basic", which I apologize for.
I try to make a macro to automatize a task (move data from one sheet to
another, return av value, and loop this process a number of times (several
hundred lines). The macro is listed below.

What is my problem is : Every time the macro does a new "loop", the row
number in "Datasheet.Range" should increase by one. I.e., for the second
loop, the first line should do :
Modelsheet.Range("D6").Value = Datasheet.Range("A3").Value
etc.
I.e. the row number at the "right" in the equation should be the row
number
of "cell".

Is it possible to do this in an elegant and functioning way ?

All help will be greatly appreciated.

Best regards,
Eirik Saevareid


Private Sub cmdKalkuler_Click()
Dim Modelsheet As Worksheet
Dim cell As Range

Set Modelsheet = Worksheets("Input")
Set Datasheet = Worksheets("Productmix2006")

For Each cell In Worksheets("Productmix2006").Range("A2:A183")
'Set the input values
Modelsheet.Range("D6").Value = Datasheet.Range("A2").Value
Modelsheet.Range("D7").Value = Datasheet.Range("B2").Value
Modelsheet.Range("D8").Value = Datasheet.Range("C2").Value
Modelsheet.Range("D9").Value = Datasheet.Range("D2").Value
Modelsheet.Range("D10").Value = Datasheet.Range("E2").Value
Modelsheet.Range("D11").Value = Datasheet.Range("F2").Value
'Ensure calculation, if not automatic
Modelsheet.Calculate
'Read the result value back, into the next column
cell.Offset(0, 12).Value = Modelsheet.Range("F46").Value
Next

End Sub