Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer variable to user Form | Excel Discussion (Misc queries) | |||
Footer variable for user name? | Excel Programming | |||
Use % as variable for user id? | Excel Programming | |||
User input formula variable | Excel Programming | |||
Calling variable from user form | Excel Programming |