variable given by user
 

Hi!

How can i programatically select cells that correspond to user input data
(such as date).
Ie, i want to ask a user a date and then programatically select all rows
that have that date value at column A.

Thanks in advance.

variable given by user
 

This should be close...

Sub FindDates()
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet
Dim strFirstAddress As String
Dim dtmToFind As Date

On Error Resume Next
dtmToFind = CDate(InputBox("Enter the Date to Find"))
On Error GoTo 0
If dtmToFind = 0 Then Exit Sub
Set wksToSearch = Sheets("Sheet2")
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=dtmToFind, _
LookIn:=xlFormulas)
If rngFound Is Nothing Then
MsgBox "Sorry. Could not find your date"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
wksToSearch.Select
rngFoundAll.Select
End If
End Sub

--
HTH...

Jim Thomlinson


"Alberto Pinto" wrote:

Hi!

How can i programatically select cells that correspond to user input data
(such as date).
Ie, i want to ask a user a date and then programatically select all rows
that have that date value at column A.

Thanks in advance.

variable given by user
 

Try:

Sub demo()
Dim d As Date, r As Range
d = InputBox("Enter date: ")
Set r = Nothing
For i = 1 To 65536
If Intersect(Cells(i, 1), ActiveSheet.UsedRange) Is Nothing Then
Exit For
End If
If Cells(i, 1).Value = d Then
If r Is Nothing Then
Set r = Cells(i, 1).EntireRow
Else
Set r = Union(r, Cells(i, 1).EntireRow)
End If
End If
Next
r.Select
End Sub
--
Gary's Student


"Alberto Pinto" wrote:

Hi!

How can i programatically select cells that correspond to user input data
(such as date).
Ie, i want to ask a user a date and then programatically select all rows
that have that date value at column A.

Thanks in advance.

variable given by user
 

I hate to nit pick but... if your first row is not populated then it is not
part of the used range and the code exits the for loop (before it even gets
to the populated data). Additionally if no matching dates are found then the
Select line at the end will throw an error... There is every possibility that
this does not make one bit of difference but it is worth noting...
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try:

Sub demo()
Dim d As Date, r As Range
d = InputBox("Enter date: ")
Set r = Nothing
For i = 1 To 65536
If Intersect(Cells(i, 1), ActiveSheet.UsedRange) Is Nothing Then
Exit For
End If
If Cells(i, 1).Value = d Then
If r Is Nothing Then
Set r = Cells(i, 1).EntireRow
Else
Set r = Union(r, Cells(i, 1).EntireRow)
End If
End If
Next
r.Select
End Sub
--
Gary's Student


"Alberto Pinto" wrote:

Hi!

How can i programatically select cells that correspond to user input data
(such as date).
Ie, i want to ask a user a date and then programatically select all rows
that have that date value at column A.

Thanks in advance.