If rCell.Value = wsCurr.Range("N196").value and _
rCell.Value <= wsCurr.Range("N197").value Then

should work with your code.

--
Regards,
Tom Ogilvy

"N E Body " wrote in message
...
Hello all

My column contains dates (in order but some dates are missing)
I want to specify a range in VBA based on two cells elsewhere on the
sheet.
I know to write N196 for the start date and < N197 for the end date to
avoid problems if the actual date is not in the list. However how would
I fit this into my code.

Code below

Sub MyBetweenDates()
Dim x As Long
Dim lLastrow As Long
Dim wsNew As Worksheet, wsCurr As Worksheet
Dim rCell As Range
Dim vContents As Variant

Set wsCurr = Sheets("Data")
Set wsNew = Worksheets.Add
wsNew.Name = "Dates"
lLastrow = wsCurr.Range("A65536").End(xlUp).Row

x = 1

For Each rCell In wsCurr.Range("C2:C" & lLastrow)

'the next line is the problemxxxxxxxxxxxxxxxxxxxxxxxxxx

If rCell.Value Range("N196").value and<"Range"("N197").value Then

'the above line is the problemxxxxxxxxxxxxxxxxxxxxxxxxx

vContents = wsCurr.Range(rCell.Offset(0, -2), rCell.Offset(0,
12)).Value
wsNew.Range("A" & x & ":O" & x).Value = vContents
x = x + 1
End If
Next rCell


End Sub


Can anyone help?

TIA
Kenny

using combinations of Win2000, Win Me, Office 97 and Office 2000


---
Message posted from http://www.ExcelForum.com/