how to handle large database

[thisisrahul]

hi,
i have a database in which in column A i have dates starting from 1st Jan 07 to 31st March 09. In coulmn B i have some random numbers. What i want is to have every month's Start and End date and to have relative value from column B. The output will be in column C having two numbers for each month (one for Start and one for End0.

any idea is welcome.



_____________
Rahul

 

[venkat1926]

keep a copy of the database safely so that the original file is not mesed up.
the row 1 is having headings
the dates start from A2 down and data from B2 down
now try this macro. afterward check the values in column C
post feedback

Sub test()
Dim rng As Range, bbeg
Dim beg, eend, dest As Range, cfind As Range

 beg = Range("a2").Value
line2:


'MsgBox beg
 eend = CDate(Month(beg) + 1 & "/" & 1 & "/" & Year(beg))
 eend = eend - 1
 
 
 'MsgBox eend
With Columns("a:a")
Set cfind = .Cells.Find(what:=beg, lookat:=xlWhole)

'MsgBox cfind.Address
Set dest = Cells(Rows.Count, "c").End(xlUp).Offset(1, 0)
dest = cfind.Offset(0, 1)
Set cfind = .Cells.Find(what:=eend, lookat:=xlWhole)
'Set cfind = .Cells.Find(what:=bbeg, lookat:=xlWhole)
Set dest = Cells(Rows.Count, "c").End(xlUp).Offset(1, 0)
On Error GoTo line1
dest = cfind.Offset(0, 1)
'Set beg = eend + 1
If beg = "" Then GoTo line1
If Month(beg) = 12 Then
beg = CDate("1/" & "1/" & Year(eend) + 1)
GoTo line2
End If

 beg = CDate(eend + 1)


GoTo line2
'MsgBox beg
End With
line1:
End Sub