Any real knowledgable MVP's out there ??.?..this is one very hard to solve VBA solution...

[chazrab]

The input is Userform Aoe with two TextBoxes, TextBox1 and TextBox2
The range is COL L of sheet BILLS, a Date col formatted mm/dd/yyyy
The following code is what I want to use to find all values between and including two dates Sdate = TextBox1.Value and Edate = TextBox2.Value:

Private Sub REPORT_Click()
Dim Searchval, Sdate As Variant, Edate As Date, myrange, finddesval As Range, ws, wx As Worksheet
Set ws = Worksheets("BILLS")
Set myrange = ws.Range("L2:L777")
Searchval = Aoe.TextBox1.Value
Sdate = Aoe.TextBox1.Value
Edate = Aoe.TextBox2.Value
Set finddesval = ws.Cells.Find(What:=Searchval, After:=[l1], LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

For i = Sdate To Edate
    MsgBox ("This value of i is" & i)

'Set wx = Worksheets("REPORT")
'lRow = wx.Cells(Rows.Count, 1) _
'.End(xlUp).Offset(1, 0).Row
'    With wx
'       Dim d, c, da, a, v
'           d = i.Offset(0, -2).Value
'           c = i.Offset(0, -1).Value
'           da = i.Offset(0, 0).Value
'           a = i.Offset(0, 1).Value
'           v = i.Offset(0, 2).Value
'           .Cells(lRow, 1).Value = d
'           .Cells(lRow, 2).Value = c
'           .Cells(lRow, 3).Value = da
'           .Cells(lRow, 4).Value = a
'           .Cells(lRow, 5).Value = v
'    End With
Next i
End Sub

Comments:
I used MsgBox to tell me that the For...Each...Next loop does find the values between and including Sdate and Edate correctly - that works.

The commented out section does not work because i it seems to me is just a counter and cannot be used as above to make assignments to variables.

All I want done is to select the value of COLS J, K, L, M and N between and including Sdate and Edate rows and list them on a sheet named REPORT.

COL L grows and now starts with 01/01/2009 and ends with 03/10/2009 as dates are appended with other VBA code in the main application

The result should look like this when
Sdate = 02/01/2009 and Edate = 02/08/2009:

Sheet REPORT

Kroger        Food     02/01/2009   Autodebit   $30.00
MW Cleaners   Cleaning 02/03/20009  Autodebit     5.73
BOA           Ser Chg  02/04/2009   Autodebit     4.50
Hebert's      Food     02/07/2009   Autodebit    50.00
Domino's      Food     02/08/2009   Autodebit    28.00

Why has this been so difficult to achieve ?

Can ANYONE with top MVP knowledge show me how to assign variables in the For...Each block to achieve this result?

Thanks for all your help.

CR

 

[Fazza]

Hi,

What about a non-code approach? Like http://www.dailydoseofexcel.com/archives/2004/12/13/

HTH, Fazza