Thread: running a loop on a few columns simultaneously?

Hi all. I am trying to run a loop on a few columns simultaneously, from the last row to the top (well, until the 2nd row).

So I have a few columns of data, A through I, and the number of rows they populate changes all the time.

So how would I run a loop that will check for the following:

If a certain row in Column F is greater than the previous row in column F by 1.5times, then in that same row, if number in Column B is greater than number in E, the code should put the the value of E in a new column, lets say M (in the same row)

I know that sounds very confusing, but is that even possible? I am playing with loops, and I can only do very simple loops where I define a range, like

Sub runningdown()

    Dim milosRow As Long

    With Range("H:H")
        For mRow = .Rows.Count To 1 Step -1
            If .Cells(mRow).Value > 3 Then .Cells(mRow, 7) = .Cells(mRow).Value
        Next mRow
    End With

End Sub

I need to see what a more complicated loop would look, like the above description of what I need. That is the only way I could learn, is if I see it and then dissect it and just learn from it.

Thank you all. If what I have above isn't even possible, please let me know

much easier to understand with real data

Now, when you say "if F8 > F9 by 1.5 times" I assume you actually mean "if F9 > F8 by 1.5 times"

I have added the close price the day before the 1.5 volume lift in column K. An alternative would be to colour the cells/rows in the actual table, but then again I don't know why you want it in column K. Also, I have added a cell for the volume multiplier into the sheet, so you can run it for different volume lifts.

I hope this is what you wanted

Sub RunMe()
    Dim lastRow As Integer
    lastRow = Range("A1").CurrentRegion.Rows.Count
    Application.ScreenUpdating = False
    Range(Cells(2, 11), Cells(lastRow, 11)).Clear
    Cells(lastRow, 6).Select
    While Selection.Row <> 2
        If Selection.Value > Selection.Offset(-1, 0).Value * Range("myMultiplier").Value Then
            If Selection.Offset(-1, -1) > Selection.Offset(-1, -4) Then
                Selection.Offset(-1, 5).Value = Selection.Offset(-1, -1)
            End If
        End If
        Selection.Offset(-1, 0).Select
    Wend
    Application.ScreenUpdating = True
End Sub

whoa! thank you! this is exactly what i was looking for!

i think i understand what you are doing here with the loop, by selecting the last row of colum F. then u r playing with that selection, up 2 and down 2 rows, correct?

is there a different way to code this? the way the teach u in the books, with simple "for x = something to something" or "for each..."

or is this the only way?

thank you so much!

There are many ways to do this. It comes down to personal preference. But the principle is the same. You need to start somewhere (in this case the last row) and then work through each row one by one, and then stop somewhere (in this case the second to top row). Some different ways you could do this are....

first count the number of rows, then do a for next loop like the following

for x = lastRow to 2 step -1

This will set x = to the row you want to work on, and step backwards up the page.

Another way would be to load your column into a range in VBA with a line of code like this.

set myRange= range(cells(2,6),cells(lastrow,6))
'cells(2,6) = cell F2, and cells (lastrow,6) will = the last cell in row F.  
'By using the format Range(A,B) excel will select the range "F2:F268" assuming F268 was the last row
for each cell in myrange
...
next cell

This would start at the top of the list and work down the list, so you would need to change your code so that it checked the cell below the current cell, rather than the cell above the current cell, like this..

if cell.offset(1,0).value>cell.value * 1.5 then....

the wend statement relates to the while statement (ie while end). It loops between while and wend until the test is satisfied, in this case until the current row is 2, which is the top row in your list.

Technically, using set myRange=.... is probably better, because everything is done in memory. The solution I provided you actually uses the cells in the sheet, and moves the selected cell around to keep track. This is much slower, and for this reason I have to turn off screen updating. If I didn't turn this off, it would look cool, but it would be verrrry slow.

When I learnt to do this, I never used set myRange =.. because I didn't understand how to make it work. Once you work it out, it is probably a better way to go. I don't know why I didn't do it that way.

Originally Posted by losmi8

i think i understand what you are doing here with the loop, by selecting the last row of colum F. then u r playing with that selection, up 2 and down 2 rows, correct?

it is selecting the last row, and then comparing itself to the row above it (not 2 rows). When it has finished with that row, it is selecting the next row up, and then starts over again.

Originally Posted by losmi8

the loop knows to run in reverse (from bottom to the top) because of your selection.offset(-1,-1) lines? right?

yes, but it is selection.offset(-1,0).select, which means select the same cell in the line above.

Originally Posted by losmi8

to somehow define a whole column (from 2nd row til the last row) and then just run a loop

That's effectively what the set myRange... option does. You first load the target range (in your example it is the records in column F) and then loop through them using "for each cell in myRange...." approach