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

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

Not sure that i fully understand your question but i can tell you that
Application.WorksheetFunction doesn't include all of the functions
available to formulas (because there is a VBA function that will
already do it) have you looked into .address?

Hope this helps

AR

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

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

^ :)
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

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

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

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

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