Finding a date & Selection.End(xlDown)

[Clivey_UK]

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)

 

[Stefi]

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:

 

[Stefi]

Please give an example for case 2!
Stefi


„Clivey_UK†ezt írta:

 

[Clivey_UK]

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

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

[/QUOTE]

 

[Tom Ogilvy]

set rng = Range("A1",cells(rows.count,1).End(xlup))
rng.Select

--
Regards,
Tom Ogilvy

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

[/QUOTE]

 

[Ardus Petus]

Question #2:

Range(Selection, Cells(Rows.Count,Selection.Column).End(xlUp)).Select

HTH
--
AP

"Clivey_UK" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[Clivey_UK]

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

Question #2:

Range(Selection, Cells(Rows.Count,Selection.Column).End(xlUp)).Select

HTH

[/QUOTE][/QUOTE]

 

[Ardus Petus]

Sub test()
Range(Range("A1"), Range("A1") _
.End(xlDown) _
.End(xlDown) _
.End(xlDown) _
.End(xlDown) _
).Select
End Sub

HTH
--
AP

"Clivey_UK" <[email protected]> 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

[/QUOTE]
[/QUOTE]

 

[Clivey_UK]

AP; that's perfect. Many thanks. It's exactly what I needed.
Clive

Sub test()
Range(Range("A1"), Range("A1") _
.End(xlDown) _
.End(xlDown) _
.End(xlDown) _
.End(xlDown) _
).Select
End Sub

HTH

[/QUOTE][/QUOTE]