Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to Named Range Based On Cell Contents | Excel Worksheet Functions | |||
Return entries from one named range based on the contents of another | Excel Worksheet Functions | |||
Reference Data Range based on cell contents | Charts and Charting in Excel | |||
Formatting a range based on contents of column/row heading | Excel Programming | |||
Formatting a range based on contents of column/row heading | Excel Programming |