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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range based on cell contents - help required
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
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 |