I should have been more explicit (this is just for completeness):

Dim Res as variant
if isnumeric(resrow) _
and isnumeric(rescol) then
res = myrng(resrow,rescol).value
else
msgbox "missing at least one match!
res = "whateveryouwanthere"
end if

But you'd want:

Dim res as Range
if isnumeric(resrow) _
and isnumeric(rescol) then
set res = myrng(resrow,rescol)
application.goto res
'or just
application.goto myrng(resrow,rescol)
else
msgbox "missing at least one match!
res = "whateveryouwanthere"
end if

Finny wrote:

Also thanks Dave. I can't figure out what was wrong with the previous
code but yours worked! Thank you. It recieves the value of that cell.

But what I'm trying to do in all this is take to two strings from "Item
detail", look on of the them up on the y and one on the x axis in a
table in "forecast" and activate (go to) that intersecting cell.
I can't get the cell location form the returned value b/c it's not
unique.
I tried declaring res as a range and that didn't work
phew

I'm new to using excel functions in VBA. Thanks for all the help thus
far.

Finny wrote:
It dies on first Set statement - 1004.
Wierd


Tom Ogilvy wrote:
Well, I wouldn't have suggested it if I hadn't tested it.

break it into parts and see where the problem is

use something like this:

sub Testit()
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim rng4 as Range, rng5 as Range, res1, res2, res
set rng1 = Range("forecast!C1:HE586")
set rng2 = Range("'Item detail'!A1")
set rng3 = Range("forecast!C1:C1000")
set rng4 = Range("'Item detail'!K9")
set rng5 = Range("forecast!C1:FC1")

res1 = Application.Match(rng2,rng3, 0)
res2 = Application.Match(rng4, rng5, 0)
if iserror(res1) then
msgbox "Problems with finding " & rng2.value
elseif iserror(res2) then
msgbox "Problems with finding " & rng4.Value
else
res = Application.Index(rng1,res1,res2)
end if
if iserror(res) then
msgbox res1 & " or " & res2 & " is out of bounds"
else
msgbox "Results are " & res
End if
end sub

--
Regards,
Tom Ogilvy




"Finny" wrote:

I had tried that and still get the 1004

res = Application.Index(Range("forecast!C1:HE586"), _
Application.Match(Range("'Item detail'!A1"), _
Range("forecast!C1:C1000"), 0), Application.Match( _
Range("'Item detail'!K9"), Range("forecast!C1:FC1"), 0))

I also tried using the first code you gave me, created string vars as
sheet1 and sheet2 and loaded the strings with the sheet names. No go.
Could this have something to do with the workbook?

Thanks for your help


Tom Ogilvy wrote:
put single quotes around the names with a space like this:

Application.Match(Range("'Item Detail'!A1"), _
Range("forecast!C1:C1000"), 0), Application.Match( _
Range("'Item Detail'!K9"), Range("forecast!C1:FC1"), 0))

--
Regards,
Tom Ogilvy


"Finny" wrote:

That worked like a charm Tom.
My trouble is now that I've got working code and wan to implement,
I substituted the names of the actual sheets where the code will be
run.
It gives me a runtime 1004.

Application.Match(Range("Item Detail!A1"), _
Range("forecast!C1:C1000"), 0), Application.Match( _
Range("Item Detail!K9"), Range("forecast!C1:FC1"), 0))

Is it b/c I have a space in the name of sheet1: "Item Detail"?
I can't change it there are too many dependents on the name.

Thanks



Tom Ogilvy wrote:
I would expect this to work:

Sub hhh()
Dim res As Variant
Dim stringVar As String
res = Application.Index(Range("sheet1!C1:HE586"), _
Application.Match(Range("sheet2!A1"), _
Range("sheet1!C1:C1000"), 0), Application.Match( _
Range("sheet2!K9"), Range("sheet1!C1:FC1"), 0))
If Not IsError(res) Then
stringVar = res
MsgBox stringVar
Else
MsgBox "Not found"
End If
End Sub

Using application.match allows you to test the result with IsError -
otherwise, using worksheetfunction causes a trappable error and this must be
handled.

Also not that the arguments must be passed as valid VBA ranges where you are
using ranges.

--
Regards,
Tom Ogilvy


"Finny" wrote:

The following formula works like a charm as a formula in sheet1.

stringvar = =
Application.WorksheetFunction.Index(sheet1!C1:HE58 6,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('shee t2'!K9,sheet1!C1:FC1,0))

Try to use it in my macro and compile error states:

"Expected: line separator or )" and highlights the first colon at
":HE586,"

Are Index and Match valid forumulas to use in VBA?
I know the syntax is straight from excel built-in functions. Isn't that
what Application.WorksheetFunction demands?

Thanks







--

Dave Peterson