Turn existing VBA code into a loop

[anglais428]

Hi,
I have the current VBA code below which I would like to turn into a loop:

Dim NoYrs As Long 'number of years
Dim LRbf As Long 'last row


NoYrs = Sheets("Lists").Range("E655536").End(xlUp).Row - 1
LRbf = Sheets("BF").Range("A655536").End(xlUp).Row




' Get First batch
    Sheets("BF").Select
    Range(Cells(3, 4), Cells(LRbf, (NoYrs + 3))).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Upload sheet").Select
    Range("J2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'Get second batch
    Sheets("BF").Select
    Range(Cells(3, (NoYrs+3+1)), Cells(LRbf, (NoYrs * 2 + 3))).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Upload sheet").Select
    Range("J" & LRbf).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Any advise?

 

[RedBeard]

Just exactly do you want to loop?

 

[anglais428]

To try to explain this further take the below:

    Sheets("Cluster").Select    Range(Cells(3, ((NoYrs * [B]0[/B]) + 3 + 1)), Cells(LRbf, (NoYrs * [B]1[/B] + 3))).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Upload sheet").Select
    Range("J" & 2 + (NoLocations * [B]8[/B])).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

I have 5 'batches' I need to do. At the minute I have the code above written out 5 times and it works fine. I am wondering if I can somehow loop it so that those values in bold would increment by 1 each time it loops. So for example the 0 becomes 1; the 1 becomes 2; and the 8 become 9. And for the third run the 1 would become a 2; the 2 becomes a 3; and the 9 becomes 10. Then repeat until all 5 batches are run.

Thanks

 

[RedBeard]

Have a look at http://www.excel-easy.com/vba/loop.html

You can then simply replace the numbers you want to increment with variables that for each iteration of the loop increases by one.

Something like:

Dim x As LongDim y As Long
Dim z As Long
Dim i As Long


x = 0
y = 1
z = 8


For i = 1 To 5

    Sheets("Cluster").Select Range(Cells(3, ((NoYrs * x) + 3 + 1)), Cells(LRbf, (NoYrs * y + 3))).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Upload sheet").Select
    Range("J" & 2 + (NoLocations * z)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    x = x + 1
    y = y + 1
    z = z + 1

Next i

 

[anglais428]

Awesome, thanks for this!