Excel if functionSolved/Closed

Question

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

Configuration: Windows XP / Internet Explorer 6.0

rizvisa1 · Answer

If you could post your code, it would be easier to tell you what approach could help you

PM · Answer

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.

rizvisa1 · Answer

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

PM · Answer

Thanks Rizvisa!! It works greats

Excel if function

4 responses

Similar discussions

Subscribe To Our Newsletter!