Trying to use INDEX function in VBA Code, compile error

[Finny]

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

 

[ducky]

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

 

[Guest]

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.

 

[Finny]

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

 

[Finny]

^

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

 

[Guest]

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

 

[Finny]

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

 

[Dave Peterson]

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)

 

[Guest]

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

 

[Finny]

It dies on first Set statement - 1004.
Wierd

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

 

[Finny]

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.

 

[Dave Peterson]

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

 

[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.

 

[Tom Ogilvy]

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

It should be in a general module.

 

[imaycotte]

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