Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Search Criteria - One works other does not.
Type:
User Input form Problem #1: Multiple search criteria is partially working. AppDate.Value works fine, however the following error comes up when it reaches the second criteria, AppDate.Value. The columns (F & G) are single numbers and are entered via a drop down list via another input form. [F: Month] [G: Day] 7 1 Error: Run-time Error (91) Object variable or With block variable not set. Problem #2: I have yet to find a way to set the row with the found results and the first cell as the active cell to start editing. I can set the offset value easy enough, however the cell number is dynamic and most often will not be the same. Code: Private Sub Search_Click() Dim wks As Worksheet Dim wks2 As Worksheet Dim FoundCell As Range Dim FoundCell2 As Range Set wks = Worksheets("Appointment Log") If Me.ClientNum.Value = "" And Me.AppDate.Value = "" Then MsgBox "You must enter a Client # and Appointment Date it was set for!" Exit Sub End If With wks With .Range("C:D") '*** Columns to look in, unable to set to a single column? ***' Set FoundCell = .Cells.Find(what:=Me.ClientNum.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ Lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) End With End With With wks2 With .Range("F:G") '***Columns to look in.***' Set FoundCell2 = .Cells.Find(what:=Me.AppDate.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ Lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) End With End With If FoundCell And FoundCell2 Is Nothing Then MsgBox "Not found, please try again." Else MsgBox "Found it! Input Fields are now enabled, please enter your results below." DealClosed.Enabled = True '1st Cell to be edited, Column L DealClosed.SetFocus ClientClosed.Enabled = True '2nd Cell to be edited, Column M Showed.Enabled = True '3rd Cell to be edited, Column N Closed.Enabled = True '4th Cell to be edited, Column O Notes.Enabled = True '5th Cell to be edited, Column P FrontGross.Enabled = True '6th Cell to be edited, Column Q BackGross.Enabled = True '7th Cell to be edited, Column R SaveB.Enabled = True '8th Cell to be edited, Column S End If End Sub Thanks in advance for any help provided, it is greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Search Criteria - One works other does not.
Correction:
Problem #1: Multiple search criteria is partially working. ClientNum.Value works fine, however the following error comes up when it reaches the second criteria, AppDate.Value. The columns (F & G) are single numbers and are entered via a drop down list via another input form. (ClientNum.Value is the correction) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple criteria search | Excel Worksheet Functions | |||
Multiple search criteria | Excel Worksheet Functions | |||
Search for multiple criteria, and return yet another | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Search string with multiple criteria | Excel Worksheet Functions |