Excel if function [Solved/Closed]

Report
-
 PM -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If you could post your code, it would be easier to tell you what approach could help you
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.
Thanks Rizvisa!! It works greats

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!