I'd use...

dim myRng as range
dim ResRow as variant
dim ResCol as variant
dim res as variant

set myRng = worksheets("forecast").range("C1:HE586")

resrow = application.match(worksheets("item detail").range("a1").value, _
myrng.columns(1),0)
rescol = application.match(worksheets("item detail").range("k9").value, _
myrng.rows(1),0)

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

(untested, uncompiled--watch for typos)

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