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

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

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

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

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]

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

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

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

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