Can any body help?

[darno]

Hi,

Following Macro was written to me by TOM, it worked great, I have t
make few modifications in this macro. As this macros asks for tw
inputs and highlight the required range area, I also would like to se
an auto generated number list from 1 to any number (depending on th
2nd input) in Column A, exactly next to Date cell, i.e., if the firs
date that I entered as an input was 01-May-2003 and was stored in cel
B26 and the second date (ending date) was 10-may-2003 and was stored i
Cell B35, now the total rows involved between these two dates are 10
now what I need is a counting from One to Ten but the counting mus
start in column A26 which should be adjacent to forst input cell i.e.
A26 and the last number of counting should be next to the end dat
i.e., A35. This list generating procedure must be dependent on firs
date input and second date input.



RESULT DATE COLUMN
ColumnA ColumnB
1 01-May-03
2 02-May-03
3 03-May-03
4 04-May-03
5 05-May-03
6 06-May-03
7 07-May-03
8 08-May-03
9 09-May-03
10 10-May-03


Regards,

DARNO



Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng 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
End If
End If
End Su

 

[Bob Phillips]

Darno,

Is this what you mean

Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng 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)
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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)