He did. Given he didn't make any mistakes in expressing what he wanted
to do, I had all the information I needed to provide a very quick
solution, as I posted initially.

Best Regards,

Lars

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lars Uffmann" wrote in message
...
Darren Ingram wrote:
Thanks for the code but how does the "apple or banana" row number get
calculated when it changes from situation to situation?

Well of course you have to identify your apple and banana cells somehow...
Either you *know* where they are (because of an active selection, or
something else), or you have to locate them, using the Find statement, for
example:

Set rangeApple = ws.Cells.Find("apple", SearchOrder:=xlByRows,
SearchDirection:=xlNext)
Set rangeBanana = ws.Cells.Find("banana", SearchOrder:=xlByRows,
SearchDirection:=xlNext)

Alternatively to ws.Cells you can use ws.Columns(yourColumnIndex) if you
know which column you are searching in.

And the row number does not get calculated, it's a property of a cell
represented in a range - row, column, value and all the other properties
are directly linked in Excel's memory, otherwise Excel wouldn't know where
to store & display a value :)

D10 another week it might be Dxxx....(whatever). How does the code
identify that the curser goes up until it reaches the next filled cell in
column D?

It doesn't - you stated in your original post that:
At all times the cells in the column between both items are blank.
So I showed you how - given you have the 2 cells apple and banana (in a
range item, for example) - to apply the formula to all the cells between
apple and banana *and* the banana cell.
You need to forget about the "cursor" (selection). When you start using
that, you're only going to get in trouble (selection and cursor position
can change with an accidental mouseclick during script execution). Better
to use variables like worksheet and range objects.
You do not need a cursor "movement" at all here - the statement
Range (Cells (row1, column), Cells (row2, column))
returns a collection (similar to an array) of *all* the cells in column
between and including row1 and row2. By modifying the property of such a
range, you modify ALL cells in that range. Much like selecting a big range
manually and pasting some value from clipboard.

When I code the words apple or banana in my code what do I put in their
place. Or are these names just generic and only apply to the bottom
point and top point in the column that I'm running the script on?
They apply to a range that only contains your apple, respectively banana
cell. Which you *could* initialize like I suggested above - but if you
know where they are, don't search for them ;) Only makes your script
slower.

Hope I cleared things up a bit..

Regards,

Lars