|
Finding a date & Selection.End(xlDown)
I have two hopefully easy questions: 1. I want to get a macro to find tomorrow's date in my spreadsheet. I've tried Cells.Find(What:=(FormatDateTime(Date + 1, vbShortDate))).Activate but this returns RTE91. However, after running, it has populated the Find field with the correct date. Please let me know where I've gone wrong. 2. I want to select a range of data (which grows over time) that is 1 column but there are always 3 blank rows in it. I tried the following: Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select which is what's recorded when I take the actions. However, the procedure effectively only runs the first line and then doesn't extend the selection further. Once corrected, I will loop the same line 4 times. I'm using Excel 2003. Many thanks for your help. Clive (new to VBA) -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
Try to use this method:
Sub tomorrow() On Error GoTo Nohit Cells.Find(What:=Date + 1).Activate On Error GoTo 0 Exit Sub Nohit: 'Take measures if not found! MsgBox "tomorrow's date not found" End Sub Regards, Stefi €˛Clivey_UK€¯ ezt Ć*rta: I have two hopefully easy questions: 1. I want to get a macro to find tomorrow's date in my spreadsheet. I've tried Cells.Find(What:=(FormatDateTime(Date + 1, vbShortDate))).Activate but this returns RTE91. However, after running, it has populated the Find field with the correct date. Please let me know where I've gone wrong. 2. I want to select a range of data (which grows over time) that is 1 column but there are always 3 blank rows in it. I tried the following: Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select which is what's recorded when I take the actions. However, the procedure effectively only runs the first line and then doesn't extend the selection further. Once corrected, I will loop the same line 4 times. I'm using Excel 2003. Many thanks for your help. Clive (new to VBA) -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
Please give an example for case 2!
Stefi €˛Clivey_UK€¯ ezt Ć*rta: I have two hopefully easy questions: 1. I want to get a macro to find tomorrow's date in my spreadsheet. I've tried Cells.Find(What:=(FormatDateTime(Date + 1, vbShortDate))).Activate but this returns RTE91. However, after running, it has populated the Find field with the correct date. Please let me know where I've gone wrong. 2. I want to select a range of data (which grows over time) that is 1 column but there are always 3 blank rows in it. I tried the following: Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select which is what's recorded when I take the actions. However, the procedure effectively only runs the first line and then doesn't extend the selection further. Once corrected, I will loop the same line 4 times. I'm using Excel 2003. Many thanks for your help. Clive (new to VBA) -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
Stefi, 1. Many thanks. Your Tomorrow Sub worked a treat and I see where I was going wrong now. Also handy to have the On Error code. 2. Here's an example. If I have from A1 to A13 the following: Column A Row 1: Data Row 2: Row 3: Data Row 4: Data Row 5: Data Row 6: Data Row 7: Data Row 8: Row 9: Row 10: Data Row 11: Row 12: Row 13: Data Starting at A1, I want to do the equivalent of Ctrl Shift Down-Arrow four times which selects in the example above A1:A13. Over time the 2nd section of data (currently A3:A7) will grow in height so I can't always select A1:A13. There may sometimes be data below A13 (separated by more blanks) and I don't want that in the selection. Hope that makes it clearer. :) Clive Stefi Wrote: Try to use this method: Sub tomorrow() On Error GoTo Nohit Cells.Find(What:=Date + 1).Activate On Error GoTo 0 Exit Sub Nohit: 'Take measures if not found! MsgBox "tomorrow's date not found" End Sub Regards, Stefi Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 [/color] -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
set rng = Range("A1",cells(rows.count,1).End(xlup))
rng.Select -- Regards, Tom Ogilvy "Clivey_UK" wrote: Stefi, 1. Many thanks. Your Tomorrow Sub worked a treat and I see where I was going wrong now. Also handy to have the On Error code. 2. Here's an example. If I have from A1 to A13 the following: Column A Row 1: Data Row 2: Row 3: Data Row 4: Data Row 5: Data Row 6: Data Row 7: Data Row 8: Row 9: Row 10: Data Row 11: Row 12: Row 13: Data Starting at A1, I want to do the equivalent of Ctrl Shift Down-Arrow four times which selects in the example above A1:A13. Over time the 2nd section of data (currently A3:A7) will grow in height so I can't always select A1:A13. There may sometimes be data below A13 (separated by more blanks) and I don't want that in the selection. Hope that makes it clearer. :) Clive Stefi Wrote: Try to use this method: Sub tomorrow() On Error GoTo Nohit Cells.Find(What:=Date + 1).Activate On Error GoTo 0 Exit Sub Nohit: 'Take measures if not found! MsgBox "tomorrow's date not found" End Sub Regards, Stefi Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 [/color] |
Finding a date & Selection.End(xlDown)
Question #2:
Range(Selection, Cells(Rows.Count,Selection.Column).End(xlUp)).Sele ct HTH -- AP "Clivey_UK" a écrit dans le message de ... I have two hopefully easy questions: 1. I want to get a macro to find tomorrow's date in my spreadsheet. I've tried Cells.Find(What:=(FormatDateTime(Date + 1, vbShortDate))).Activate but this returns RTE91. However, after running, it has populated the Find field with the correct date. Please let me know where I've gone wrong. 2. I want to select a range of data (which grows over time) that is 1 column but there are always 3 blank rows in it. I tried the following: Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select which is what's recorded when I take the actions. However, the procedure effectively only runs the first line and then doesn't extend the selection further. Once corrected, I will loop the same line 4 times. I'm using Excel 2003. Many thanks for your help. Clive (new to VBA) -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
Many thanks Tom & Ardus. That was extremely helpful and I now know a new way to select data. One problem I have in this particular case is that in some cases there is data below the range I want to select (separated by blanks) and so I don't always won't to go to the bottom cell in that column. I just need the macro equivalent of Ctrl-Shift-DownArrow 4 times from row1. Any ideas? Thanks again. Clive Ardus Petus Wrote: Question #2: Range(Selection, Cells(Rows.Count,Selection.Column).End(xlUp)).Sele ct HTH -- AP [/color] -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
Sub test()
Range(Range("A1"), Range("A1") _ .End(xlDown) _ .End(xlDown) _ .End(xlDown) _ .End(xlDown) _ ).Select End Sub HTH -- AP "Clivey_UK" a écrit dans le message de ... Many thanks Tom & Ardus. That was extremely helpful and I now know a new way to select data. One problem I have in this particular case is that in some cases there is data below the range I want to select (separated by blanks) and so I don't always won't to go to the bottom cell in that column. I just need the macro equivalent of Ctrl-Shift-DownArrow 4 times from row1. Any ideas? Thanks again. Clive Ardus Petus Wrote: Question #2: Range(Selection, Cells(Rows.Count,Selection.Column).End(xlUp)).Sele ct HTH -- AP -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile:[/color] http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
Finding a date & Selection.End(xlDown)
AP; that's perfect. Many thanks. It's exactly what I needed. Clive Ardus Petus Wrote: Sub test() Range(Range("A1"), Range("A1") _ .End(xlDown) _ .End(xlDown) _ .End(xlDown) _ .End(xlDown) _ ).Select End Sub HTH -- AP [/color] -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=536312 |
| All times are GMT +1. The time now is 09:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com