Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more step...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Need step by step for entering data into combobox | Excel Discussion (Misc queries) | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |