getting around function limitations? 1

[larryww]

I'm open to whatever thievery and trickery is required to be able to do a .Find within a function. Am I'm high, or does .Find only works in macros?

I acknowledge that functions are specifically and purposefully limited. Nonetheless, at times, I need to "gain functionality" within functions.

Chip Pearson had something that seemed clever in

http://www.cpearson.com/excel/differen.htm

though it wasn't practical for me. I have tried calling a macro from a function, but the "functionality of functions" restrictions still apparently held in the macro - namely, .Find still returned Nothing every time.

Any thoughts at all, folks?

 

[SkipVought]

Hi,
No problem using .Find in a Function...

Function FindString(rng As Range, sStr As String)
    FindString = Application.Find(sStr, rng)
End Function

returns the start position.

Am I missing something? Skip,
metzgsk@voughtaircraft.com

 

[larryww]

Maybe I really am high ... but try

Function MyFunc(rngA As Range)
Dim c As Range
Set c = rngA.Find("ABC&quot
Debug.Print "(c is Nothing)? "; (c Is Nothing)
End Function

Put this in B2:
ABC

Put this in D4:
=MyFunc(A1:C3)

Dig that funky immediate window - nuthin' every time. Same for you?

 

[larryww]

Now if you change it to a sub, dim and set variables appropriately, it's peachy.

Perhaps the ASSIGNMENT (i.e., the "set" -ting) is the culprit, being that functions 'philosophically' deter side effects within themselves?

 

[SkipVought]

try this...

Function MyFunc(rngA As Range)
    For Each c In rngA
        If c.Value = "ABC" Then
            MyFunc = c.Row
            Exit Function
        End If
    Next
End Function

VOLA Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Larryww,

If you're having issues getting the value back to your spreadsheet, it's because you're not sending the value back...


Add this right before END FUNCTION:

 myfunc = c

And you need to call the FIND function the way Skip suggested (check his message again for the syntax).

 

[larryww]

Thanks for responding...

skip -
thanks for the code, and indeed it is my current "workaround of choice." But I'd still like to "set c = .Find" if you or anyone can solve it.

euska - no, that is not the issue or question - the problem is that (c is nothing) as is. Did you get the same result with the code above Skip's post?

I need to (*Essentially*) "set c = .find..."
and then work with c before returning the value.

 

[dsb]

skip
I don't see an application.Find method in Excel2000.
There is a .FindFile, but no .find

Tried your code, it returns #Value if called directly
from the worksheet.


My poking around indicates that .Find does not (in this
version) return a range object when called in function
context and applied to a range
(rangefound = rangesource.Find())

of course it all works find in Procedure (macro) context)
David

 

[SkipVought]

dsb,
Any worksheet function can in VBA in the form...

x = Application.WorksheetFunction(arg1...)

Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

dsb,
Sorry, my mind works faster than my fingers sometimes:

Any worksheet function can be used in VBA in the form... Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Well, anyway. If what you're trying to do is find one (or more) occurances of "abc" or whatever in a range of cells, I would use SkipVought's method (for each c in RngA, etc) so I'm giving that a star. Anything using "find" is probably more complicated then you need to get...

 

[SkipVought]

Larryww,

I think that the problem is in using the .Find in a function. It can be used in a subroutine like this one...

Sub FindIt()
    Dim rngA As Range, sVal As String
    Set rngA = Range("A1:A999")
    sVal = "ABC"
    ActiveCell = rngA.Find(sVal).Row
End Sub

If you did...

With  rngA.Find(sVal)
' here's where you can do all kind of stuff to THAT Found CELL
End With

Skip,
metzgsk@voughtaircraft.com

 

[larryww]

skip - yep, but like I said early on here, nesting a sub call inside a function doesn't fool anything - the .find still fails then. Your sub is peachy, though (works).

Anyway...
Didn't somebody say something recently about .Find vs. looped compare, a couple of days ago? Seems like it was on one of those threads where the guy's handle was offensive and he was booted?

 

[dsb]

there was such a thread...it didn't go anywhere technically.

skip....
I want to be sure, did you end up changing your position
on being able to call .Find in a function context ?

If not, help me understand the routine you posted. Where is this use of find documented and what is the scope of the search (the activesheet, all the workbooks open under the application ?)

Function FindString(rng As Range, sStr As String)
FindString = Application.Find(sStr, rng)
End Function

 

[SkipVought]

There are several Find routines...
1. Find worksheet function
2. Find Method

Both use a Range to search in. Skip,
metzgsk@voughtaircraft.com

 

[dsb]

Skip
There is much here I don't understand

1. your function
Function FindString(rng As Range, sStr As String)
FindString = Application.Find(sStr, rng)
End Function

a. takes a range object, but only searches the
first cell in the range so what is its practical
use over using the much faster INSTR function?

b. Somehow returns a value to the worksheet, but
you cannot return it to a variable in the vba
routine(dblPosition = application.Find(sStr,rng))
which I don't understand. Can you explain what
is happening?

2. If you change your function to
Function FindString(rng As Range, sStr As String)
FindString = Application.Find(sStr, rng(1,1))
End Function

a. You search the same first cell
b. You get back the dbl value that .Find is spec'd
to return..and thus you can do extra processing

3. If you code your function full qualification
FindString = Application.Worksheetfunction.Find(sStr, rng)

This does NOT return a value to the worksheet.
Why isn't this identical to application.Find ?

4. If you add the range qualification, rng(1,1) then it
behaves like the your routine with the similar change.

5. What is the advantage of using this .Find over
FindString = Instr(rng.value(1,1),sStr) ?

Appreciate any insight you have on all this.

Thanks

 

[SkipVought]

dsb,
1. The initial question related to USING .Find in a function, NOT writing a function that was a super, wiz-bang, bullet proof, wipes yer nose kind of Find function.

2. The correct syntax for using the FIND worksheet function is...

x = Application.Find(sStr, rng)

NOT

x = Application.Worksheetfunction.Find(sStr, rng)

I had posted earlier...
"dsb,
Any worksheet function can in VBA in the form...

                         x = Application.

WorksheetFunction

(arg1...)

3. Instr is a VB function that searches a string. Find is a VBA function that searches an Excel Range Object. Big difference. Skip,
metzgsk@voughtaircraft.com

 

[dsb]

Well I am very confused somewhere.... not trying for superduper...only to figure out what this function was good for.

My experience is that the following DOES NOT search a range, only the first cell of the specified range.

x = Application.Find(sStr, rng)

This makes it equivalent to the Instr function pointed at the same cell.

Also, this syntax can only be used to return the value to the worksheet and not for anything else.

For instance
x = Application.Find(sStr, rng) + 1

DOES NOT WORK even though the Application.Find(..) is returning the found position. (and the Find function in a worksheet returns a double).

The initial note in this thread WAS trying to SEARCH a RANGE and thus this does not appear to be the answer.

However, I stand to be corrected and theirby learn something.

dsb

 

[SkipVought]

dsb,
If you look very closely, you will see egg on my face.

Application.Find does NOT work on a range, RATHER on a string. I stand corrected.

Skip,
metzgsk@voughtaircraft.com

 

[dsb]

Well, we will put it down to a bad MS implementation and then go on to the next problem.

good fun, it was !