I have the following:

Debug.Print myRow("TEXT", Cells(i, "H").Value & "C2")


Function myRow(Lookup As String, myString As String) As Variant

Dim res As Variant
'res = Application.Match(Lookup, myString.Columns(2), 0)
res = Application.Match(Lookup, myString, 0)
Debug.Print res, Lookup, myString
If IsError(res) Then
myRow = "some error message here?"
Else
myRow = res
End If

End Function

Error 2015
TEST
'[workbook.xls]sheet1'!C2

I think I need an indirect around this, but can't get it to work.

"Dave Peterson" wrote:

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.



Barb Reinhardt wrote:

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

"NickHK" wrote:

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

"Barb Reinhardt" ¼¶¼g©ó¶l¥ó·s»D:CFDD9 ...
I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of
how
I'd set it up and use it? You can give a simple example and I'll fill in
my
own data.

Thanks,
Barb Reinhardt




--

Dave Peterson