Prev: Accessing the call stack in an Excel VBA macro?
Next: Separate code from workbook?!
From: GS on 4 Jun 2010 13:21
Howard Brazee explained on 6/4/2010 :
> 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.

Try this...

Copy this sub into a module:

Sub InterpolateMonthlyValues(YearsToDo As String)
Dim i As Long, r As Long
Dim s1 As String, s2 As String, f1 As String, f2 As String, f3 As
String
Dim vYrs As Variant

r = 1
f1 = "=month(text(row()-"
f2 = "&""/1/""&D$"
f3 = ",""mm/dd/yyyy""))"

vYrs = Split(YearsToDo, ",")
For i = LBound(vYrs) To UBound(vYrs)
s1 = f1 & CStr(r) & f2 & CStr(r) & f3
s2 = "D" & CStr(r)
With Range(s2)
.value = vYrs(i)
With .Offset(1).Resize(12)
.Formula = s1
' .value = .value 'convert to constants if desired
End With
End With
'Insert 1 blank row between groupings
r = r + 14 'spacing: adjust to suit
Next
End Sub

Enter this in the Immediate Window:
InterpolateMonthlyValues "2007,2008,2009,2010"

Hopefully, you'll be able to modify it to suit your need if it's not
exactly what you want 'as is'.

regards,

--
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 18:08
On Fri, 04 Jun 2010 13:21:50 -0400, GS <gesansom(a)netscape.net> wrote:

>Hopefully, you'll be able to modify it to suit your need if it's not
>exactly what you want 'as is'.

Interesting. That will be fun to play with when I have time. Thanks.

--
"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  | 
Pages: 1 2 3
Prev: Accessing the call stack in an Excel VBA macro?
Next: Separate code from workbook?!