Copying Formula In Rows With Increment...

D

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(D1:D2)
3 14-may-03 r 1 SUM(D1:D3)

Regards,


Darn
 
P

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
---------------------------------
darno > said:
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(D1:D2)
3 14-may-03 r 1 SUM(D1:D3)

Regards,


Darno
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering/Excluding Values 1
VBA Coding Help for Beginner 0
Can any body help? 1
Problem with auto count... 0
Please Help! 1
The last nail... 1
One more step... 0
TOM you made my day... 0

Top