Hello VBA experts!
I'm not familiar with VBA at all, and my attempt to cobble together a function has been an abject failure
All the function needs to do is go through the next 50 rows in an adjacent column and test each cell in turn to see whether its value is: (a) higher than the previous max (and if so, reset the max to that new value); and (b) X units less than the previous max. The function can stop running once it finds the first (if any) cell that meets condition (b).
I think I'm getting stuck because I don't quite understand how VBA instructs excel to "move" from one cell to the next. Do cells have to be selected/activated before they can be analyzed as part of a loop? It seems like that would be very slow, and I have millions of rows of data to populate with this function. It seems like it would be better for a loop to simply use a counter variable to look at the next row each cycle...
So the logic of the function extremely straightforward:
- Is CurrentCell > Previous_Max? If true, then Previous_Max = CurrentCell. If False, then ...
- Is CurrentCell < (Previous_Max - 3)? If true, then StopLossAddress = CurrentCell's address (i.e. the cell's range), and Exit loop. If false, continue loop.
- After loop is finished, if StopLossAddress <> 0, then function = StopLossAddress (as a string). If no StopLossAddress was found, function = "no stop loss found".
But I just don't have enough experience with VBA to get it to work. I just keep getting errors!
Can anyone have a look at my code and see what's wrong? (Annotated code attached).
Thanks!
Bookmarks