Repeat short macro given # of times

[richjeff]

Hello all, I am fairly new to this and starting to get my feet under the ground. I need to repeat the below macro a given number of times but am having trouble setting a loop up correctly. I put 'Range("Z" & i + 1).Select' at the end to move to the next row down and repeat the process. Is this necessary? Or is there a way to set up the loop to move down one row and start over there? Any help would be greatly appreciated.
Any insights or adjustments would be greatly appreciated. Thank you so much

Sub Zip_Lookup()

Dim Formulas As Range, Results As Range

i = ActiveCell.Row
Set Formulas = Range("AA5:AP5")
Set Results = Application.Range(Cell1:="AA" & i, Cell2:="AP" & i)

Range("Z" & i).Select
Range("Z" & i).Copy

Range("E18").PasteSpecial xlPasteValues

Formulas.Copy
Results.PasteSpecial xlPasteValues
Range("Z" & i + 1).Select

End Sub

 

[Joe4]

Welcome to the Board!

Usually, there is no need to select ranges in VBA code. That just slows your code down.

There is currently no loop at all in your code.
If you want to loop through rows 1 to 10, you could do so like this:

Dim r as Long
For r = 1 to 10
    'your code here 
Next r

 

[richjeff]

Thank you Joe!
I left any loop out because I wasn't having any luck. I put in the code you suggested, and it still stalls out after Range("Z" & i + 1).Select. I cannot make it repeat the copy/paste part of the code for the life of me. Do I need to leave that last step out?

 

[Joe4]

Thank you Joe!
I left any loop out because I wasn't having any luck. I put in the code you suggested, and it still stalls out after Range("Z" & i + 1).Select. I cannot make it repeat the copy/paste part of the code for the life of me. Do I need to leave that last step out?

You wouldn't use "i" in that copy, as "r" is the variable to indicate the rows.
And you con't need to select it to copy it.
It would just be something like:

Range("Z" & r).Copy

If you want more specific help on setting this up from start to finish, it would be helpful to see what your data looks like and what your expected output should look like. It is difficult to understand exactly what you are trying to do from some code that has issues, and without seeing the data you are working with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[richjeff]

The goal is to take a Zip Code in column Z, paste it into cell E18, and take the results (Range Formulas on row 5) and paste them next to the zip code.
I put an example together below showing 32570 and the corresponding results pasted. I thought I was 99% of the way there but like I said, just having trouble making the code repeat itself
Thank you again for all your help

 

[Joe4]

See if this does what you want:

Sub Zip_Lookup_New()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find row of last zip code in column Z
    lr = Cells(Rows.count, "Z").End(xlUp).Row
    
'   Loop through each zip code in column Z, starting with row 8
    For r = 8 To lr
'       Set cell E18 equal to zip code
        Range("E18") = Cells(r, "Z")
'       Copy values from AA6:AP5 into same columns on current row
        Range("AA5:AP5").Copy
        Range("AA" & r).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next r
    
    Application.ScreenUpdating = True

End Sub

Notice how I did not use a single "Select" statement in any of the code!

 

[richjeff]

This worked thank you so much! Using the ScreenUpdating seems to speed it up a lot. And it looks like I had the cell references backwards where you have ( r, "Z") vs my ("Z" & i).
This looks much cleaner and I'll keep this in my records for future endeavors. Thank you again Joe I really appreciate it!

 

[Joe4]

You are welcome.

Using the ScreenUpdating seems to speed it up a lot.

Yes, that suspends all screen updating until the very end, after all the changes have been made.
It speeds up the code, and gets rid of a lot of screen "flashing".

And it looks like I had the cell references backwards where you have ( r, "Z") vs my ("Z" & i)

Not necessarily. It depends on what kind of range referencing you use (you can use either of the following).

If you use "Range", it is column letter first, then row number, i.e.
Range("Z1")

If you use "Cells", it is rows first, then columns, i.e.
Cells(1, "Z")

I often prefer Cells myself, as you can reference columns by letter or by number (i.e. "Z" = 26 th column), i.e.
Cells(1, 26)
That can be really helpful went you want to loop through columns.