coolworld
09-19-2016, 04:01 AM
Hello everyone,
I am a novice in vba and would like to require your help on my work.
I was able to record a macro that allows me to copy values from my "data" sheet into my "calculation" sheet that returs a score based on the data. Then the macro copies the information of the row in "data" sheet with the score from "calculation" into a third spreadsheet : "data_assessment".
It works properly but I would like to know how to automate the process for each row of info present on my data spreadsheet ? How to loop it for each row ?
I tried to find the solution myself but have not succeed.
Sub last()
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
' Insert your code here.
' last Macro
' last Macro
' Keyboard Shortcut: Ctrl+Shift+L
Sheets("CALCULATION").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C5"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C17&"" ""&DATA!R2C18"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C3"
Range("C6:E6").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C1"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C25"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C19"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C24"
Range("C15").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C31"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C21"
Range("C19").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C28"
Range("C20").Select
Sheets("DATA").Select
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=CALCULATION!R[21]C[-29]"
Rows("2:2").Select
Range("M2").Activate
Selection.Copy
Sheets("DATA_ASSESSEMENT").Select
Rows("2:2").Select
Range("K2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:= False, Transpose:= False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("DATA").Select
Selection.Delete Shift:=xlUp
Sheets("DATA").Select
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
End Sub
There you see the code I tried to use to loop for each row but excel returns me:error for this selection of range and ask to select a single range.
Hope you can help me.
Many thanks in advance !
I am a novice in vba and would like to require your help on my work.
I was able to record a macro that allows me to copy values from my "data" sheet into my "calculation" sheet that returs a score based on the data. Then the macro copies the information of the row in "data" sheet with the score from "calculation" into a third spreadsheet : "data_assessment".
It works properly but I would like to know how to automate the process for each row of info present on my data spreadsheet ? How to loop it for each row ?
I tried to find the solution myself but have not succeed.
Sub last()
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
' Insert your code here.
' last Macro
' last Macro
' Keyboard Shortcut: Ctrl+Shift+L
Sheets("CALCULATION").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C5"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C17&"" ""&DATA!R2C18"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C3"
Range("C6:E6").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C1"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C25"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C19"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C24"
Range("C15").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C31"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C21"
Range("C19").Select
ActiveCell.FormulaR1C1 = "=DATA!R2C28"
Range("C20").Select
Sheets("DATA").Select
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=CALCULATION!R[21]C[-29]"
Rows("2:2").Select
Range("M2").Activate
Selection.Copy
Sheets("DATA_ASSESSEMENT").Select
Rows("2:2").Select
Range("K2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:= False, Transpose:= False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("DATA").Select
Selection.Delete Shift:=xlUp
Sheets("DATA").Select
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
End Sub
There you see the code I tried to use to loop for each row but excel returns me:error for this selection of range and ask to select a single range.
Hope you can help me.
Many thanks in advance !