The last nail...

[darno]

Please read the following Macro and see if you can help me. (finger
crossed)


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

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

mum = res - res + 1

if mum = 28 then

Set rng = rng(1,1).offset(-56, -2).Resize(rng.Count + 56)
rng(1, 1).FormulaR1C1 = "=R[-1]C+1"
rng(1, 1).AutoFill rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Else

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 if

if mum = 28 then

res = Application.Match(CLng(CDate(sStart)), Range("E1:E800"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("E1:E800"), 0)
Set rng = Range(Range("Ai4:Ai800")(res), Range("Ai4:Ai800")(res1))

' <<<<<<<< "MY PROBLEM STARTS FROM HERE">>>>>>>>>

Set rng = rng(1, 1).Offset(-59, 0)
rng(1, 1).FormulaR1C1 = "=SUM(RC[-10]:R[83]C[-10])"
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End If
End Sub


You have read that macro above, in this macro where it says "MY PROBLE
STARTS FROM HERE" i need a piece of code that could copy the su
formula (written above) into the defined range. I have noticed that i
brings the cursor to the right point and then starts copying the firs
cell that holds the above formula and then i can not stretch that cop
cell to the required range so that the formula could be copied in t
the 84 rows in column AI. Please help me out. This is the final thing
need from this program to do.


Regards,

Darn

 

[Tom Ogilvy]

mum = res - res + 1

shouldn't that be

mum = res1 - res + 1

Possible solution:

Set rng = rng(1, 1).Offset(-59, 0).Resize(84)
rng.FormulaR1C1 = "=SUM(RC[-10]:R[83]C[-10])"
rng.Formula = rng.Value