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

 

[darno]

hI

IN THE FOLLOWING MACRO I NEED A LITTLE MORE HELP. SEE IF YOU CAN HELP?

frm = "=SUM(RC[-12]:R[83]C[-12])"
Set rng = rng(1, 1).Offset(-59, 0).Resize(84)
rng.FormulaR1C1 = frm
End If
End Sub

the problem is that as it copies the formula to 84 rows the sum formul
gets an increment in both starting range and end range i.e.
Sum(a1:b84), next line reads a2:b85, next reads a3:b86... i donot wan
like this. what i am looking for is to keep the first range freez
while the other moving. i.e., Sum($a$1:b84), but we do not know fro
which cell the range shall start and end. Looking for a wayout.

Regards,

Darn

 

[Tom Ogilvy]

frm = "=SUM(R1C[-12]:R84C[-12])"

Would be my best guess at what you want.