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