Set range based on cell contents - help required

[N E Body]

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 th
sheet.
I know to write >N196 for the start date and < N197 for the end date t
avoid problems if the actual date is not in the list. However how woul
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 200

 

[Norman Jones]

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

 

[Tom Ogilvy]

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

should work with your code.

 

[Norman Jones]

Hi N E,

That Should have been:

If rCell.Value > Range("N196").Value And rCell.Value< Range"("N197").Value
Then

---
Regards,
norman

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

 

[N E Body]

Many thanks

That did the trick

Kenn

 

[Tom Ogilvy]

Range("N196") and Range("N197") are on the new sheet you just added where
you have added no values? Or is the code in the sheet module of the sheet
with the Dates?