PDA

View Full Version : [Novice] VBA Looping Macro



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 !

SamT
09-19-2016, 07:46 AM
I did NOT change the code. I only changed the wording.

The below illustrates the first step in turning a recorfded Macro into a real Procedure (Sub or Function)

The Macro Recorder records all actions taken on the worksheet, even those little oopsies; Delete all of them. Subs reference Objects directly; Delete all "Select. . .Selection" and "Activate. . . Active*."

You said that you wanted to copy data but this macro is inserting formulas.
THis Macro is handling 3 Worksheets.
You said that you wanted to loop by rows. This Macro does not reflect that.


Sub last()
' Keyboard Shortcut: Ctrl+Shift+L
'Changing the name of the sub will cancel keyboard shortcut assignment

Dim i As Long 'Always use Longs for Row and Column Counters
Dim NumRows As LOng

' Set numrows = number of rows of data.
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

' Establish "For" loop to loop "numrows" number of times.
'Start loop here



Sheets("CALCULATION").Select
'Best to use A1 style formulas
Range("E1").FormulaR1C1 = "=DATA!R2C5"
Range("B3").FormulaR1C1 = "=DATA!R2C17&"" ""&DATA!R2C18" 'Unknown formula formation
Range("C3").FormulaR1C1 = "=DATA!R2C2"
Range("C3").FormulaR1C1 = "=DATA!R2C3"
Range("C6:E6").FormulaR1C1 = "=DATA!R2C1"
Range("C9").FormulaR1C1 = "=DATA!R2C25"
Range("C10").FormulaR1C1 = "=DATA!R2C19"
Range("C11").FormulaR1C1 = "=DATA!R2C24"
Range("C15").FormulaR1C1 = "=DATA!R2C31"
Range("C18").FormulaR1C1 = "=DATA!R2C21"
Range("C19").FormulaR1C1 = "=DATA!R2C28"


Sheets("DATA").Select
Range("AF2").FormulaR1C1 = "=CALCULATION!R[21]C[-29]"
Range("M2").Copy

Sheets("DATA_ASSESSEMENT").Select
Range("K2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:= False, Transpose:= False
Application.CutCopyMode = False

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'Assign specific Range
'
Sheets("DATA").Selection.Delete Shift:=xlUp 'Assign specific Range

Sheets("DATA").ActiveCell.Offset(1, 0).Select 'Assign specific Range
'Next 'End of Loop
End Sub


Looping thru rows is done with the Cells Property

Sub RowLooper()
Dim i as long
For i = 1 to 10
Cells(, "A") = "A" & i + 10
Cells(i, "B") = "B" & i + 20
End Sub
Next i

Paul_Hossler
09-19-2016, 10:20 AM
I had a really hard time following the macro, and the suggestions below are most likely not 100% right

I addition to SamT's, I've found it to generate less errors to debug if I always fully define the worksheet.

I.e. Range(...) refers to what ever the active sheet is, but Worksheets("Sheet1").Range (...) is explicit



Option Explicit

Sub last()
Dim iRowNum As Long, iNumRows As Long
Dim wsData As Worksheet, wsCalc As Worksheet, wsAssessment As Worksheet

Set wsData = Worksheets("DATA")
Set wsCalc = Worksheets("CALCULATION")
Set wsAssessment = Worksheets("DATA_ASSESSEMENT")

iNumRows = wsData.Range("A2", Range("A2").End(xlDown)).Row

For iRowNum = 2 To iNumRows

wsCalc.Range("E1").Value = wsData.Cells(iRowNum, 5).Value
wsCalc.Range("B3").Value = wsData.Cells(iRowNum, 17).Value & " " & wsData.Cells(iRowNum, 18).Value
wsCalc.Range("C3").Value = wsData.Cells(iRowNum, 2).Value
wsCalc.Range("D3").Value = wsData.Cells(iRowNum, 3).Value ' C3 repeated???
wsCalc.Range("C6").Value = wsData.Cells(iRowNum, 1).Value
wsCalc.Range("D6").Value = wsData.Cells(iRowNum, 1).Value
wsCalc.Range("E6").Value = wsData.Cells(iRowNum, 1).Value
wsCalc.Range("C9").Value = wsData.Cells(iRowNum, 25).Value
wsCalc.Range("C10").Value = wsData.Cells(iRowNum, 19).Value
wsCalc.Range("C11").Value = wsData.Cells(iRowNum, 24).Value
wsCalc.Range("C15").Value = wsData.Cells(iRowNum, 31).Value
wsCalc.Range("C18").Value = wsData.Cells(iRowNum, 21).Value
wsCalc.Range("C19").Value = wsData.Cells(iRowNum, 28).Value

wsData.Range("C20").Value = wsCalc.Range("AF2").Value

wsData.Rows(iRowNum).Copy ' or maybe wsData.Rows(3).Copy ??????

wsAssessment.Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Next iRowNum
End Sub

SamT
09-19-2016, 10:39 AM
Paul,

You will find that using With WsCalc is faster than Explicitly declaring the sheet with each Range.

Each 'dot, (example: WsCalc.Range,) requires the compiled code to explicitly refer to the Parent Object and that takes several CPU cycles.

My personal Rule of Thumb is "No more than three explicit assignments, including loop iterations." YMMV.

jolivanes
09-19-2016, 03:45 PM
http://www.ozgrid.com/forum/showthread.php?t=201174&p=777676#post777676

Paul_Hossler
09-19-2016, 04:09 PM
Paul,

You will find that using With WsCalc is faster than Explicitly declaring the sheet with each Range.

Each 'dot, (example: WsCalc.Range,) requires the compiled code to explicitly refer to the Parent Object and that takes several CPU cycles.

My personal Rule of Thumb is "No more than three explicit assignments, including loop iterations." YMMV.

Possibly, but I've never seen a benchmark comparison. If you can reference one, I'd like to see it

In this case, since performance didn't seem to be an issue, and on a reasonably modern PC a few 100K CPU cycles would be imperceptible, I suggested the most easily understood (IMO) approach for the OP to look at and modify if they were interested

jolivanes
09-19-2016, 06:03 PM
On the other forum I asked the OP for an attachment. Too much work probably. SamT, you would not have had the remarks in your Post #2, "You said that blah blah", and again, "you said that blah blah" if the Op had attached an example. BTW, there was an answer from his fellow countryman on the other site.