[Novice] VBA Looping Macro

[coolworld]

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]

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]

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]

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]

http://www.ozgrid.com/forum/showthre...676#post777676

[Paul_Hossler]

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]

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.