Copying Formula In Rows With Increment...

[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 Column A before date column B.

what I am looking for is a piece of code that could fill up a su
formula in column E. Lets say if the first input had a date which wa
in column B cell B2 (12-may-2003) and the second input had a date tha
was in same column B but in cell B30 . in this case what I need is t
copy a formula in column E (from E1 to E29), it could be any range a
it is dependent on inputs. The formula should do a sum of column
values, column G contains only 1 in each row .so from D1 to D800 ther
are just 1s (Ones) only, what I need this sum formula to calculat
values with an increment in row. For example the value in D1 was
(one) so the formula in E1 should show 1 as result and if the value i
D2 was 1 again the formula in E2 should show 2 and if the value in D
was 1 again then E3 should show 3 and if the value in D4 was 0 (zero
then E4 should show 3 as a result it will keep on going until the eac
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

 

[Patrick Molloy]

I am not sure that I really understand the question. But try this

Say row1 is the row where the first date is and row2 is where the second date is
A formula that would sum incrementally would for exampel:

row C(Formula) D(Data)
res1 27 10 10
28 40 30
29 40 0
30 55 15
res2 31 62 7

where the formula is in D

With Range(Cells(res1, "C"), Cells(res2, "C"))

.FormulaR1C1 = "=SUM(R" & res1 & "C4:RC4)"

End With

this basicaly "locks" the first cell in the sum
so where row1 is 27
.....SUM(R" & res1 & "C4 becomes SUM(R27C4
the formala would look like = SUM(R27C4:RC)


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------

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 Column A before date column B.

what I am looking for is a piece of code that could fill up a sum
formula in column E. Lets say if the first input had a date which was
in column B cell B2 (12-may-2003) 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 if the value in D4 was 0 (zero)
then E4 should show 3 as a result 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