Looping through columns

[beba420]

Hi all

So got some code where i essentially do the same thing to each column between columns between cells G5 and P5

Just wondering if theres a way i can rewrite this into a loop. I know how to loop through rows pretty easily, gust replace "C1" with "C"&x and have x=x+1 at the end of each loop. but am confused how to do it with columns. any advice would be greatly appreciated

tbh dont really "need" it as the code will work fine as is, but it is more so i learn how to do it properly (as well as more cleanly and so i know how its done in the future if i ever need to accomplish something similar)

code below

Sheets("temp").Range("G5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("H5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("I5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("J5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("K5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("L5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("M5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("N5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("O5").Value = rando1
    rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Sheets("temp").Range("P5").Value = rando1

[jeffreybrown]

Untested, but this should work.

Sub LoopThis()
    Dim i As Long
    For i = 7 To 16
        Sheets("temp").Cells(5, i).Value = rando1
        rando1 = Int((Worksheets("vars").Range("C1").Value - Worksheets("vars").Range("B1").Value + 1) * Rnd + Worksheets("vars").Range("B1").Value)
    Next i
End Sub

[beba420]

Thanks so much Jeffrey!!!

This method worked perfectly, will be sure to use it for reference in the future, cheers

[jeffreybrown]

You are very welcome and thanks for the feedback.

[AliGW]

Beba420 - I explained in your previous thread how to correctly mark a thread as solved. It is not the way you are doing it.

In future, please select Thread Tools from the menu link above and mark this thread as SOLVED. I have changed it for you again this time.

[beba420]

Hi Ali

Yes i noticed that, in my defense ya did explain several hours after i marked both threads as solved the incorrect way (in fact im relatively certain i marked this one solved first). Rest assured the lesson is learned and ill start to do it the proper way. thank you for your help