Set range based on cell contents - help required
 

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

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

Set range based on cell contents - help required
 

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/

Set range based on cell contents - help required
 

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/

Set range based on cell contents - help required
 

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/

Set range based on cell contents - help required
 

Many thanks

That did the trick

Kenn

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

Set range based on cell contents - help required
 

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?

--
Regards,
Tom Ogilvy

"N E Body " wrote in message
...
Many thanks

That did the trick

Kenny


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