Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find with a variable
Having a few problems with my syntax and would appreciate any help.
I want to write a macro that will find any date I specify in my spreadsheet, I can enter the date variable using InputBox and I can do a specific find with the Find command but I don't know how to pass the date variable entered into the InputBox into the Find command. -- Regards, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find with a variable
Hi , Chris
Range object has a Find method. That's what you manually use the Find dialogbox. Inputbox returns a string that you entered, you can pass the string to the Find methd. str=InputBox(message,title) If Len(str)=0 Then Exit Sub With Worksheets(1).Range("a1:a500") Set c = .Find(str, lookin:=xlValues) If Not c Is Nothing Then ' Do your Processing End If End With Best Regards, sjoo. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find with a variable
Hi , Chris
Range object has a Find method. That's what you manually use the Find dialogbox. Inputbox returns a string that you entered, you can pass the string to the Find methd. str=InputBox(message,title) If Len(str)=0 Then Exit Sub With Worksheets(1).Range("a1:a500") Set c = .Find(str, lookin:=xlValues) If Not c Is Nothing Then ' Do your Processing End If End With Best Regards, sjoo. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find with a variable
Sometimes VBA and dates don't play nice:
Option Explicit Sub testme01() Dim myDate As String Dim FoundCell As Range Dim RngToSearch As Range myDate = InputBox(Prompt:="enter a date") If myDate = "" Then Exit Sub End If If Year(CDate(myDate)) < 1990 _ Or Year(CDate(myDate)) 2010 Then MsgBox "Please enter a nice date" Exit Sub End If With Worksheets("sheet1") Set RngToSearch = .UsedRange With RngToSearch Set FoundCell = .Cells.Find(what:=CDate(myDate), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) End With End With If FoundCell Is Nothing Then MsgBox Format(myDate, "mmmm dd, yyyy") & " was not found" Else MsgBox "Found it: " & FoundCell.Address(0, 0) End If End Sub If this doesn't work, you could try changing: ..Cells.Find(what:=CDate(myDate), _ to ..Cells.Find(what:=clng(CDate(myDate)), _ Chris wrote: Having a few problems with my syntax and would appreciate any help. I want to write a macro that will find any date I specify in my spreadsheet, I can enter the date variable using InputBox and I can do a specific find with the Find command but I don't know how to pass the date variable entered into the InputBox into the Find command. -- Regards, Chris -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Find | Excel Discussion (Misc queries) | |||
Find Max from Variable Range | Excel Worksheet Functions | |||
Cells.Find with a variable | Excel Discussion (Misc queries) | |||
Find a variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |