A Find Function

[MartinVW]

Hey!

I'm currently writing a code but I need to know which range holds a certain. I have the code for the find but Ideally i want to do something like this:

Sub

FindFunction(CertainWord)

Set FoundRange= (I Would like for the code to return the range here so I can set it and continue with my code)

FindFunction(CertainWord)

With Sheets("Output")
Set dFound = .Columns(4).Find(What:=CertainWord, After:=.Cells(1, 4), LookIn:=xlValues, _
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With

End Function

Thank you for the help!

 

[AlphaFrog]

I'm not sure what you're asking. Do you want to pass a range object between two Subs (procedures)?

 

[MartinVW]

I'm sorry. I guess an example is best. Let's say I want to find the text "Systems".

My main sub will first set the value to "Systems"

Then I would like to have a Find Function to search the column on my Output sheet for "Systems" (It will always be there because that is how the sheet is set up)
Once it's found have the function return the range in my main Sub.

Then in my Main sub I can use that range the Find Function has found. And add text underneath the Systems (Such as Windows,MAC)

My problem is I'm not sure about how to get the fund function to return the range into the Main Sub.

Sorry for the confusion.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Main()
    
    [color=darkblue]Dim[/color] rngFoundWord [color=darkblue]As[/color] Range, strCertainWord  [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    strCertainWord = "System"
    
    [color=darkblue]Set[/color] rngFoundWord = FindFunction(strCertainWord)
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rngFoundWord [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=green]'Match found[/color]
        MsgBox rngFoundWord.Address, , "Here it is"
    [color=darkblue]Else[/color]
        [color=green]'No match found[/color]
        MsgBox "Cannot find '" & strCertainWord & "'. ", , "No Match Found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
    
[color=darkblue]Function[/color] FindFunction(strCertainWord [color=darkblue]As[/color] [color=darkblue]String[/color]) [color=darkblue]As[/color] Range
    
    [color=darkblue]With[/color] Sheets("Output")
        [color=darkblue]Set[/color] FindFunction = .Columns(4).Find(What:=strCertainWord, After:=.Cells(1, 4), LookIn:=xlValues, _
            Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[MartinVW]

Thank you a lot!

It worked!!

 

[AlphaFrog]

Thank you a lot!

It worked!!

You're welcome. Thanks for the feedback.