My query needs an extension to the following Macro:

Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng As Range
Dim cng As Range

sStart = InputBox("Enter Start Date")
sEnd = InputBox("Enter End Date")

If IsDate(sStart) And IsDate(sEnd) Then
res = Application.Match(CLng(CDate(sStart)), Range("B1:B800"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("B1:B800"), 0)
If Not IsError(res) And Not IsError(res1) Then
Set rng = Range(Range("B1:B800")(res), Range("B1:B800")(res1))
rng.Resize(, 31).BorderAround Weight:=xlMedium, ColorIndex:=3
Set rng = rng(1, 1).Offset(0, -1).Resize(rng.Count)
rng(1, 1).FormulaR1C1 = "=R[-1]C+1"
rng(1, 1).AutoFill rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
End If
End Sub


In this Macro what further I look for is a formula that can be copie
into any range dependent column. As this macro uses rng and do an aut
count in a column before date column. what I am looking for is a piec
of code that could fill up a sum formula in column H. Lets say if th
first input had a date which was in column B cell B2 (12-may-2002) an
the second input had a date that was in same column B but in cell B30
in this case what I need is to copy a formula in column E (from E1 t
E29), it could be any range as it is dependent on inputs. The formul
should do a sum of column D values, column g contains only 1 in eac
row .so from D1 to D800 there are just 1s (Ones) only, what I need thi
sum formula to calculate values with an increment in row. For exampl
the value in D1 was 1 (one) so the formula in E1 should show 1 a
result and if the value in D2 was 1 again the formula in E2 should sho
2 and if the value in D3 was 1 again then E3 should show 3 and it wil
keep on going until the each column cell is not filled with result.

Column A Column B ColumnC Column D Column E

1 12-may-03 r 1 SUM(D1)
2 13-may-03 r 1 SUM(D1:D2)
3 14-may-03 r 1 SUM(D1:D3)

Regards,

Darn

--
Message posted from http://www.ExcelForum.com