Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatization of data entry (and return of data) | Excel Programming | |||
Automatization routine | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
For..Next loop problem | Excel Programming | |||
loop problem | Excel Programming |