Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the sake
of this we'll call it column D, but it will in fact be varying columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they are
always in the same column together.

At all times the cells in the column between both items are blank. What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly enough
and its getting on in time and I thought that I'd best try to ask it again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm trying
to learn because I see the pluses that it holds in my work environment....So
please be patient.

I know that this is a seperate question but with the apple and banana cells,
is there a piece of simple code that will work out the numerical positional
difference between the two and give me a value. ie. If Apple is a D10 and
Banana is at D15 it will give me a value of 5? Thus I'm then able to code
the difference a put a place marker at column C half way between the two
values in column D....

Regards,

Easy enough but you say these cells are BLANK. Before and after example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the
sake
of this we'll call it column D, but it will in fact be varying columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they are
always in the same column together.

At all times the cells in the column between both items are blank. What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able to code
the difference a put a place marker at column C half way between the two
values in column D....

Regards,

Hi Don,

Ok example

Column D:

D3:Apple
D4
D5
D6
D7: Banana

I want to select everything from Banana up to Apple....as I'm on Banana to
start with.

Regards,


"Don Guillett" wrote:

Easy enough but you say these cells are BLANK. Before and after example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the
sake
of this we'll call it column D, but it will in fact be varying columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they are
always in the same column together.

At all times the cells in the column between both items are blank. What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able to code
the difference a put a place marker at column C half way between the two
values in column D....

Regards,

This should do it -- change Apple and Bananna to what you need it to be

Sub fill()

Range("d1").Select
Lastrow = Cells(Rows.Count, 4).End(xlUp).Row
Do Until ActiveCell.Row = Lastrow
If ActiveCell.Value = "Apple" Then
v1 = ActiveCell.Address
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
Range("d1").Select
Do Until ActiveCell.Row = Lastrow
If ActiveCell.Value = "Bananna" Then
v2 = ActiveCell.Address
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
v3 = v1 & ":" & v2

Range(v3).Select
Selection.FillDown
End Sub


"Darren Ingram" wrote:

Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the sake
of this we'll call it column D, but it will in fact be varying columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they are
always in the same column together.

At all times the cells in the column between both items are blank. What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly enough
and its getting on in time and I thought that I'd best try to ask it again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm trying
to learn because I see the pluses that it holds in my work environment....So
please be patient.

I know that this is a seperate question but with the apple and banana cells,
is there a piece of simple code that will work out the numerical positional
difference between the two and give me a value. ie. If Apple is a D10 and
Banana is at D15 it will give me a value of 5? Thus I'm then able to code
the difference a put a place marker at column C half way between the two
values in column D....

Regards,

Darren Ingram wrote:
I have two cells (we'll call them: Apple & Banana) in a column (for the sake
of this we'll call it column D, but it will in fact be varying columns)...
[..] What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between it
and Banana and also Banana.

Dim ws As Worksheet
Dim rangeApple As Range, rangeBanana As Range

Set ws = ActiveWorksheet
' set your rangeApple and rangeBanana to those two cells here

ws.Range (ws.Cells (rangeApple.Row + 1
ws.Range(ws.Cells(rApple.Row + 1, rApple.Column), _
ws.Cells(rBanana.Row, rBanana.Column)).Formula = rApple.Formula

' done!

I know that this is a seperate question but with the apple and banana cells,
is there a piece of simple code that will work out the numerical positional
difference between the two and give me a value.

Dim rowsBetween as Long
rowsBetween = rBanana.Row - rApple.Row


HTH,

Lars

That much was a given. Now what do you want to do???
I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between
it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi Don,

Ok example

Column D:

D3:Apple
D4
D5
D6
D7: Banana

I want to select everything from Banana up to Apple....as I'm on Banana to
start with.

Regards,


"Don Guillett" wrote:

Easy enough but you say these cells are BLANK. Before and after example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the
sake
of this we'll call it column D, but it will in fact be varying
columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they
are
always in the same column together.

At all times the cells in the column between both items are blank.
What I
want to do it to put the curser on Banana (the bottom cell item) and
for a
piece of code to select Banana and every cell in the column above it up
to
and including Apple. I then want to process the formula that is inside
of
the Apple cell (the topmost cell)so that it applies to all cells
between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able to
code
the difference a put a place marker at column C half way between the
two
values in column D....

Regards,

Ok the first one would if Apple was D7.... =Sum(D6+C7-B7)
and down the column etc etc.

"Don Guillett" wrote:

That much was a given. Now what do you want to do???
I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between
it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi Don,

Ok example

Column D:

D3:Apple
D4
D5
D6
D7: Banana

I want to select everything from Banana up to Apple....as I'm on Banana to
start with.

Regards,


"Don Guillett" wrote:

Easy enough but you say these cells are BLANK. Before and after example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the
sake
of this we'll call it column D, but it will in fact be varying
columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they
are
always in the same column together.

At all times the cells in the column between both items are blank.
What I
want to do it to put the curser on Banana (the bottom cell item) and
for a
piece of code to select Banana and every cell in the column above it up
to
and including Apple. I then want to process the formula that is inside
of
the Apple cell (the topmost cell)so that it applies to all cells
between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able to
code
the difference a put a place marker at column C half way between the
two
values in column D....

Regards,

You really should learn to explain the first time. Now where would you like
this formula(s). Before/after examples.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Ok the first one would if Apple was D7.... =Sum(D6+C7-B7)
and down the column etc etc.

"Don Guillett" wrote:

That much was a given. Now what do you want to do???
I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells
between
it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi Don,

Ok example

Column D:

D3:Apple
D4
D5
D6
D7: Banana

I want to select everything from Banana up to Apple....as I'm on Banana
to
start with.

Regards,


"Don Guillett" wrote:

Easy enough but you say these cells are BLANK. Before and after
example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in
message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for
the
sake
of this we'll call it column D, but it will in fact be varying
columns)...
The cell points at which Apple and Banana lay vary and at all times
the
number of cells between Apple and Banana also vary.... However,
they
are
always in the same column together.

At all times the cells in the column between both items are blank.
What I
want to do it to put the curser on Banana (the bottom cell item) and
for a
piece of code to select Banana and every cell in the column above it
up
to
and including Apple. I then want to process the formula that is
inside
of
the Apple cell (the topmost cell)so that it applies to all cells
between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it
clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and
banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a
D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able
to
code
the difference a put a place marker at column C half way between the
two
values in column D....

Regards,

Might be easier for you to send your workbook to my address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Ok the first one would if Apple was D7.... =Sum(D6+C7-B7)
and down the column etc etc.

"Don Guillett" wrote:

That much was a given. Now what do you want to do???
I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells
between
it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in message
...
Hi Don,

Ok example

Column D:

D3:Apple
D4
D5
D6
D7: Banana

I want to select everything from Banana up to Apple....as I'm on Banana
to
start with.

Regards,


"Don Guillett" wrote:

Easy enough but you say these cells are BLANK. Before and after
example
please.

so that it applies to all cells between

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Darren Ingram" wrote in
message
...
Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for
the
sake
of this we'll call it column D, but it will in fact be varying
columns)...
The cell points at which Apple and Banana lay vary and at all times
the
number of cells between Apple and Banana also vary.... However,
they
are
always in the same column together.

At all times the cells in the column between both items are blank.
What I
want to do it to put the curser on Banana (the bottom cell item) and
for a
piece of code to select Banana and every cell in the column above it
up
to
and including Apple. I then want to process the formula that is
inside
of
the Apple cell (the topmost cell)so that it applies to all cells
between
it
and Banana and also Banana.

I have asked this question already but I don't think I did it
clearly
enough
and its getting on in time and I thought that I'd best try to ask it
again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm
trying
to learn because I see the pluses that it holds in my work
environment....So
please be patient.

I know that this is a seperate question but with the apple and
banana
cells,
is there a piece of simple code that will work out the numerical
positional
difference between the two and give me a value. ie. If Apple is a
D10
and
Banana is at D15 it will give me a value of 5? Thus I'm then able
to
code
the difference a put a place marker at column C half way between the
two
values in column D....

Regards,

Don Guillett wrote:
You really should learn to explain the first time.

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

Hi dmoney,

I'm receiving a global error in your code at this point:

Range(v3).Select

Any thoughts?

Also in your code where you've put the "apple" and "banana" what do I put?
As I can't really put any specific cell reference as it changes from
situation to situation....The column stays the same but the cell row is
always different. Sometimes its D10 sometimes D44, D59,D432....etc.

Sorry for my ignorance but I'm learning and very junior at this.

Regards,



"dmoney" wrote:

This should do it -- change Apple and Bananna to what you need it to be

Sub fill()

Range("d1").Select
Lastrow = Cells(Rows.Count, 4).End(xlUp).Row
Do Until ActiveCell.Row = Lastrow
If ActiveCell.Value = "Apple" Then
v1 = ActiveCell.Address
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
Range("d1").Select
Do Until ActiveCell.Row = Lastrow
If ActiveCell.Value = "Bananna" Then
v2 = ActiveCell.Address
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
v3 = v1 & ":" & v2

Range(v3).Select
Selection.FillDown
End Sub


"Darren Ingram" wrote:

Hi,

I have two cells (we'll call them: Apple & Banana) in a column (for the sake
of this we'll call it column D, but it will in fact be varying columns)...
The cell points at which Apple and Banana lay vary and at all times the
number of cells between Apple and Banana also vary.... However, they are
always in the same column together.

At all times the cells in the column between both items are blank. What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between it
and Banana and also Banana.

I have asked this question already but I don't think I did it clearly enough
and its getting on in time and I thought that I'd best try to ask it again -
Hopefully a little clearer this time. I'm new at VBA coding but I'm trying
to learn because I see the pluses that it holds in my work environment....So
please be patient.

I know that this is a seperate question but with the apple and banana cells,
is there a piece of simple code that will work out the numerical positional
difference between the two and give me a value. ie. If Apple is a D10 and
Banana is at D15 it will give me a value of 5? Thus I'm then able to code
the difference a put a place marker at column C half way between the two
values in column D....

Regards,

Lars,

Thanks for the code but how does the "apple or banana" row number get
calculated when it changes from situation to situation? One week it might be
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?
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?

Sorry for my questions....I'm very new at this and I'm trying to understand
my coding rather that just typing what people write out of ignorance.

Regards,



"Lars Uffmann" wrote:

Darren Ingram wrote:
I have two cells (we'll call them: Apple & Banana) in a column (for the sake
of this we'll call it column D, but it will in fact be varying columns)...
[..] What I
want to do it to put the curser on Banana (the bottom cell item) and for a
piece of code to select Banana and every cell in the column above it up to
and including Apple. I then want to process the formula that is inside of
the Apple cell (the topmost cell)so that it applies to all cells between it
and Banana and also Banana.

Dim ws As Worksheet
Dim rangeApple As Range, rangeBanana As Range

Set ws = ActiveWorksheet
' set your rangeApple and rangeBanana to those two cells here

ws.Range (ws.Cells (rangeApple.Row + 1
ws.Range(ws.Cells(rApple.Row + 1, rApple.Column), _
ws.Cells(rBanana.Row, rBanana.Column)).Formula = rApple.Formula

' done!

I know that this is a seperate question but with the apple and banana cells,
is there a piece of simple code that will work out the numerical positional
difference between the two and give me a value.

Dim rowsBetween as Long
rowsBetween = rBanana.Row - rApple.Row


HTH,

Lars

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

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