Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with auto count...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with Auto-Filter On | Excel Discussion (Misc queries) | |||
Auto filter count | Excel Discussion (Misc queries) | |||
Auto Count for Auto Filter Doesn't Function under certain circumst | Excel Discussion (Misc queries) | |||
Count down with auto fill? | Excel Worksheet Functions | |||
auto count how many Apple | Excel Discussion (Misc queries) |