Prev: Accessing the call stack in an Excel VBA macro?
Next: Separate code from workbook?!
From: Howard Brazee on 3 Jun 2010 15:48
On Thu, 03 Jun 2010 21:31:40 +0200, Lars-�ke Aspelin
<larske(a)REMOOVEtelia.com> wrote:

>>If the first formula in in cell D2, try this:
>>
>>=D1+(D15-D1)*(ROW(D2)-ROW(D$1))
>>
>>Hope this helps / Lars-�ke
>
>Oops, forgot to write that the formula in D2 can now be copied down
>column D until cell D12.

The trouble is, the (D$1) will need to be manually changed to (D$15)
and so on each time I duplicate the set.

I guess my manual code before is a bit easier to paste for each new
year. No big deal, but I'd sort of like to have it more *coded*, if
you know what I mean. (even if it's more work - it's more fun code!)

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
From: Lars-�ke Aspelin on 3 Jun 2010 16:23
On Thu, 03 Jun 2010 13:48:32 -0600, Howard Brazee <howard(a)brazee.net>
wrote:

>On Thu, 03 Jun 2010 21:31:40 +0200, Lars-�ke Aspelin
><larske(a)REMOOVEtelia.com> wrote:
>
>>>If the first formula in in cell D2, try this:
>>>
>>>=D1+(D15-D1)*(ROW(D2)-ROW(D$1))
>>>
>>>Hope this helps / Lars-�ke
>>
>>Oops, forgot to write that the formula in D2 can now be copied down
>>column D until cell D12.
>
>The trouble is, the (D$1) will need to be manually changed to (D$15)
>and so on each time I duplicate the set.
>
>I guess my manual code before is a bit easier to paste for each new
>year. No big deal, but I'd sort of like to have it more *coded*, if
>you know what I mean. (even if it's more work - it's more fun code!)

Well, the formula I proposed need some corrections.

=D$1+(D$15-D$1)*(ROW(D2)-ROW(D$1))

should work for the first year.

In order to handle subsequent year without manual editing of formula
we have to know where the subssequent year is located.

Lars-�ke
From: Howard Brazee on 3 Jun 2010 18:29
On Thu, 03 Jun 2010 22:23:09 +0200, Lars-�ke Aspelin
<larske(a)REMOOVEtelia.com> wrote:

>Well, the formula I proposed need some corrections.
>
>=D$1+(D$15-D$1)*(ROW(D2)-ROW(D$1))
>
>should work for the first year.
>
>In order to handle subsequent year without manual editing of formula
>we have to know where the subssequent year is located.

I actually simplified my formula for the post. Pick a line. My
example had it in line 15.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
From: GS on 3 Jun 2010 23:13
Howard Brazee used his keyboard to write :
>
> I have a need to calculate a formula that I don't know how to
> reference. The following is the pattern that I could hard code.
> Basically, I have a value for each year, and need to interpolate
> monthly values. Is there a more generic way of entering these
> cells?
>
> =D1 + (D15-D1)*1
> =D1 + (D15-D1)*2
> =D1 + (D15-D1)*3
> =D1 + (D15-D1)*4
> =D1 + (D15-D1)*5
> =D1 + (D15-D1)*6
> =D1 + (D15-D1)*7
> =D1 + (D15-D1)*8
> =D1 + (D15-D1)*9
> =D1 + (D15-D1)*10
> =D1 + (D15-D1)*11
> =D1 + (D15-D1)*12
>
>
> =D30 + (D30-D15)*1
> =D30 + (D30-D15)*2
> =D30 + (D30-D15)*3
> =D30 + (D30-D15)*4
> =D30 + (D30-D15)*5
> =D30 + (D30-D15)*6
> =D30 + (D30-D15)*7
> =D30 + (D30-D15)*8
> =D30 + (D30-D15)*9
> =D30 + (D30-D15)*10
> =D30 + (D30-D15)*11
> =D30 + (D30-D15)*12

Where are you storing the value you have for each year? Are you trying
to generate numbers for each month (1 to 12), or month names (Jan to
Dec)?

A sample of where your year values are stored and the expected results
of the formula would be very helpful!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Howard Brazee on 4 Jun 2010 08:22
On Thu, 03 Jun 2010 23:13:58 -0400, GS <gesansom(a)netscape.net> wrote:

>Where are you storing the value you have for each year? Are you trying
>to generate numbers for each month (1 to 12), or month names (Jan to
>Dec)?
>
>A sample of where your year values are stored and the expected results
>of the formula would be very helpful!

I'm off today and the sheet is at work. I do some manipulation of
date functions to display the date. I would have to do some other
manipulation to get it back.

I simplified what I'm doing for this thread. Make any assumptions
about date that would illustrate your solution.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Accessing the call stack in an Excel VBA macro?
Next: Separate code from workbook?!