Excel if function

Solved/Closed
PM - Jan 20, 2011 at 04:29 AM
 PM - Jan 20, 2011 at 09:23 AM
Hello,

Could someone please help me with the following problem.

I want to know how to do a if function in vba to search for a value (output), and if it does not find the value then it searches for another value (entry).

I have got the code for the searches but i cant get the if function to work in order without an error occuring. If it doesnt find the word output then query stops.

Maybe a if function is not needed and you have a better solution.

Thanks in advance!

PM


Excel 2003

4 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 20, 2011 at 08:25 AM
Public Function getSearchedCell(sSearchString As String, _ 
                                rngRange As Range, _ 
                                Optional bFirstOccur As Boolean = True) As Range 

   Dim myCell     As Range 
    
   Set getSearchedCell = Nothing 
    
   With rngRange 
      If (bFirstOccur) _ 
      Then 
       
         Set getSearchedCell = .Find(What:=sSearchString, _ 
                               After:=.Cells(1, 1), _ 
                               LookIn:=xlValues, _ 
                               LookAt:=xlWhole, _ 
                               SearchOrder:=xlByRows, _ 
                               SearchDirection:=xlPrevious, _ 
                               MatchCase:=False) 
      Else 
         Set getSearchedCell = .Find(What:=sSearchString, _ 
                               After:=.Cells(.Cells.Count), _ 
                               LookIn:=xlValues, _ 
                               LookAt:=xlWhole, _ 
                               SearchOrder:=xlByRows, _ 
                               SearchDirection:=xlPrevious, _ 
                               MatchCase:=False) 
      End If 
   End With 
    
End Function 

Sub Loss() 

   Dim myCell        As Range 

   Set myCell = getSearchedCell("Output", Cells) _ 

   If Not (myCell Is Nothing) _ 
   Then 
      Workbooks("Losses.xls").Close savechanges:=False 
   Else 
      Set myCell = getSearchedCell("Entry", Cells)
      If Not (myCell Is Nothing) _
      Then
         'found the value
      Else
      
         'did not find the value
      End If
    
   End If 
    
Loss_Exit: 

   Set myCell = Nothing 
    
End Sub
1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 20, 2011 at 06:59 AM
If you could post your code, it would be easier to tell you what approach could help you
0
Here's what i got so far, but i do have more code i need to add given the second IF is true. But i can add that later.

Sub Loss()

If Cells.Find(What:="Output", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate Then

Workbooks("Losses.xls").Close savechanges:=False

Else

If Cells.Find(What:="Entry", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate Then

........(more code)


Endif

End Sub

Let me know what you think!! Maybe iv gone about this all wrong.
0
Thanks Rizvisa!! It works greats
0