Can any body help?

[darno]

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(D12)
3 14-may-03 r 1 SUM(D13)

Regards,

Darn

 

[David McRitchie]

Hi Darno,
The method you are showing using SUM Worksheet Function
is extremely inefficient. rather than using something like
E1: =SUM(D$11)
E2: =SUM(D$12)

E1: =$D1
E2: =OFFSET(D2,-1,0)+D2
which is the same as =E1+D2
except now you can insert and delete rows without having
to rewrite/correct formulas copied with the fill handle.

You can use the fill handle to copy the formula down
http://www.mvps.org/dmcritchie/excel/fillhand.htm

More on use of OFFSET in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset

Why you do not want to use SUM in the fashion that you show
http://www.mvps.org/dmcritchie/excel/slowresp.htm
http://google.com/groups?threadm=ugDxrXayCHA.2592@TK2MSFTNGP10

Your description (column H) does not match your example (column E).
If you still need help with a macro perhaps you can simplify your question
as the part you actually need.

It would be most helpful if when you post a question you used
a subject that describes the question. You should try to find
your own answers by searching Google Newsgroups before
posting. By including a more descriptive subject others who
search the groups after you may find their answers without
having to post. There are probably over 2000 postings per
day during the work week in this newsgroup.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

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 copied
into any range dependent column. As this macro uses rng and do an auto
count in a column before date column. what I am looking for is a piece
of code that could fill up a sum formula in column H. Lets say if the
first input had a date which was in column B cell B2 (12-may-2002) and
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 to
E29), it could be any range as it is dependent on inputs. The formula
should do a sum of column D values, column g contains only 1 in each
row .so from D1 to D800 there are just 1s (Ones) only, what I need this
sum formula to calculate values with an increment in row. For example
the value in D1 was 1 (one) so the formula in E1 should show 1 as
result and if the value in D2 was 1 again the formula in E2 should show
2 and if the value in D3 was 1 again then E3 should show 3 and it will
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(D12)
3 14-may-03 r 1 SUM(D13)

Regards,

Darno