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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
588
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:
Code:
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:
Code:
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top