Hi N E,
That Should have been:
If rCell.Value Range("N196").Value And rCell.Value< Range"("N197").Value
Then
---
Regards,
norman
"Norman Jones" wrote in message
...
Hi N E,
Change:
If rCell.Value Range("N196").value and<"Range"("N197").value Then
to:
If rCell.Value Range("N196").value And Rcell < Range"("N197").value Then
---
Regards,
Norman
"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/