L
lonnierudd via OfficeKB.com
I've asked several questions lately, and I'm sorry about that. I have a
series of formulas that I could probably put into a very large macro, but I'm
sure there's a way in VBA, but I can't figure it out. I have a very large
spreadsheet to work with, but the formulas on most of them are either the
same of similar. In one section I have a table that is renewed monthly. The
table looks like this:
Term Value
1 13905
2 12920
3 12015
4 11455
5 11095
6 11060
7 11200
8 11010
9 10580
10 10160
11 9705
12 9210
13 8665
14 7985
15 7375
16 6990
17 6730
18 6690
19 6745
20 6585
21 6280
22 5980
23 5660
24 5320
In addition there is a row of numbers that relate to this table that also
changes monthly (it's used to keep track of the market value of a vehicle).
The rows of data look like this:
Aug-07 $18,848
Sep-07 $18,320
Oct-07 $17,792
Nov-07 $17,264
Dec-07 $16,736
1 Jan-08 $16,208
2 Feb-08 $15,680
3 Mar-08 $15,152
4 Apr-08 $14,623
5 May-08 $14,095
6 Jun-08 $13,567
7 Jul-08 $13,039
8 Aug-08 $13,905
9 Sep-08 $12,920
10 Oct-08 $12,015
11 Nov-08 $11,455
12 Dec-08 $11,095
13 Jan-09 $11,060
14 Feb-09 $11,200
15 Mar-09 $11,010
16 Apr-09 $10,580
17 May-09 $10,160
18 Jun-09 $9,705
19 Jul-09 $9,210
20 Aug-09 $8,665
21 Sep-09 $7,985
22 Oct-09 $7,375
23 Nov-09 $6,990
24 Dec-09 $6,730
25 Jan-10 $6,690
26 Feb-10 $6,745
27 Mar-10 $6,585
28 Apr-10 $6,280
29 May-10 $5,980
30 Jun-10 $5,660
31 Jul-10 $5,320
The August 07 amount is when it was put in service. We didn't really start
keeping track until a few months later, which is next to the number 1. There
is another cell that I put a date for the end of the month in, and I have
several other cells that use that date in a vlookup table using the dates you
see above (I've had to turn those formulas into VBA code, but that's another
issue).
Here's the deal. When it's July 2008 a formula is in place to move make
various calculations, and the August 2008 cell should equal the first number
in the first table above (in this case 13,905). In August 2008 I need the
formula to carry down from July, and for September to then equal the first
number in the table, October to equal the second number, etc. Is this
something I can set up in VBA to do? I appreciate the help.
series of formulas that I could probably put into a very large macro, but I'm
sure there's a way in VBA, but I can't figure it out. I have a very large
spreadsheet to work with, but the formulas on most of them are either the
same of similar. In one section I have a table that is renewed monthly. The
table looks like this:
Term Value
1 13905
2 12920
3 12015
4 11455
5 11095
6 11060
7 11200
8 11010
9 10580
10 10160
11 9705
12 9210
13 8665
14 7985
15 7375
16 6990
17 6730
18 6690
19 6745
20 6585
21 6280
22 5980
23 5660
24 5320
In addition there is a row of numbers that relate to this table that also
changes monthly (it's used to keep track of the market value of a vehicle).
The rows of data look like this:
Aug-07 $18,848
Sep-07 $18,320
Oct-07 $17,792
Nov-07 $17,264
Dec-07 $16,736
1 Jan-08 $16,208
2 Feb-08 $15,680
3 Mar-08 $15,152
4 Apr-08 $14,623
5 May-08 $14,095
6 Jun-08 $13,567
7 Jul-08 $13,039
8 Aug-08 $13,905
9 Sep-08 $12,920
10 Oct-08 $12,015
11 Nov-08 $11,455
12 Dec-08 $11,095
13 Jan-09 $11,060
14 Feb-09 $11,200
15 Mar-09 $11,010
16 Apr-09 $10,580
17 May-09 $10,160
18 Jun-09 $9,705
19 Jul-09 $9,210
20 Aug-09 $8,665
21 Sep-09 $7,985
22 Oct-09 $7,375
23 Nov-09 $6,990
24 Dec-09 $6,730
25 Jan-10 $6,690
26 Feb-10 $6,745
27 Mar-10 $6,585
28 Apr-10 $6,280
29 May-10 $5,980
30 Jun-10 $5,660
31 Jul-10 $5,320
The August 07 amount is when it was put in service. We didn't really start
keeping track until a few months later, which is next to the number 1. There
is another cell that I put a date for the end of the month in, and I have
several other cells that use that date in a vlookup table using the dates you
see above (I've had to turn those formulas into VBA code, but that's another
issue).
Here's the deal. When it's July 2008 a formula is in place to move make
various calculations, and the August 2008 cell should equal the first number
in the first table above (in this case 13,905). In August 2008 I need the
formula to carry down from July, and for September to then equal the first
number in the table, October to equal the second number, etc. Is this
something I can set up in VBA to do? I appreciate the help.