I am having a difficult time grasping this for some reason. I have a column
of dates from August 2007 to December 2010 (all end of month). These are in
column D from row 5 to row 45. What I need to do is to have a row of values
that is in column E to refresh monthly when an end of month date is put into
another cell, in this case in cell B1. Here's the thing; from the beginning
date until the current month I need it to have the formula E14-$H$4 (the 14
is what it is in July 2008. It was E13 in June, will be E15 in August, etc).
The following month it needs to roll down one month, and it relates to
another table. In this case July 2008 has the previously mentioned formula,
so August equals B10, September 2008 equals B11, October 2008 equal B12, etc.
Next month the formula mentioned above rolls down one month, and September
2008 equals B10, October equals B11, and so one.

Based on what I can find out it seems that a Do Until is the best thing to do,
but I have never been able to get this to work (I'm pretty new to VBA). If
that's not the best thing, then what would be? This is something I'll be
using in a variety of workbooks going forward, so I would REALLY appreciate
any help anyone could give me. Thanks in advance

Frank R.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

I'd like to help but am having difficulty understanding the question.

Your quote:- What I need to do is to have a row of values that is in column E
Can't have a row of values in a column. Do you mean a row of values that is
in COLUMN E?

Your quote:- (the 14 is what it is in July 2008. It was E13 in June, will be
E15 in August, etc)
E14 is adjacent to May, E13 adjacent to April and E15 adjacent to June

You then refer to cells in column B and I can't understand that at all.

Could you post a small sample of the worksheet. On the formulas in the
worksheet, place a single quote in front of them so that when copying them to
the post, they show up as formulas not the results of the formula.

--
Regards,

OssieMac


"lonnierudd via OfficeKB.com" wrote:

I am having a difficult time grasping this for some reason. I have a column
of dates from August 2007 to December 2010 (all end of month). These are in
column D from row 5 to row 45. What I need to do is to have a row of values
that is in column E to refresh monthly when an end of month date is put into
another cell, in this case in cell B1. Here's the thing; from the beginning
date until the current month I need it to have the formula E14-$H$4 (the 14
is what it is in July 2008. It was E13 in June, will be E15 in August, etc).
The following month it needs to roll down one month, and it relates to
another table. In this case July 2008 has the previously mentioned formula,
so August equals B10, September 2008 equals B11, October 2008 equal B12, etc.
Next month the formula mentioned above rolls down one month, and September
2008 equals B10, October equals B11, and so one.

Based on what I can find out it seems that a Do Until is the best thing to do,
but I have never been able to get this to work (I'm pretty new to VBA). If
that's not the best thing, then what would be? This is something I'll be
using in a variety of workbooks going forward, so I would REALLY appreciate
any help anyone could give me. Thanks in advance

Frank R.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Sorry, I mispoke and I wasn't clear.
Here's part of the spreadsheet as it sets right now. I've added the column
and row numbers and letters for reference sake.

A B C D E
1 31-Jul
2
3
4 MODEL 300 Period Value Formula in
column E
5 MSRP $25,325 Aug-07 $18,848 (no formula
here)
6 INVOICE $23,658 Sep-07 $18,320 'E5-$H
$4
7 Oct-07 $17,792 'E6-$H$4
8 39K MILES Nov-07 $17,264
'E7-$H$4
9 Term Value Dec-07 $16,736 '=E8-$H$4
10 1 $13,905 Jan-08 $16,208 '=E9-$H$4
11 2 $12,920 Feb-08 $15,680 '=E10-$H$4
12 3 $12,015 Mar-08 $15,152 '=E11-$H$4
13 4 $11,455 Apr-08 $14,623 '=E12-$H$4
14 5 $11,095 May-08 $14,095 '=E13-$H$4
15 6 $11,060 Jun-08 $13,567 '=E14-$H$4
16 7 $11,200 Jul-08 $13,039 '=E15-$H$4
17 8 $11,010 Aug-08 $13,905 '=B10
18 9 $10,580 Sep-08 $12,920 '=B11
19 10 $10,160 Oct-08 $12,015 '=B12
20 11 $9,705 Nov-08 $11,455 '=B13
21 12 $9,210 Dec-08 $11,095 '=B14
22 13 $8,665 Jan-09 $11,060 '=B15
23 14 $7,985 Feb-09 $11,200 '=B16

If you'll notice, cell E17 of August 08 equals B10. This needs to move down
each month, so that when it's August, Sept 08 will equal B10, and what is in
August 08 has it's formula changed to carry down what is above it (in this
case that would be '=E16-$H$4). This goes down to row 45, which is December
2010. It would be great if I didn't have to do this manually each month. I
appreciate your help.




OssieMac wrote:
I'd like to help but am having difficulty understanding the question.

Your quote:- What I need to do is to have a row of values that is in column E
Can't have a row of values in a column. Do you mean a row of values that is
in COLUMN E?

Your quote:- (the 14 is what it is in July 2008. It was E13 in June, will be
E15 in August, etc)
E14 is adjacent to May, E13 adjacent to April and E15 adjacent to June

You then refer to cells in column B and I can't understand that at all.

Could you post a small sample of the worksheet. On the formulas in the
worksheet, place a single quote in front of them so that when copying them to
the post, they show up as formulas not the results of the formula.

I am having a difficult time grasping this for some reason. I have a column
of dates from August 2007 to December 2010 (all end of month). These are in
[quoted text clipped - 16 lines]

Frank R.

--
Message posted via http://www.officekb.com

I see that what I typed didn't carry forward very well after I posted. If you
need me to try again, let me know.


lonnierudd wrote:
Sorry, I mispoke and I wasn't clear.
Here's part of the spreadsheet as it sets right now. I've added the column
and row numbers and letters for reference sake.

A B C D E
1 31-Jul
2
3
4 MODEL 300 Period Value Formula in
column E
5 MSRP $25,325 Aug-07 $18,848 (no formula
here)
6 INVOICE $23,658 Sep-07 $18,320 'E5-$H
$4
7 Oct-07 $17,792 'E6-$H$4
8 39K MILES Nov-07 $17,264
'E7-$H$4
9 Term Value Dec-07 $16,736 '=E8-$H$4
10 1 $13,905 Jan-08 $16,208 '=E9-$H$4
11 2 $12,920 Feb-08 $15,680 '=E10-$H$4
12 3 $12,015 Mar-08 $15,152 '=E11-$H$4
13 4 $11,455 Apr-08 $14,623 '=E12-$H$4
14 5 $11,095 May-08 $14,095 '=E13-$H$4
15 6 $11,060 Jun-08 $13,567 '=E14-$H$4
16 7 $11,200 Jul-08 $13,039 '=E15-$H$4
17 8 $11,010 Aug-08 $13,905 '=B10
18 9 $10,580 Sep-08 $12,920 '=B11
19 10 $10,160 Oct-08 $12,015 '=B12
20 11 $9,705 Nov-08 $11,455 '=B13
21 12 $9,210 Dec-08 $11,095 '=B14
22 13 $8,665 Jan-09 $11,060 '=B15
23 14 $7,985 Feb-09 $11,200 '=B16

If you'll notice, cell E17 of August 08 equals B10. This needs to move down
each month, so that when it's August, Sept 08 will equal B10, and what is in
August 08 has it's formula changed to carry down what is above it (in this
case that would be '=E16-$H$4). This goes down to row 45, which is December
2010. It would be great if I didn't have to do this manually each month. I
appreciate your help.

I'd like to help but am having difficulty understanding the question.

[quoted text clipped - 17 lines]

Frank R.

--
Message posted via http://www.officekb.com