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 in a column before date column (column C)but the values ar
exactly adjacent to the date values. For example if the starting dat
in column B was 01-march-04 and ending date was 28 march 04 then th
counter will start in coulmn b as in column C contains date values
while generating an auto number list it also adds 56 more row
backwards to it hence the total count becomes 1 to 84. now there is
problem, the prblem is: when i add another 84 days to the column B, th
count starts from 85 and goes till 168. I can not skip a row. pleas
help me out. followig is the piece of code for making the picture mor
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

It doesn't look like you are skipping a row from the
looks of what you're saying here, but if that is indeed
the case you may have to subtract 1 from something in an
if statement.
-----Original Message-----
I have a macro that takes an input depending on two
dates, start date
and end date and markes that area as rng. it also
generates an auto
count in a column before date column (column C)but the
values are
exactly adjacent to the date values. For example if the
starting date
in column B was 01-march-04 and ending date was 28 march
04 then the
counter will start in coulmn b as in column C contains
date values.
while generating an auto number list it also adds 56
more rows
backwards to it hence the total count becomes 1 to 84.
now there is a
problem, the prblem is: when i add another 84 days to
the column B, the
count starts from 85 and goes till 168. I can not skip a
row. please
help me out. followig is the piece of code for making
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,

darno


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

.