Trying to use INDEX function in VBA Code, compile error

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

stringvar = =
Application.WorksheetFunction.Index(sheet1!C1:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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
> > > > >
> > > > >
> > >
> > >
>
>

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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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 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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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

Is your code in a general module or behind a worksheet?

If your code is behind a worksheet, then that unqualified range will refer to
the sheet that owns the code--and if Forecast isn't that sheet, you'll see that
error.

You can either change it to look more "normal" (ahem):
set rng1 = worksheets("forecast").range("C1:He586")

or you can qualify that range:
Set rng1 = Application.Range("forecast!C1:HE586")

==========
It's usually nicer to post more of the function--sometimes it helps clarify the
question.

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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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

You shouldn't have the code in a sheet module.

It should be in a general module.

--
Regards,
Tom Ogilvy


"Finny" <[email protected]> wrote in message
news:[email protected]...
> 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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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
>> > > > > >
>> > > > > >
>> > > >
>> > > >
>> >
>> >
>

Hello all,

How would I be able to modify the Match() portion of the code so that
it looks for multiple criteria in different ranges. For example, in
excel, I am able to use
"=Index(RangeOfValues,Match(Criteria1&Criteria2&Criteria3,Range1&Range2&Range3,0))."
How would this translate into VBA? I understand the idea of using the
index function in vba but only the match function with 1 set of
criteria and no more. If you could help, I would highly appreciate it.

-- Isaac

Tom Ogilvy wrote:
> You shouldn't have the code in a sheet module.
>
> It should be in a general module.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Finny" <[email protected]> wrote in message
> news:[email protected]...
> > 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:HE586,MATCH('sheet2'!A1,sheet1!C1:C1000,0),MATCH('sheet2'!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
> >> > > > > >
> >> > > > > >
> >> > > >
> >> > > >
> >> >
> >> >
> >