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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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..

Please Login or Register  to view this content.

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