Using For...Next with date variables...

[chazrab]

Can ANYONE tell me why this code does not work ? I get an error 13, type mismatch errorcode on the For i code line:

Private Sub Runreport_Click()
Dim Searchval, Sdate, Edate As Date, myrange, finddesval As Range, ws, wx As Worksheet
Set ws = Worksheets("BILLS")
Set myrange = ws.Range("L2:L777")
myrange.Select
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)
MsgBox ("The value of finddesval is" & finddesval)

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

Comments: COL L is a date col. Just loop between any two date values anywhere down the COL from two userform textboxes and initially use MsgBox to identify each value found as the code above should do (Will substitude more complicated code in place of MsgBox if someone can get this to work for me.

Thanks, CR

 

[mikerickson]

Sdate is type Variant holding a string variable.
Edate is type Date

 

[chazrab]

Mike, thanks, the For...Next loop now works. Will you please help me with one last thing - why do I get 'run time errror code 424 'object required' when I use i to make assignments to selected cells of the rows of the dates found between the two date values in the For loop, substituting this code in place of MsgBox:

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

Comments: Obviously, each i is not the variable representing the next date found in the For Loop on that row.

What am I doing wrong?

Thanks for your help. This is all I need to make this work.

CR

 

[Chris Bode]

Set the data type of variant variable to date…like set Sdate as Date data type instead of variant..further declare I as date to before being used in the for loop…also, if the text box of user form contains date value then first cast it to date before assigning to a date variable with CDate() function

 

[RoryA]

i is a Date variable not a Range so you can't use Offset with it (how would you offset two columns to the right of a date? ) It's not clear to me what that code is supposed to achieve, though.

 

[chazrab]

Rory, thanks for the reply. It now works since I used Sdate As a Variant type and Edate as a Date type. What this code is ultimately supposed to do:

Take two dates from two userform textboxes and list the cell values of each row of COLS J-M of the dates found on a sheet named REPORT. This is the code that does not work when I try to do this for each i date value found.

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

I get an 'error 424 object required' at the first line above

 d = i.Offset(0, -2).Value

Obviously I can't use i as a variable to assign values to. I did have

With wx
       Dim d, c, da, a, v
           d = ActiveCell.Offset(0, -2).Value
           c = ActiveCell.Offset(0, -1).Value
           da = ActiveCell.Offset(0, 0).Value
           a = ActiveCell.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

but this puts the first value at the top of the COL range as the ActiveCell, NOT the row of the first date value found. I hope I have explained this well enough to understand what I need.

Can you help me with this?

Thanks,
CR

 

[RoryA]

It's still not clear to me. Are you trying to find each date value between sdate and edate in column L? (Currently you appear to be finding sdate and then just looping between sdate and edate but not actually trying to find anything).

 

[chazrab]

Yes - find each value between Sdate and Edate in COL L and list values in adjacent cells J and K and M and N on another sheet.

I want to do this: If Sdate = 2/2/2009 and Edate = 2/4/2009

1) find Sdate
2) then list all the rows from Sdate to Edate on another sheet.
Here is the scenario:

  J         K            L         M        N
  
Apples     Red        2/1/2009  Autodeb   $1.00
Oranges    Orange     2/2/2009  Autodeb    2.00
Bananas    Yellow     2/2/2009  Autodeb    3.00
Pears      Green      2/3/2009  Autodeb    4.00
Figs       Brown      2/4/2009  Autodeb    5.00 
Raisins    Purple     2/5/2009  Autodeb    6.00

If the For Loop or find code is correct, the result should be

SHEET1

   A          B          C         D 
Oranges    Orange     2/2/2009  Autodeb   2.00
Bananas    Yellow     2/2/2009  Autodeb   3.00
Pears      Green      2/3/2009  Autodeb   4.00
Figs       Brown      2/4/2009  Autodeb   5.00

Comments:
1. cells.Find would find the first value, Sdate = 2/2/2009
2. The For Loop would assign variables to the cell values of J, K, L and M of each row found until Edate is reached = 2/4/2009 and place the subset
on SHEET 1 in this case in COLS A-D

I am having trouble getting the code between the For Loop to give the result on SHEET1. Trying to do this does not work, as it generates a 424 error object required:

For i = Sdate To Edate
ActiveCell.Select
Set wx = Worksheets("SHEET1")
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  'value in COL J
'           c = i.Offset(0, -1).Value 'value in COL K
'           da = i.Offset(0, 0).Value 'date value in COL L
'           a = i.Offset(0, 1).Value  'value in COL M 
'           v = i.Offset(0, 2).Value  'value in COL N
           .Cells(lRow, 1).Value = d   'value from COL J
'           .Cells(lRow, 2).Value = c  'value from COL K
'           .Cells(lRow, 3).Value = da 'date value from COL L
'           .Cells(lRow, 4).Value = a  'value from COL M
'           .Cells(lRow, 5).Value = v  'value from COL N
    End With
Next i

What can I use in place of i to store the values, since assiging i does not
work?

I tried to make this as clear as possible. Hope you can see what I want
the code to do.

Thank you for helping me with this.

CR

 

[RoryA]

You could try using a filter, assuming you have headers in row 1:

Private Sub RunReport_Click()
   Dim Sdate As String, Edate As String, myrange As Range, ws As Worksheet, wx As Worksheet
   Dim lngCount As Long, lRow As Long
   Dim rngCell As Range
   Set ws = Worksheets("BILLS")
   Set myrange = ws.Range("L1:L777")
   Sdate = Aoe.TextBox1.Value
   Edate = Aoe.TextBox2.Value
   Set wx = Worksheets("SHEET1")
   lRow = wx.Cells(Rows.Count, 1) _
   .End(xlUp).Offset(1, 0).Row
   myrange.AutoFilter field:=1, Criteria1:=">=" & Sdate, Operator:=xlAnd, Criteria2:="<=" & Edate
   Set myrange = myrange.Offset(1, 0).Resize(myrange.Rows.Count - 1)
   On Error Resume Next
   Set myrange = myrange.SpecialCells(xlCellTypeVisible)
   On Error GoTo 0
   If Not myrange Is Nothing Then
      For Each rngCell In myrange
         wx.Cells(lRow, 1).Resize(, 5).Value = rngCell.Offset(0, -2).Resize(, 5).Value
         lRow = lRow + 1
      Next rngCell
   End If
End Sub