I have a macro that takes an input depending on two dates, start dat
and end date and markes that area as rng. it also generates an aut
count list in a column before Column C that is a date column (colum
C), but the values are exactly adjacent to the date values. For exampl
if the starting date in column C was 01-march-04 and ending date was 2
march 04 then the counter will start in coulmn B as in column
contains date values. while generating an auto number list it also add
56 more rows backwards to it hence the total count becomes 1 to 84. no
there is a problem, the prblem is: when i add another 84 days to th
column B, the count starts from 85 and goes till 168. I can not skip
row. what i wish to have is a way to start the same count from 1 to 8
from row 85 and it must not start from 85 it must start from 1 and goe
till 84. please help me out. followig is the piece of code for makin
the picture more clear for you.


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 + 56)
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




regards,

darn

--
Message posted from http://www.ExcelForum.com